The Challenege:

Moving one of our MySQL database servers to a new machine. Database is live and under constant heavy load, 80GB in size, with 5,000+ tables. Taking the machine down for 'scheduled maintenance' is not an option. With the database handling financial transactions and campaign stats tracking, we couldn't afford any mistakes or the slightest chance of data loss.

Suggested solutions:

* MySQLHotCopy - a Perl script that locks MySQL tables, then uses system-level "cp" or "scp" to make a database copy quickly. Upon completion, MySQLHotCopy flushes the tables and releases the lock. Fastest way but only works if the source and destination are on the same machine.

Not suited for our case and also I'm not clear about the usefulness of this script. If you're looking to create a database backup for disaster recovery, you're better off backing to a remote machine.

* rsync - a utility that provides fast incremental file transfer.

RSync-ing directories on two remote machines is as simple as:


Works great for daily file backups but not suitable for a MySQL database. If you're rsyncing while your database is live, you'll never reach a "safe" point where the database DNS record can be switched over (causing all future requests to land on the new machine) with a guarantee that all data up to that point has been transferred successfully.

Depending on your load, you will experience some degree of data loss.

* MySQLDump - Suggested by our hosting provider, MySQLDump creates a file of MySQL statements that can later be executed on the destination server to safely recreate the database.


This command can be executed on the destination server and it will pull the contents of the source database, recreating all tables and data.

Unfortunately using this approach would require taking the source database server down to avoid loss of data. In our case, even shutting down for a few minutes was not an option we were willing to live with.

Chosen Method - Replication:

MySQL Replication, a solution suggested by our resident database guru, is the process of setting up a master/slave relationship between two database servers, where the slave continually and automatically gets fed all MySQL queries from the master machine, resulting in a replication of data across the two machines.

Once replication has been configured and activated, the slave machine will act as a live MySQL database, holding an up-to-the-minute snapshot of all data from the master machine.

What we love about this solution is that it leaves no "holes". No potential for data loss and no need to ever take the database down.

Once the slave machine is ready, with the flip of a switch, we could redirect all queries to the new machine, terminate the master/slave relationship and we'll be up and running with the new machine.

Here's how the actual implementation went:

Step 1: Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege.


Step 2: Turn on binary logging of MySQL transactions. The server cannot act as a replication master unless binary logging is enabled. Locate the my.cnf file on the master database server machine and add these two lines to the file, under the [mysqld] section:


If those options were not present (we had them), you will have to restart the server.

Step 3: Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement - this is important so that we can safely copy the database files, with no fear of the files being changed during the copy operation. Make sure you keep the shell window where you issue the command below open throughout the process, until the tar finishes. Once this window closes, the lock will be automatically released.



Step 4: Take a snapshot of all data on the master database server. The easiest way to create a snapshot is to use tar. (Make sure you have sufficient storage space on your master server to hold the tar. If unsure, calculate the current size of your database and plan for an additional 70% of available space)


On our system this operation took a little over four hours to complete. Notice the "&" operand - this causes tar to run as a background process. Useful if your shell gets disconnected. You'll know it's done by periodically viewing the process-list using "ps -ax"

Step 5: While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:


The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

Step 6: Now that you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:



Step 7: Hop on the new database server machine (slave). Shutdown the database, then locate the my.cnf file and add these lines to the file:


Step 8: Copy and extract master database snapshot to the slave server under /var/db


Step 9: Start the slave database and issue these commands, replacing the option values with the actual values relevant to your system:


Step 10: Start the slave


If everything went well, your slave database will now connect to the master database, downloading all transactions following the time you took the snapshot. From this moment on, the slave database will continually feed itself with data from the master database, always staying in sync.

Confirm the slave server is up-to-date by running a:


and comparing the position markers with what the master database reports under:


Step 11: Update database-connect functions

Once the slave database is up-to-date, it is now time to update your application code, routing all calls to the new server.

Assuming you have all database access functions encapsulated in a database.php / database.c layer, locate the function that establishes a connection to the database and update the IP address or domain name to point to the slave database.

Voila! There you have it -- migration of MySQL from machine A to machine B without ever taking the database down.

Step 12: Test

Run a tail on the master database MySQL log file, or run SHOW PROCESSLIST and make sure no one is accessing the master database any more. Once confirmed, shutdown the master database and test for any strange side effects.

I recommend you keep the old database server up for a few days until you're absolutely certain the transfer went through smoothly.

Running a mysqlcheck --all-databases on the new database is highly recommended.

Any comments, tips or insight are welcome!

Update:

If you don't have enough storage space on the master server to hold the tar file, you could tar across ssh using this command:


Replace USERNAME with a login on the slave machine
Replace NEWHOST.COM with the domain of the slave machine

RSync would also work for the snapshot part.
Something like this:

If your shell gets disconnected & won't help you much. You might want to look into nohup or screen (or maybe nohup screen ;)

Thank you Jon, that is a very valuable tip.

Most people don't realize once you do FLUSH TABLES WITH READ LOCK, if the shell gets disconnected or MySQL goes away (because of its internal timeout setting), then the lock gets released and if you're still TARing, your tar will be corrupted.

I updated the guide above, taking an extra step to ensure this doesn't happen.

