Sometimes data sets are so large, a mysqldump to load a slave is just not practical.  With some of the systems we have administrated, we have had data so large it would have taken days to load the slave when it became out of sync with the master.  When this happens, we usually rely upon Percona’s XTRABackup utility which allows us to make a hot/online backup of the master to use for loading the slave.

In the old days we had to rely upon a third-party tool called ibbackup, or InnoDB Hot Backup utility to do this task. In many ways XTRABackup is a replacement for this tool and has in fact surpassed the ibbackup utility in features and function.

The most efficient way we have found to transfer that data to the slave is the use of the netcat utility.

We also use the screen command since we expect this could take quite some time and don’t want to take the chance that a network connection issue, or a dropped VPN, could derail the process.

Below is the basic process we follow:

1. On the slave server, start a screen session (if installed): screen
2. Get the replication user account and password for the broken slave. cat /path/to/data/dir/master.info You will see something like the following. The 5th line is the replication user and the 6th is the replication password: 18 mysql-bin.010571 31351 myhost.com repl replication_password 3306 10 0
3. On the slave server, ensure MySQL is down: /etc/init.d/mysqld statusIf it is running, stop it: /etc/init.d/mysqld stop
4. On the slave server, prep the directories (Make a backup of datadir if there is room): cd /mysql/base/dir; mv data data.old; mkdir data; chown mysql:mysql data -R; cd data
5. On the slave server, start a netcat session to receive the xtrabackup and write it to datadir: nc -l 9999 | xbstream -xv
6. On the master server, start a screen session (if available): screen
7. On the master server, start an xtrabackup and send to netcat: innobackupex –defaults-file=/etc/my.cnf –user=root –password=my_password –socket=/path/to/mysql.sock –stream=xbstream /path/to/mysql/data/ | nc myslave.com 9999
8. On the slave server, prepare the backup: innobackupex –apply-log /path/to/mysql/data
9. On the slave server, make sure the datadir is owned by mysql: chown mysql:mysql /path/to/mysql/data -R
10. On the slave server, start MySQL on the node. /etc/init.d/mysqld start
11. Examine the “xtrabackup_binlog_pos_innodb” file to get the Master’s binary log file and position to restart replication.
12. Issue the CHANGE MASTER command with above info on binary log file and position from file to restart replication. An example is below: CHANGE MASTER TO MASTER_HOST=’mymaster.com‘, MASTER_USER=’repl‘, MASTER_PASSWORD=’replication_password‘, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.010572′, MASTER_LOG_POS=268496122, MASTER_CONNECT_RETRY=10;
13. Start replication thread: START SLAVE;
14. Verify replication and monitor until slave is caught up: SHOW SLAVE STATUS\G
15. Consider deleting the old data directory: rm /path/to/mysql/data.old -rf

Naturally, your process may vary and feel free to modify the script to meet your needs. Be sure to replace server names, the replication user and password, etc., with your information.