MySQL Community Edition: Can I replicate my entire server with another server in one go?

Question

I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.

If I copy over the databases in one go, by the time the website for that database is migrated to the new server, the data will be out of sync.

Is there a way to setup a temporary replicate of all the databases in the server to replicate to the new server (one way master/slave replication, master being the original server and slave being the source server). I can then setup the website on the new server and run it in parallel to the old site until all sites are migrated then switch off the old server.

I'm running MySQL 5 community edition.

Thanks

Jason

Answer 1

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 !!!

Answer 2

If you take regular backups of your databases in consistent state, then you could restore the backup to a new server, and then set up replication so that your original server is master and the new server is a slave. After replication catches up, you can pick a convenient time when there is least traffic from the web, to stop your applications, promote your slave to master, point your application to new master, and restart your applications.

If you do not take backups, then you should take a backup and then follow the steps above. If you use InnoDB engine for all your tables, then you can take online non-blocking backup (there is a short period at the beginning were a lock is required to get the binary log position, usually this lasts less than a second).

Here is an example of how you could take an online non-blocking backup:

nohup mysqldump --socket=/path/mysql.sock --user=username --password=password --single-transaction --flush-logs --master-data=2 --all-databases --create-options --extended-insert --quick --routines > backup.sql 2> backup.err &