How to Restore a Large MySQL Database Replica

Prerequisites

• MySQL 5.5, 5.6 or 5.7
• Percona XtraBackup tools. The version should be 2.4 or lower.
• An open SSH port and connection between your current master server and your replica.

Installing Percona Xtrabackup

Go on the Percona installation page for Xtrabackup on Debian or Ubuntu to get the latest instructions for version 2.4.

Once Xtrabackup is installed, you will need to install the qpress compression utility tool that Xtrabackup requires to compress and decompress the backup.

  both$ sudo apt-get install qpress

You should install these tools on both your master and replica servers.

Start the backup process

On your master server, run the following commands to prepare the backup process.

Create a directory for the backup data. Use /var/backups/mysql. You should create this directory on both servers.

  both$ mkdir -p /var/backups/mysql

Next, run the Xtrabackup backup tool. There are multiple options to pass to this tool. Here's the link for the complete list of options.

Here's the list of options you should pass:

• user=MYSQL_USER
• password=MYSQL_PASSWORD
• stream=xbstream
  By using a stream with Xbstream, the backup can be copied and compressed in parallel which speeds up the process.
• parallel=#
  The number of threads to use to copy the data files. To not overload your normal production load, set parallel to an appropriate number of threads. You can set this value based on the number of CPU your server has. For this guide, our server has 6 CPUs, we will use 2 for parallel reads.
• compress-threads=#
  The number of threads used by xtrabackup for parallel data compression. Use 2 threads for compression. Leave 2 CPUs for the current production load your MySQL master server might need.
• target-dir=/var/backups/mysql
  Destination folder where the data will be written.

  master$ xtrabackup --backup --user=MYSQL_USER --password=MYSQL_PASSWORD --stream=xbstream --parallel=2 --compress-threads=2 --compress --target-dir=/var/backups/mysql > /var/backups/mysql/backup.xbstream

Transfer the backup data to the replica server

Now that you have your backup file with the name backup.xbstream, it's time to copy the file over to your replica server. For this, use scp. You could also use rsync. If your server has password authentication disabled, you will need to create a public and private key to allow the master to communicate with the replica. Otherwise, scp will prompt you to enter your user password.

  master$ scp /var/backups/mysql/backup.xbstream [email protected]_IP:/var/backups/mysql

Extract files from the stream data

Next, extract the data from the backup file you just copied to your replica server. On the replica server, run the following command:

  replica$ xbstream -x -C /var/backups/mysql < /var/backups/mysql/backup.xbstream

Decompress the files created by Xtrabackup

Next, you need to decompress the files created by xtrabackup.

Here's the list of options we will pass to xtrabackup:

• decompress
  Decompresses all the files with the .qp extension in a backup previously made with the xtrabackup --compress option.
• parallel
  The number of threads to use to decompress files. As your replica server is probably not running anything else, you can use all the CPUs of your server. In our case, we have 6 CPUs.
• remove-original
  This will remove .qp files after the decompression to free up disk space.

  replica$ xtrabackup --decompress --parallel=6 --remove-original --target-dir=/var/backups/mysql

Prepare the backup data

In order for your data to be consistent and ready to be used on your replica, you need to prepare it.

Here's the list of options we will pass to xtrabackup:

• prepare
  Performs the recovery on the backup, so that it's ready to use¹.
• use-memory
  How much memory is allocated for preparing the backup. As your replica server is not running any loads, we recommend that you set it to ~75% of the RAM on your server.
• parallel
  The number of threads to use to prepare the data. As your replica server is probably not running anything else, you can use all the CPUs of your server. In our case, we have 6 CPUs.

  replica$ xtrabackup --prepare --use-memory=12G --parallel=6 --target-dir=/var/backups/mysql

Stop MySQL and delete the data folder

Next, to copy the new backup data to MySQL's data folder, you need to stop MySQL from running and delete the data inside the folder.

  replica$ systemctl stop mysql
  replica$ rm -r -f /var/lib/mysql/ # or use mv if you want to keep the data.

Copy the backup files

Next, it's time to move your prepared files to the MySQL data folder. Xtrabackup knows where your MySQL data folder is by reading your my.cnf configuration file.

Here's the list of options we will pass to Xtrabackup:

• copy-back
  Copy all the files in the backup directory to their original locations.
• parallel
  The number of threads to use to copy the files. As your replica server is probably not running anything else, you can use all the CPUs of your server. In our case, we have 6 CPUs.

  replica$ xtrabackup --copy-back --parallel=6 --target-dir=/var/backups/mysql

You could use --move-back instead of --copy-back if you don't want to save your backup data. Data will be moved instead of copied.

Give MySQL permission to access the data directory

As the files were copied by your connected user, some might not be owned by the MySQL user. This command will change the files' ownership to mysql.

  replica$ chown -R mysql:mysql /var/lib/mysql

Start MySQL

  replica$ systemctl start mysql

Restart the replication

Now that the backup data is in place, it's probably out-of-date with your master server. During the backup process, Xtrabackup saved the binlog file name and position. It can now be used to start the replication at the right position. To get this information, run the following command.

  replica$ cat /var/lib/mysql/xtrabackup_info

In the output, you should see binlog_pos. The output is the following:

  binlog_pos = filename 'mysql-bin.000001', position '154'

Use this information to start the replication. Before you do this on the replica, ensure that the user on the master server used for the replication as access to the replication grant. If not, here's a SQL command to run on your master server.

  GRANT REPLICATION SLAVE ON *.*  TO 'replication-user'@'REPLICA_IP' IDENTIFIED BY 'PASSWORD_TO_CHANGE';

On the replica server, open a MySQL console and execute this statement with the binlog information obtained earlier.

  CHANGE MASTER TO MASTER_HOST='REPLICA_IP', MASTER_USER='replication-user', MASTER_PASSWORD='PASSWORD_TO_CHANGE', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 154;

Once that's done, you can start the replication.

  START SLAVE;

You should check that everything is operating properly with the following command:

  SHOW SLAVE STATUS;

Confirm that both Slave_IO_Running and Slave_SQL_Running are running (the value should be "Yes"). MySQL will pick up the new changes from the master database. The Seconds_Behind_Master means that MySQL is currently catching up with the changes from master and once it's done the value should be 0.

Clean up the backup data

If you used the --copy-back option, you still have the folder /var/backups/mysql full of data used for the backup. Once you confirm that your replica is running well, you can delete the data inside this folder.

  both$ rm -r -f /var/backups/mysql/

You can run this command on both your master and replica servers.

And there you go! Your MySQL replica is now up-to-date with your master database.

1. Percona - Preparing a backup