Category Image MySQL Backup and Maintenance Strategy


This is a description of a simple backup strategy for MySQL. At the time of this writing, we were using MySQL version 4.1.21 in a master-slave replication configuration, described in a previous article . The replication setup forms a basis for the backup strategy and makes backup very easy while providing 24/7 planned uptime on the master MySQL server.

On the master MySQL server
The binary logs directory is on a separate physical drive. This helps performance a little I'm sure, but the main benefit is that if the data drive fails on the master server, we still have binary log to "play back" updates that might not have made it to the slave. So a lost master data directory could be rebuilt from the slave data and if the slave was behind a few seconds in replicating, the master binary log could be used to recover those last few data transactions.

Also on the master is a simple daily maintenance script which is self explanatory
#!/bin/bash

# This script performs daily maintenance tasks on xmysql master MySQL server
# the script should be run as root
# The tasks performed are:
# - backing up the my.cnf file to the external drive
# - flush logs (which automatically rotates the binary log)
#
# TO-DO items when I get time
# - slow query log rotation and purge
# - identify and purge binary logs that the slave has fully read

# Must be run as root
if [ `whoami` != "root" ]; then
echo "You must be root to use this!"
exit 1
fi

# The privileges for backup user are documented in the slave /etc/my.cnf config file
USER=backuser
PASSWORD=backpassword
BACKUP_DIR=/Volumes/mysqldatabackup/databasebackups/

# Backup this script in case I have made changes
echo "cp /etc/xmysql-master-daily.sh $BACKUP_DIR"
cp /etc/xmysql-master-daily.sh $BACKUP_DIR

# Backup the my.cnf file in case I have made changes
echo "cp /etc/my.cnf $BACKUP_DIR"
cp /etc/my.cnf $BACKUP_DIR

# Flush the logs which starts a new binary log
echo "mysqladmin -u $USER -p<password> flush-logs"
mysqladmin -u $USER -p$PASSWORD flush-logs

echo "Done."

You can set this script to run daily via a cron task or a launchd job

On the slave MySQL server
This is where the backup snapshots take place. There are a number of different strategies you could employ, including
- Backup the mysql data directory
- Use mysqldump to backup all or some of the databases
- Create an offsite remote slave that replicates from this slave across the internet

My current strategy here is to run a nightly script that actually shuts down the slave and makes a copy of its data directory each night to external drives that are rotated offsite. The script looks like this:
#!/bin/bash

# A script to back up the mysql data folder

# Shutdown mysql server before we copy the files using
# the backup user in my.cnf [client] params with shutdown privileges
echo "Stopping the MySQL Slave server for backup"
/usr/local/mysql/bin/mysqladmin shutdown
echo "mysqladmin shutdown command executed"

# Now copy the mysql data folder to backup drive
echo Synchronizing mysql data to backup drive `/bin/date`
/bin/mkdir /Volumes/backup/Enterprise/mysql_backup/data
/usr/local/bin/psync -q -d /usr/local/mysql/data /Volumes/backup/Enterprise/mysql_backup/data
echo Finished synchronizing mysql data using psync `/bin/date`

# Now restart mysql
echo "Starting mysqld again with mysqld_safe"
echo ""
# This next line starts mysql, sends output to bit bin and puts it in the background
/usr/local/mysql/bin/mysqld_safe > /dev/null &
echo "mysqld has started"

echo MySQL backup task complete: `/bin/date`

Posted: Friday - November 03, 2006 at 04:11 PM        

Published by