MySQL quick database transfer between two servers

backupmigrationMySQLmysqldumptransaction-log

I am migrating a reasonably large (18GB) database used for a website from one server to another. There are two tables which make up 95% of that size and they do not have to be fully transferred for the site to function but eventually they'll have to be transferred. All tables are InnoDB.

I've examined the following options but I'm still unsure which would be most reliable and with least downtime.

Option A: mysqldump + import

Stop server A, mysqldump, transfer to server B and import. Definitely the safest method but downtime could be over 2 hours.

Option B: mysqldump w/out large tables + import later

Would be very quick but I'm not sure how the server would function importing the large tables while under load and whether this would cause it to lock.

Option C: rsync datadir

I've seen this option mentioned in other question answers, and I wasn't able to make it work on a running server, understandably, but I can see it going wrong again even if server A is stopped fully before syncing. Also server A is on 5.6.25 while server B is on 5.6.28.

Option D: early import + incremental backup

I could make a –single-transaction dump on running server A, import it in advance and then make a further incremental backup whether through the binary log or other incremental backup systems.

Best Answer

Option A

Definitely the safest, but long downtime not necessary if Option D is applied correctly. Your downtime would be based on collecting the incremental, not the initial load of the target server. This would be very short if the loading of incrementals are being done frequently.

Option B

This is good, provided the large tables are archive data only.

If the large tables are active logs, go to Option D

Option C

To ensure an rsync, mysqld should be down. To do so, run this

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, run this

service mysql stop

Then, issue the rsync of the data directory. This will ensure the mysqld commits everything is has on hand when performing MySQL shutdown. Thus, when you run

service mysql start

the MySQL will not do any crash recovery during mysqld's startup phase.

If you decide to rsync a live server, you are not taking into account the InnoDB Buffer Pool that has dirty pages (changes needed to be flushed to disk). Starting mysqld from a live rsync would impose crash recovery using ibdata1's double write buffer and a scan for any changes (deltas) in the redo logs (ib_logfile0,ib_logfile1). This is risky in a write-intensive live database.

Option D

Doing --single-transaction mysqldump creates a convenient point-in-time backup. Doing the incremental using binary logs will require four(4) things:

  • Enable binary loggging on DB Server
  • Ensure that binary logs stay around by increasing expire_logs_days in case you delay doing the incremental load of changes a day or two
  • Use mysqlbinlog to dump events after the replicaton coordinates of the dump to create incremental data
  • Use --master-data=2 to record binary log and position of the dump (will appear on line 23)
Related Question