Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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

share|improve this answer

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 &
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.