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

Question

I'd like to minimize downtime (during the "cutover" period).

We're running on EC2 with the data on an EBS volume. Is it safe to take a snapshot of the database volume while it's running and use that to restore on the new one or do I need to shut down the old database first?

Answer 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.

Answer 2

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

Answer 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.

Answer 4

First make sure binlogs are enabled. Take a filesystem level snapshot (e.g. LVM or similar) of the mysql directory under the global read lock (FLUSH TABLES WITH READ LOCK). Use this snapshot to setup the new server, and configure replication to the old system (you can figure out the binlog name and position (master_log_file/master_log_pos) by looking at the size of the newest binlog in the snapshot).

When you're ready to move, stop all writes (read_only=true, kill all connections) on the old server and wait for the new server to catchup with replication. Then make the new server writeable and take down the old server.

Depending on what sort of queries are run against you database, you can have a downtime on the order of seconds. This however depends on your write queries being deterministic so that the old and new server will have exactly the same data.