MySQL – How to Migrate Huge Databases One by One to New Server

I answered a question on how to use rsync to copy a live DB to another location.

You would repeat rsync process multiple times until two rsync's run in the same time.

Then, you would shutdown mysql on the master, perform a final rsync to the new server, and start mysql on the master.

You then properly prep the new server, make sure /var/lib/mysql has everything, make sure you erase all binary logs on the new server, and make sure you have the same my.cnf on both machines (you may want to change the server_id)

Here is an excerpt from that link:

Step 01) install the same version of mysql on ServerB that ServerA has

Step 02) On ServerA, run SET GLOBAL innodb_max_dirty_pages_pct = 0; from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step.

Step 03) rsync /var/lib/mysql of ServerA to /var/lib/mysql on ServerB

Step 04) Repeat Step 03 until an rsync takes less than 1 minute

Step 05) service mysql stop on ServerA

Step 06) Perform one more rsync

Step 07) scp ServerA:/etc/my.cnf to ServerB:/etc/.

Step 08) service mysql start on ServerB

Step 09) service mysql start on ServerA (optional)

Give it a Try !!!

Do not use copy database wizard. Instead use backup/restore. Also, if you want to minimize the downtime then below steps would be sufficient :

Prepwork on the New server :

1. Create blank databases with the same name as the old server. This is because if any logins or jobs has that database as default, there would be errors.

2. Script out all the logins using the sp_help_revlogin from the old server to the new server and create them on the new server.

3. Script out all the jobs. You dont have to Script > CREATE To for each jobs. Instead right click in Object Explorer and script all the jobs in one shot :-) Remember to disable the jobs until you failover to the new server.

   

4. Scrip out all Linked servers and recreate them on the destination server. Remember that passwords will not be scripted out.

5. Once 1-4 is set up then set up either Logshipping or database mirroring (Async mode) from source server to destination server.

6. On the day of failover

   • if using logshipping then manually failover to new server.

   • if using Mirroring then change the mode to synchronous and then initiate a failover to new server.

This way you will do all the prep work before hand and then during the day of failover, the downtime will be minimal.

You can refer to my answer HERE for more steps (they are for migration from old version to higher version, but still will help you).