Migrating a large MySQL database from one server to another? One of these three options might fit your needs:
- Use rsync to synchronize the data folder of MySQL
- Use mysqldump and mysql commands
- Master-Slave replication (also with mysqldump and mysql)
In this article I will go over the characteristics of each approach, sum up the advantages and disadvantages and give a step-by-step example for tranfserring a MySQL database using the mysqldump and mysql commands approach.
Advantages and disadvantages
Option 1: Using rsync to synchronize the data directory
Rsync is a utility that can be used to transfer data between servers via an SSH connection. One of the main advantages of using rsync is that, if you run rsync several times it will only update files that changed over time. So this approach would be very useful if your database has a lot of static data (data that doesn’t get modified a lot).
When migrating a MySQL database with rsync, you’ll need to ensure binary portability. Binary portability means that you could migrate the binary database files to another server and the MySQL server will be able to directly use these files. Problems with binary portability can occur when you’re migrating your database to a server with a different operating system. Also, having differences in the MySQL server configurations could cause problems when you use rsync to migrate a database.
(More information on binary compatibility: https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html)
Option 2: Use mysqldump and mysql commands
This is the easiest approach of the three options. With execution of only a couple of commands you will be able to export the database to a file and import that into the new setup.
It’s easy, but has some downsides. As you can imagine, it takes a while to export and import a large database with these mysql dump commands. When you’re migrating a live environment with lots of user activity, you will need to confront the users with some down-time.
(Migrating within night hours and announce the down-time could soften this issue a bit)
Option 3: Use Master-Slave replication
This is the most complex approach of the three options. But by using master-slave replication, you will be able to decrease and almost eliminate the server down-time.
The disadvantage of this approach is that it might cost more time than what it brings value for. It is very time expensive compared to the other approaches, as you need to setup the right configurations. You should only consider this option if minimizing your down-time is a real must.
How Master-Slave works in short
WIth the Master-Slave approach you will need to setup configuration for the Master server (the server you want to migrate from) and the Slave server (the server you want to migrate to). At some point in time a database dump is made from the Master server, from that point on, all database queries will be logged and also pushed to a logfile on the Slave server. When the import of the database on the Slave server completes, the queries in the logfile should be executed and from that point the Slave server should run synchronically to the Master server (all changes on the Master server should also be executed on the Slave server). At this point you should be able to switch over to using the Slave server as master server without change in user experience.
(A good article to get known to the Master-Slave approach: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql)