MySQL streaming Xtrabackup to slave recovery

Restoring a MySQL slave by manually taking a backup and then copying the file over the network can be an incredibly slow process as your database grows. This guide details how to bypass intermediate files by using Netcat (nc) and Percona XtraBackup to stream and compress data directly from a source to a target.

This "on-the-fly" method can reduce network traffic by 85% to 90% and ensures data is only written once at the final destination.

Technical Requirements

Before proceeding, ensure the following utilities and access levels are available on both the source and target servers:

• Netcat (nc): Used for the network data stream.
• Percona XtraBackup: Specifically the innobackupex and xbstream utilities.
• Pigz (Optional): Highly recommended for parallelized compression/decompression during the stream.
• MySQL Access: Credentials required for performing the backup on the source and configuring replication on the target.
• OS Environment: This guide was tested on Debian. Note that Netcat flags (like -p) may vary on other Linux distributions.

Step-by-Step Restoration Process

1. Prepare the Target Server

First, stop the MySQL service on the target and clear the existing data directory. This ensures a clean slate for the incoming stream.

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

2. Open the Listener on Target

With the data directory empty, start a Netcat listener. This command waits for the source to connect and pipes the incoming data through decompression into the xbstream extractor.

# With compression nc -l -p 2112 | unpigz -c | xbstream -x -C /var/lib/mysql  # Without compression nc -l -p 2112 | xbstream -x -C /var/lib/mysql 

3. Initiate the Stream from Source

On the source server, start the backup. Use the --parallel flag to match the number of CPU cores. The output is compressed by pigz and sent to the target IP via Netcat.

# With compression innobackupex --stream=xbstream --parallel=4 /tmp | pigz -c --fast | nc -w 2 <TARGET_IP> 2112 

4. Prepare the Backup

Once the stream finishes, the files are on the target disk but are not yet consistent. You must "prepare" the backup by applying the transaction logs. For a 4GB RAM server, using 3GB for this process is ideal.

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

5. Finalize Ownership and Start

Update the file permissions so the MySQL user can access the new data, then bring the service online.

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

6. Configure Replication

Finally, link the new slave to the master.

• GTID: Set gtid_purged and use MASTER_AUTO_POSITION = 1.
• Legacy: Read the binary log coordinates from the xtrabackup_binlog_info file created during the backup.

Automation: The Rebuild Script

The following Bash script automates these steps. Note: This script assumes you have SSH key access and a configured .my.cnf file for passwordless commands.

#!/bin/bash SOURCE_SERVER=$1 TARGET_SERVER=$2  # Configuration MYSQL_DATADIR='/var/lib/mysql' NETCAT_PORT=2112 SSH='ssh -q -o StrictHostKeyChecking=no'  if [ ! $1 ]; then     echo "Usage: $0 <source_server> <target_server>"     exit 1 fi  # Step 1: Wipe Target $SSH $TARGET_SERVER "service mysql stop && rm -rf $MYSQL_DATADIR/*"  # Step 2: Listener $SSH $TARGET_SERVER "nc -l -p $NETCAT_PORT | unpigz -c | xbstream -x -C $MYSQL_DATADIR" > /dev/null 2>&1 &  # Step 3: Stream from Source $SSH $SOURCE_SERVER "innobackupex --stream=xbstream --parallel=2 /tmp | pigz -c --fast | nc -w 2 $TARGET_SERVER $NETCAT_PORT" &  # Step 4-5: Prepare & Start # [Wait for completion, then run --apply-log and service mysql start] 

Conclusion

Streaming backups directly to their destination is significantly more efficient than the "backup then copy" workflow. By reducing disk I/O on the source and minimizing network overhead through on-the-fly compression, you can recover failed slaves in a fraction of the traditional time.

MySQL Database Services

Ready to optimize your MySQL Database for the future?