MySQL Streaming Xtrabackup to Slave Recovery

Overview

There are times when I need to restore a slave from a backup of a master or another slave, but too many times I have taken the typical approach of taking the backup on the source server and then copying it to the target server. This takes a great deal of time, especially as your database grows in size.

These steps are going to detail how to use Netcat (nc) and Percona Xtrabackup (innobackupexec) to stream the backup from the source server to the target server, saving a great deal of time by copying the data only once to the desired location. While the data is streaming to the target server, it’s being compressed and then uncompressed on the fly, reducing the amount of traffic going across the network by around 85% to 90% (typical backup compression ratios of MySQL Innodb I have witnessed are in this range). I will also provide a simple script to complete these steps to give you a start at creating your own customized solution.

Requirements

In order to accomplish this task, you need to keep the following items in mind:

Netcat (nc) – Application needed on both servers, used for data streaming.

Percona Xtrabackup – Application needed on both servers, used to perform the backup.

MySQL Access – MySQL access is required in order to do the backup on the master, and the slave configuration on the slave.

Pigz (optional) – This is only needed if you want to compress and uncompress it on the fly. If you are going to use this, it’s needed on both servers.

Debian – All code and scripts were tested using Debian. Commands may slightly change with different OS’s.

Steps

Here are the steps that are required to accomplish this task. The source server is the server where the backup is coming from. The target server is where the backup is going to.

Step 1. Stop MySQL on Target Server and Clear MySQL Data

On the server that needs to be restored, we will make sure that MySQL is stopped. Then we will clear out the old data as this will all be replaced with the backup coming from the source server. The example assumes your MySQL data directory is /var/lib/mysql.

service mysql stop
rm -rf /var/lib/mysql/*

Step 2. Start Listener on Target Server

Now that the target server has its MySQL data directory cleared of its old data, it is now ready to receive the new backup directly from the source server. The port 2112 can be changed, but the port needs to match on both source and target commands. Nc command and options may vary by OS.

Step 3. Start Backup on Source Server

The listener is now up on the target server, ready to accept the connection. We will now start the backup on the source server to stream to the target server. Update the parallel option to match the number of cpu cores on your server. Use the lower core count between the source and target server. The port 2112 can be changed, but the port needs to match on both source and target commands. Nc command and options may vary by OS.

Step 4. Prepare Backup on Target Server

Percona xtrabackup requires that you prepare the backup after it has been completed to apply any outstanding logs and get the database ready to be started. Use as much memory on your target server as you can without causing it to go OOM (Out of Memory). As an example, 75% of your total memory would be a good starting point if there is nothing else running on your server. On a server with 4G of RAM you could safely set user memory to 3G.

innobackupex --use-memory=3G --apply-log /var/lib/mysql

Step 5. Update Ownership and Start MySQL

Now that the apply logs step has completed on your backup, you should be able to update the ownership of the files to mysql:mysql, and then start the MySQL service.

chown -R mysql:mysql /var/lib/mysql
service mysql start

Step 6. Configure Replication

If the source server is a slave, you should be able to just start the new slave as the positioning will be already configured. If your source server is the master, then you will have to figure out if you are using GTID or legacy replication. If you are using GTIDs, you should be able start replication with gtid_purged being set and the master auto position parameter. If you are using legacy replication, you can find the master log and position in the xtrabackup_binlog_info file in your backup directory. In this scenario, the backup directory is the MySQL data directory (/var/lib/mysql/) on the target server.

Script

Below you will find the simple script that I came up with to get you started on automating this task to quickly rebuild a slave in your own environment. The script was created with a lot of assumptions that you may not have in your environment. Please make sure to update accordingly.

The script is running from a separate server that has access to both MySQL servers. May work by running on one of the MySQL servers but not tested to do so.

The account running the commands on the remote servers have SUDO access to run commands.

SSH key of the remote server is set up to allow ssh access to both MySQL servers allowing for ssh with no password prompt.

.my.cnf is configured in the home directory of the account being used to SSH on the MySQL servers allowing the script to run MySQL and Xtrabackup commands with no password prompt.

The following software is installed on the MySQL servers : netcat (nc), pigz, xtrabackup.

Firewall rules are open for the port being used by NETCAT streaming.

All my testing was on Debian servers. Found with other OS’s and Netcac versions there is a -d flag for running nc in the background. In Debian you have to use -p for netcat when telling it to listen on a port.

Conclusion

I have found that this has greatly increased the timeframe in which it took me to recover a failed slave. By transferring the data only once from the source server to the target server, and with the data being compressed during the transfer, I feel this is one of the most efficient methods of recovering a failed slave, or building a new one.

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.