1

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?

OC2PS
  • 539
  • 2
  • 8
  • 21
  • Got one good DBA SO post check: http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another – user3400613 Apr 04 '14 at 09:59

2 Answers2

3

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

gWaldo
  • 11,887
  • 8
  • 41
  • 68
0

Googling turns out the most common method is using the mysqldump command.

https://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another -Most detailed

http://www.networkworld.com/columnists/2004/092004drinternet.html What is the easiest way to move mysql from linux box one my local Vista box?

However there has been mentions that Navicat, you mentioned that you are using, allows setup of data transfer/replication.

Hope this helps.

Community
  • 1
RobAtStackOverflow
  • 121
  • 4