This page has become quite popular, with about 30 hits per day, from developers looking to "binary copy mysql server", "move mysql database", "copy mysql" etc.

If you're reading this page and you are looking to move your MySQL database, be prepared to invest about 5-10 hours into the process. We found using remote tar works the best, but once the new database is up, there are typically some issues to deal with, especially if you're migrating MySQL4 to MySQL5.

How about letting us handle the migration/replication for you? We can get it done in 5 hours guaranteed and get started right away. For more info about using SoftwareProjects database services - click here

I got all excited about this page - until I setup a test environment to see if it would work for getting a new slave (in one city) to sync everything off a master (in another city) as preperation for a datacenter cutover in a high mysql load environment with huge databases. The production master database can not be stopped - or prevent writes - for anything more than a few minutes without causing finanical loss.

After issuing the "FLUSH TABLES WITH READ LOCK" on the master in the first mysql client session - in the second mysql client session trying to do a simple insert simply froze - until "UNLOCK TABLES" was issued on the master in the first mysql client session - then the second mysql client session was able to finish the insert query.

What that would translate to in production:

* OldMasterServer "FLUSH TABLES WITH READ LOCK"
* get the master position, copy the database files to NewSlaveServer - a copy that will take days due to large mysql database size and small pipe bandwidth between sites (due to vpn tunnel overhead between city A and B)
* all new transacations coming in from the web are not being written to OldMasterServer while the read lock is in place - the cgi programs trying to do the inserts would eventually time out or die etc... new data coming in would be lost
* UNLOCK TABLES on OldMasterServer - writes start happening again
* restart mysql on NewSlaveServer with the copied data in place, CHANGE MASTER blah blah to get the slave started pulling from the master at the position that was recorded during the read lock

I don't see how this "FLUSH TABLES WITH READ LOCK" approach to this problem has any advantages over mysqlhotcopy or mysqldumps... none of these options are going to work for a server cutover in an environment with LARGE mysql databases that CANNOT have inserts blocked.

The author in the beginning said "Taking the machine down for 'scheduled maintenance' is not an option." Blocking inserts from web applications for many hours by using a read locks is a much worse option (IMHO).

Scott,

Good point. There's no way around FLUSH TABLES WITH READ LOCK, you absolutely have to issue this command prior to taking a snapshot of your database.

You are 100% correct that while FLUSH TABLES WITH READ LOCK is in effect, all INSERT queries will wait and not execute. This behavior is by design.

So what can you do?

Fortunately there's a way around the issue you are describing. From your comment I understand you are trying to create the snapshot directly on the new database and the pipe is tiny, causing this operation to possibly take too long to complete.

The way to get around this, is to create the snapshot locally (on the current database machine) using tar with compression turned on or using Veritas. If you don't have enough storage space on the current machine to store the snapshot, create another machine on the same subnet and tar the snapshot over there.

All you have to do is create the snapshot locally (or at least on the same subnet) so that this operation can complete as quickly as possible. As soon as you have a snapshot, you can immediately UNLOCK TABLES. There's no need to wait until the snapshot is transferred to the destination machine. If you have access to a RAID5 setup or Veritas, grabbing a snapshot can typically be done in less than a minute, regardless of the database size.

Please feel free to contact me directly or post a project here and we'll be glad to assist.

Hey why don't use a similar pattern as suggested
(lock->fulldump->unlock) but with LVM instead ?
lock -> snapshot->unlock->copy snapshot to backup

I've done it for a couple of years and it is flawless compared to mysqldump. mysqldump in my opinoin can not really be used on a live server efficiently.

I've created my own LVM script which is suited for master-replication situations but for pure backuping mylvmbackup will help you: http://lenz.homelinux.org/mylvmbackup/

Ps. I can give away my script if wanted Ds.

Kindly

//Marcus

Thanks for this post! Its very useful.

To reduce the time of the snapshot, you can run an rsync command with the database alive without LOCK:

nohup rsync --size-only -zav ./mysql /local/ &> log.txt &

Then, put the LOCK and run this command again.
They will only copy the changes files.

* Note: This is work fine and fast for system with a loot of tables. System with one dozen of big tables don't will improve very much.

On Debian Linux the databases are stored in /var/lib/mysql/. The same should be the case for Debian derived systems such as Ubuntu.

Has anyone investigated into the options you get for such an endeavour when you sign into MySQL Enterprise support?

I had a database crash when trying to rsync it with the error:
InnoDB: Error: Write to file ./ibdata1 failed at offset 0 1048576.
InnoDB: 1048576 bytes should have been written, only -1 were written.

Given the fact that this occurred while I was trying to rsync my suspicion is that something about the copy caused the locking issue. Has anyone encountered this before?

But remember that replication is not a be-all-end-all backup solution. Replication can break and sometimes the datasets may not even match in some ways.

We use a combination of mysqldump, tarballs and replication, we also use Munin and a plug-in based on mk-heartbeat to monitor replication (see mysqltoolkit).

What I'd like to do is use rsync over the raw /var/lib/mysql/* without worrying about data corruption. This way, I cac achieve more efficient use of backup media by only syncing the differences. A good use of the slaves here is to do the backups off that, so the FLUSH with READ LOCK statements do not impact production performance.