Percona XtraBackup

Using Percona's XtraBackup is amazingly fast. I can build a 100GB MySQL slave in 20 minutes - compared to 24 hours using the old mysql dump/import methods.

Install XtraBackup

yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum -y install percona-xtrabackup-80
yum -y install qpress

Stop MySQLd and remove old data

service mysqld stop
rm -Rf /var/lib/mysql

Give the MySQL server a unique ID (base it upon the IP)

echo -n 'server_id = ' >> /etc/my.cnf.d/mysql-server.cnf
echo $(curl -s icanhazip.com) | tr . '\n' | awk '{s = s*256 + $1} END{print s}' >> /etc/my.cnf.d/mysql-server.cnf

Get data from Master

mkdir /backup
cd /backup

ssh -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null root@SERVER "xtrabackup --backup --compress --user=root --password=PASSWORD --stream=xbstream --target-dir=./" | xbstream -x -C ./
xtrabackup --decompress --target-dir=/backup
xtrabackup --prepare --target-dir=/backup
xtrabackup --move-back --target-dir=/backup
chown -R mysql:mysql /var/lib/mysql
service mysqld restart

Connect slave to master

mysql -pPASSWORD -e "reset master; CHANGE MASTER TO MASTER_HOST='MASTERIP',MASTER_USER='MASTERUSER', MASTER_PASSWORD='MASTERPASSWORD', MASTER_LOG_FILE='$(cat /backup/xtrabackup_binlog_info | cut -f 1)', MASTER_LOG_POS=$(cat /backup/xtrabackup_binlog_info | cut -f 2); start slave"

mysql -pPASSWORD -e "show slave status\G;"

Monitor the slave

If you want to check to ensure the slave is healthy, we use the following shell script that we run every 5 minutes...

#!/bin/bash

### VARIABLES ### EMAIL="EMAILADDRESS"
SERVER=$(hostname)
MYSQL_CHECK=$(mysql -pPASSWORD -e "SHOW VARIABLES LIKE '%version%';" || echo 1)
LAST_ERRNO=$(/usr/bin/mysql -pPASSWORD -e "SHOW SLAVE STATUS\G" | grep "Last_Errno:" | awk '{ print $2 }')
SECONDS_BEHIND_MASTER=$(/usr/bin/mysql -pPASSWORD -e "SHOW SLAVE STATUS\G"| grep "Seconds_Behind_Master:" | awk '{ print $2 }')
IO_IS_RUNNING=$(/usr/bin/mysql -pPASSWORD -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{ print $2 }')
SQL_IS_RUNNING=$(/usr/bin/mysql -pPASSWORD -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{ print $2 }')
ERRORS=()

### Run Some Checks ###

## Check if I can connect to Mysql ##
if [ "$MYSQL_CHECK" == 1 ]
then
    ERRORS=("${ERRORS[@]}" "Can't connect to MySQL (Check Pass)")
fi

## Check For Last Error ##
if [ "$LAST_ERRNO" != 0 ]
then
    ERRORS=("${ERRORS[@]}" "Error when processing relay log (Last_Errno)")
fi

## Check if IO thread is running ##
if [ "$IO_IS_RUNNING" != "Yes" ]
then
    ERRORS=("${ERRORS[@]}" "I/O thread for reading the master's binary log is not running (Slave_IO_Running)")
fi

## Check for SQL thread ##
if [ "$SQL_IS_RUNNING" != "Yes" ]
then
    ERRORS=("${ERRORS[@]}" "SQL thread for executing events in the relay log is not running (Slave_SQL_Running)")
fi

## Check how slow the slave is ##
if [ "$SECONDS_BEHIND_MASTER" == "NULL" ]
then
    ERRORS=("${ERRORS[@]}" "The Slave is reporting 'NULL' (Seconds_Behind_Master)")
elif [ "${SECONDS_BEHIND_MASTER}" -gt 300 ]
then
    ERRORS=("${ERRORS[@]}" "The Slave is at least 300 seconds behind the master (Seconds_Behind_Master) - ${SECONDS_BEHIND_MASTER}")
fi

### Send and Email if there is an error ###
if [ "${#ERRORS[@]}" -gt 0 ]
then
    MESSAGE="An error has been detected on ${SERVER} involving the mysql replication. Below is a list of the reported errors:\n\n
    $(for i in $(seq 0 ${#ERRORS[@]}) ; do echo "\t${ERRORS[$i]}\n" ; done)
    Please correct this ASAP
    "
    echo -e $MESSAGE | mail -s "Mysql Replication for $SERVER is reporting Error" ${EMAIL}
    echo -e $MESSAGE
fi

Just be careful to ensure that you remove any existing events you have setup as this can mess up your master->slave replication.



Want to get in touch?