Mysql – What’s the best way to migrate a MySQL database

migrationMySQLnavicat

We are moving a busy eCommerce site from one server to another.

It's a PHP application that uses MySQL. phpMySQL is not installed on either server. We use Navicat Essentials for MySQL to manage databases.

The plan is as following:

  1. Reduce DNS TTLs.
  2. User rsync to transfer files from old to new server.
  3. On old server, export database using Navicat and on new server import the dumped SQL file.
  4. Do the testing on the new server (by changing local hosts file so that on the computer, the domain points to new server)
  5. If there are any problems then troubleshoot and loop back to step 4. Else move to step 6.
  6. Once everything is good in step 4, turn on maintenance mode in the PHP application (still running on old server).
  7. Run rsync again to copy any remaining files since last rsync.
  8. In Navicat, on old server, for the relevant database, Dump SQL file (structure and data), and then on new server, drop all tables from the originally imported database, and Execute the SQL file which was freshly dumped.
  9. Update the DNS details with the new IPs, and increase the TTLs again.

Is this a proper approach to take? Actually, honestly, steps 1-7 are already completed. I have never used Navicat during migration before (always had phpMyAdmin), so I am worried about step 8. Is step 8 as it should be, or should I do something differently?

Best Answer

The plan that you describe involves application downtime or lost data (or both). A more recommended method would be to stand up the new DB server, configuring it as a Slave of the current server (now Master), reconfigure your app to use both of them as a master/slave pair. When the Slave is caught up (and you can connect to it and send queries to it), promote the Slave / demote the Master.

MySQL Replication Configuration Docs

Related Topic