4

This one is tricky. I want to have the ability to move a single database from a server to another, with no downtime. I could take a snapshot of the data on the primary, move to the secondary, start up replication, get up to date data on the secondary, move the queries to the secondary.

But what if the secondary server already is a production MySQL server, that I can't restart, and that has to accept incoming "databases transfert" from multiple different sources servers?

I would just like to have a really brief introduction to the matter.

4
  • The replication process you detailed is probably the best method to reduce downtime. Should only be down a couple minutes. Is a few minutes downtime truely not an option?
    – Grant
    May 15, 2012 at 22:27
  • 1
    There are some methods for hot copy and software available. Here's a good article on the replication process in production MySQL Live DB Move
    – inevio
    May 15, 2012 at 23:19
  • You could start separate mysql on the secondary server and make it listen on a different port or a different ip address.
    – Zrin
    May 16, 2012 at 3:57
  • Yes zrin, but I want to pool the memory used by MySQL among every database I have on the server I start up, that would cernanly work, but I consider it a very dirty solution once you have like 100 running instances of mysqld on the same server ;)
    – cedivad
    May 16, 2012 at 6:25

3 Answers 3

2

So yeah, the correct way to do this is with clustering/replication. Here's some incorrect (but probably functional in special cases) ways to do it:

  1. If you're using MyISAM on ZFS or some other filesystem that can support snapshots, you can lock the table for write, issue flush tables, snapshot the filesystem, then unlock tables. Then, go into the snapshot, copy all the files out, and load them up on the slave server. Of course, getting them into the slave server is kind of a reverse of the above procedure, except there's a delete&move instead of a reverse snapshot. Note: I would not seriously recommend this to anyone on a production system.

  2. You can write a script that creates temporary tables on the slave system using the definition of the tables you're going to be replicating to, then connects to the master system, does a select * and copies the results into the target memory tables, then when they're all done, lock (or transaction) the tables and copy the data from the memory to the real tables (ie, delete then replace, or replace and then delete); then, unlock and wee.

Both these require effectively no downtime (depending on your table sizes etc it may lock the DBs for a brief time, probably at most a few seconds, which is probably fine if you don't do it too often.)

Both these solutions however are not great in that they create load spikes, and result in periods between syncs where the slave data is not up to date. You didn't specify you needed that in your question, hence the suggestions, but just pointing out that if this is in fact required, then yes, you need replication.

I use something like the first idea to do backups on a very specific DB server we have (and they work fine) and the second to sync data from an upstream source I do not control down to a server I have control over so that I can work with the data. Like I said, no great general solutions there, but in those specific cases they work a treat.

3
  • When run on a cluster MSQl, or pretty much anything else for that matter, is logically on one server, so I really can't see how that's going to help. May 16, 2012 at 5:12
  • Thanks for the downvote, John, but the OP didn't ask to move from one logical server to another, just "server", so I'm covering all bases here and making sure I explain that the best way to do what it's most sensible to assume the OP is asking is via clustering/replication. If you read past the first sentence of my reply however, you'll see I also covered some (admittedly nonstandard) ways to achieve what he's asking even if he did in fact mean different logical servers as well. May 16, 2012 at 5:24
  • 1
    Thanks for the answer Jon. A crazy idea of mine, maybe useful for others (yes, I'm using ZFS). Lock tables/snapshot/unlock/copy on a spare server I can reboot, synch the spare to master, use the spare server to iusse the select * queries, so that the load goes only on the spare server, so that the final server is up and happy with an older version, sync the final server with the master, delete the spare server. Not sure it's gonna work, but I think it will with some workarounds!
    – cedivad
    May 16, 2012 at 6:22
2

Since this is very difficult to do without having native support in the database itself, you may want to look into using a MySQL cluster with asynchronous replication. This will, however, require that you use the MySQL Cluster distribution, not the normal MySQL distribution.

Otherwise, like @RolandoMySQLDBA's answer suggests, there's simply no way to replicate the database without having some underlying support for mirroring updates across each database. What he doesn't clarify is that this does not cause downtime--the site can remain up in a read-only state while the database is dumped and loaded.

1
  • I can't find the RolandoMySQLDBA's answer, anyway thanks, but I didn't specified (my fault) that I'm not using NDB. However, I could suggest that to my clients. Thanks!
    – cedivad
    May 16, 2012 at 6:30
1

I also think a MySQL cluster is your solution. You can set a active/passive solution. What I did was configuring fail-over on my DNS server if failing I balance the server to the passive one and then I did the fail-over...

Of course the best solution active/active is the best but when I did this was not trivial or I was much more stupid than now :P

Anyway I think your solution will be definitely focused to cluster configuration.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .