MySQL streaming Xtrabackup to slave recovery
OverviewThere 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.
RequirementsIn 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.
StepsHere 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 dataOn 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. [code language="bash"] service mysql stop rm -rf /var/lib/mysql/* [/code]
Step 2. Start listener on target serverNow 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. [code language="bash"] nc -l -p 2112 | unpigz -c | xbstream -x -C /var/lib/mysql No Compression would be nc -l -p 2112 | xbstream -x -C /var/lib/mysql [/code]
Step 3. Start backup on source serverThe 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. [code language="bash"] innobackupex --stream=xbstream --parallel=4 /tmp | pigz -c --fast | nc -w 2 TARGET_SERVER 2112 No Compression would be innobackupex --stream=xbstream --parallel=4 /tmp | nc -w 2 TARGET_SERVER 2112 [/code]
Step 4. Prepare backup on target serverPercona 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. [code language="bash"] innobackupex --use-memory=3G --apply-log /var/lib/mysql [/code]
Step 5. Update ownership and start MySQLNow 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. [code language="bash"] chown -R mysql:mysql /var/lib/mysql service mysql start [/code]
Step 6. Configure replicationIf 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. GTID Replication [code language="bash"] mysql CHANGE MASTER TO MASTER_HOST='<MASTER_SERVER>', MASTER_USER='<REPL_USER>', MASTER_PASSWORD='<REPL_PASS>', MASTER_AUTO_POSITION = 1; START SLAVE;" [/code] Legacy Replication [code language="bash"] cat /var/lib/mysql/xtrabackup_binlog_info mysql CHANGE MASTER TO MASTER_HOST='<MASTER_SERVER>', MASTER_USER='<REPL_USER>', MASTER_PASSWORD='<REPL_PASS>', MASTER_LOG_FILE='<LOG_FROM_xtrabackup_binlog_info', MASTER_LOG_POS=<POSITION_FROM_xtrabackup_binlog_info>; START SLAVE;" [/code]
ScriptBelow 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.