Using xtrabackup to configure replication

January 5, 2020 Stanly G MySQL 1 minute, 13 seconds

Configuring MySQL replication can be done by taking a backup with xtrabackup on the master, restoring the backup on the slave, and changing just a few settings.

Prior to backup, ensure that you have a user setup for replication, with a known password. This will be required later. (And if you don't have it, you'll have to start over.)

On the master server, backup the data. This assumes you already have pigz, pv, and xtrabackup installed.

MYSQLDATA=/var/lib/mysql ; SAVEPATH=/rep-data ; \ 
time xtrabackup --backup --stream=xbstream --parallel=2 $SAVEPATH 2> $SAVEPATH/xtrabackup_log |\ 
pv -s $(du -sb $MYSQLDATA | cut -f1) |\ 
pigz --fast -p2 > $SAVEPATH/backup.xb.gz

Transfer the backup.xb.gz file to the slave server using your favorite method. On the slave server, stop the mysqld process.

service mysqld stop

Restore the backup file.

INCOMING=/var/lib/mysql_incoming/ ; SOURCE=/rep-data/backup.xb.gz ; \
time pigz -d -c $SOURCE \| 
xbstream --parallel=1 -x -C $INCOMING

Replay the transaction log.

INCOMING=/var/lib/mysql_incoming/ ; \
ulimit -n 1000000 ; \
time innobackupex --defaults-file=$INCOMING/backup-my.cnf --use-memory=2G --prepare --target-dir=$INCOMING

Update the path, ownership, and restart mysql.

MYSQLDATA=/var/lib/mysql ; INCOMING=/var/lib/mysql_incoming ; \
chown -R mysql:mysql $INCOMING 
mv -v $INCOMING $MYSQLDATA
service mysqld start

In MySQL on the slave, change the master server.

CHANGE MASTER TO MASTER_HOST='192.168.100.100', MASTER_USER='replica', MASTER_PASSWORD='abc';

And update the binary log file/position.

echo "CHANGE MASTER TO MASTER_LOG_FILE='$(cut -f1 /var/lib/mysql/xtrabackup_binlog_info)', MASTER_LOG_POS=$(cut -f2 /var/lib/mysql/xtrabackup_binlog_info);"

Remove the path from the log file, and execute the command inside of SQL, e.g.

CHANGE MASTER TO MASTER_LOG_FILE='db-bin-log.xxxxxx', MASTER_LOG_POS=xxxxxxx;

Then start the replication slave.

START SLAVE;