200GB+ DB innobackupex questions before migration

greminn · December 30, 2020, 4:20pm

Hi There,

Running v1.5.1-xtrabackup on Debian Squeeze 64bit with standard debian mysql-server-5.1 (5.1.63-0+squeeze1) in a 2 server master/slave setup. The server(s) has one database (besides mysql), all InnoDB tables totaling about 225GB.

We need to migrate the master database to new hardware, but cannot shutdown for the amount of time it takes to just copy the 225GB data from one server to another.

Am i right in the following?

1). Using innobackupex i can backup the entire mysql install on the live server directly to the mysql datadir directory on the new server (with mysql off on the new server) - all without shutting down the live server and with no locking of the live server tables.

2). Then stop access to the live server and run --apply-log on the backup, which will apply the binary log file changes since the backup in step 1.

3). Then stop mysql on live server, change IP addresses over, set permissions etc, and start mysql on the new server.

Other clarifications:

All --copy-back does is simply copy the data from the backup dir to the datadir on the server. It does nothing else?

Would the best way to get the data from the live server to the new server during the innobackupex process be NFS?

Sorry for asking lots of questions on one go… but thanks in advance for any answers!

Cheers

Simon

scott.nemes · December 30, 2020, 4:29pm

With the help of replication, you should be able to do this with next to no downtime (likely just how long it takes to switch to the new IP).

Backup the master server using Xtrabackup.
Copy the backup to the new master (however you want; we use SCP normally internally).
Copy / move the backup to the data directory on the new master.
Run innobackupex --apply-log on the data directory of the new master.
Set correct permissions / ownership for the data directory / contents on the new master.
Start MySQL on the new master.
Using the replication coordinates recorded in the backup (xtrabackup_binlog_info), setup the new master as a slave of the old master.
Let replication catch up on the new master.
Stop replication on the new master.
Re-point your application to the new master IP.
Do whatever you want to the old master.

The last few steps may vary depending on how you want to handle the cut over. With the above. your application would remain up the whole time, but you’d lose whatever data went to the old master while you were switching the IPs (may be moot, but if it’s high volume and important data that may not be an option).

greminn · December 30, 2020, 4:29pm

Perfect process! We are up to just before step 8 and have scheduled a couple of mins downtime late this evening todo the swap over. Many thanks for the reply.

Simon