Fastest way to move MySQL database to a new system while minimizing downtime?

Solution 1:

I would suggest:

1. Taking a snapshot whilst briefly in read lock with FLUSH TABLES WITH READ LOCK;.
2. Bring that dataset up on your secondary machine and check consistency.
3. Setup replication from the old machine to the new.

Then at your cutover point:

1. Bring down the client facing IP on the old machine (you have a separate one, right?).
2. Issue FLUSH LOGS; on the old machine.
3. Ensure the new machine is in sync. 0 seconds behind.
4. Stop the old machine and double check the last binlog size against the new machine's position.
5. Issue STOP SLAVE; RESET MASTER; on the new machine.
6. Bring up the client facing IP on the new machine and arping to ensure the clients see it.

There are a few finer details, such as whether you are a heavy InnoDB user. But that is the general jist.

Solution 2:

Why not setup the second MYSQL server as a slave, replicate, then reconfigure the slave to be a master?

Solution 3:

Dan C is right on the money, but I'd like to be more specific about the 1st 3 steps

For the sake of speed, and to avoid disasters, do it all in the CLI mysql client like so:

sudo mysql -e "FLUSH TABLES; FLUSH TABLES WITH READ LOCK; SYSTEM ec2-create-snapshot vol-4d826724; UNLOCK TABLES;"

I am relying on the following points:

1. Acquiring the read lock can take some time if there is a long running UPDATE, DELETE, or INSERT command. I issue a preparatory FLUSH TABLES to minimize the time that any tables are locked.
2. The mysql client can pass commands to the host OS with the SYSTEM command, and does so as the user running the mysql client. (That's why I used sudo)
3. When you said you used ESB on EC2, which I've never used, I looked up this documentation for you. You have to look up what to put for the "vol-XXXXXXXX"
4. Make your life a little easier and put a password in ~root/.my.cnf

I see this describe ambiguously, or blatantly wrong all over the internet! The documentation clearly states: "If a client connection drops, the server releases table locks held by the client.". So, you cannot open the mysql client, flush+lock, quit client, make snapshot, open mysql client, unlock tables. You will end up with an inconsistent snapshot.

Some extra-sharp readers will correctly identify that the "UNLOCK TABLES" is unnecessary, because the client connection is going to close at the end anyway. I put it in there because it makes people more comfortable.

I've cited 6 sources for you. I hope you feel confident doing this now. Let us know if you have anymore uncertainty.