Backup MySQL Replication Database Server

Here is the script, which I use to backup MySQL Replication Slave database server.

HOST=localhost

USR=root

PASS=xxxxx

SOCKET=/tmp/mysql.sock

NOW=$(date +"%m_%d_%Y")

BACKUP_LOCATION=/path/to/backup/$NOW

mkdir -p `echo $BACKUP_LOCATION`

MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS -S $SOCKET -v"

MYSQLDUMP="/usr/bin/mysqldump -u$USR -p$PASS -S $SOCKET --all-databases"

# STOP_SLAVE

echo 'STOP SLAVE SQL_THREAD; FLUSH TABLES;'| $MYSQL

# GET_SLAVE_STATUS

echo "SHOW SLAVE STATUS\G" | $MYSQL > $BACKUP_LOCATION/bin_log_pos.log

# BACKUP_TABLE_STRUCTURES

$MYSQLDUMP --no-data --routines --result-file=$BACKUP_LOCATION/table_structure.sql

# BACKUP_RECORDS

$MYSQLDUMP --result-file=$BACKUP_LOCATION/all_records.sql

# START_SLAVE

echo "START SLAVE" | $MYSQL

####################

if you prefer, you can automate this script, add following line into crontab

0 4 * * * /path/to/script/backup.sh