MySQL on EC2: Consistent Backup and Log Purging using EBS Snapshots and N2W

MySQL is probably the most popular relational database used for web applications and websites. It’s used by almost all web hosting services and is the basis for web legends like Facebook. It is so prevalent in these applications, that in fact, the ‘M’ out of the term “LAMP Stack” stands for MySQL (Linux, Apache, MySQL, PHP). MySQL is also very popular on the EC2 cloud platform. It is widely used on EC2 instances and also offered by AWS as one of the options in RDS. While RDS supports a fixed-schedule backup by default (once a day) and also manual snapshots, the user is responsible for the backup of its MySQL databases running on EC2.

The most powerful way to perform such a backup is by using EBS snapshots, especially when large databases are involved. EBS snapshots provide the fastest and cheapest method of backup, with the most efficient data reduction method: block-level incremental snapshots. The “cherry on top” for using EBS snapshots is their rapid recovery property: with EBS snapshots databases can be restored in seconds, regardless of their size.

How to provide consistency for MySQL?

In a busy production environment the database gets constantly updated. To provide a reliable backup solution, the database needs to be in a consistent state when the EBS snapshots are taken. This is required to avoid situations where transactions are left open, important data is still cached in memory and write operations to files are still ongoing. Some sources suggest addressing this at the file system level. They provide instructions to freeze IO at the file system level, specifically when using XFS, which has a freeze command.

However, better consistency of the database can be achieved by going to a higher level in the storage stack. While a file-system level operation can make sure that the file system is left intact, the database itself can do even better: it can make sure no cached data is left in memory at the application level. It can also ensure that all transactions are closed or left at a consistent state. MySQL has a command that allows flushing all data to disk and keeps a read lock on the database, meaning that read operations are allowed, while write operations are not. This lock only lasts for a very short time, and should not cause any failures or concerns over performance. The point-in-time property of EBS snapshots requires the database to be locked only until the snapshots start. There is no need to wait until they are completed. EBS snapshots guarantee to record the exact state of the disk at the point-in-time of the beginning of the snapshot.

Truncating Binary Logs

One task that can be achieved with a backup solution is truncation of binary logs. Binary logs are created by the database to provide point-in-time consistency in case of a crash. As binary logs consume storage space, it makes sense to delete them from time to time. But which binary logs can be deleted and when? Once a consistent backup is completed, it is OK to delete binary logs older than that backup. This is the reason that logs truncation is a task often performed by backup solutions.

The Scripts

For those interested in hands-on information, here are some example scripts for getting the MySQL database into a consistent state.  These example scripts were written for use with a N2WS Backup & Recovery backup policy. However, they can be used in other environments as well. These scripts assume they are running on a remote machine, and use SSH to connect to the MySQL host and perform all operations. There are 3 scripts (+ one helper script):

1. “before” script – is launched before the EBS snapshots are taken, this script locks and flushes the MySQL database.
2. “after” script – is launched right after all the snapshots started. This script saved a timestamp and then releases the lock.
3. “complete” script – is launched when all snapshots completed. It gets an indication if everything was successful, i.e. if previous scripts succeeded and all snapshots competed successfully. If so, it issues a command to purge all binary logs older than the timestamp created by the previous script.

Here are the scripts: “before” script:

#!/bin/bash ssh -i /cpmdata/scripts/mysshkey.pem sshuser@<MySQL host address> "mysql -u root –p<MySQL root password>” -e 'flush tables with read lock; flush logs;'" if [ $? -gt 0 ]; then
echo "Failed running mysql freeze" 1>&2
exit 1
else
echo "mysql freeze succeeded" 1>&2
fi

“after” script:

#!/bin/bash
if [ $1 -eq 0 ]; then
echo "There was an issue running first script" 1>&2
fi
ssh -i /cpmdata/scripts/mysshkey.pem sshuser@<MySQL host address> "date +'%F %H:%M:%S' > sql_backup_time; mysql -u root -p<MySQL root password> -e 'unlock tables;'"
if [ $? -gt 0 ]; then
echo "Failed running mysql unfreeze" 1>&2
exit 1
else
echo "mysql unfreeze succeeded" 1>&2
fi

helper script “complete_sql_inner”:

butime=`<sql_backup_time`
mysql -u root -p<MySQL root password> -e 'PURGE BINARY LOGS BEFORE "'"$butime"'"'

“complete” script:

#!/bin/bash
if [ $1 -eq 1 ]; then
cat /cpmdata/scripts/complete_sql_inner |ssh -i /cpmdata/scripts/mysshkey.pem    sshuser@<MySQL host address> "cat > /tmp/complete_ssh; chmod 755 /tmp/complete_ssh; /tmp/complete_ssh"
if [ $? -gt 0 ]; then
echo "Failed running mysql truncate logs" 1>&2
exit 1
else
echo "mysql truncate logs succeeded" 1>&2
fi
else
echo "There was an issue during backup - not truncating logs" 1>&2
fi

This last script actually copies the helper script to the MySQL host and executes that script on the host.
With this 3-scipt approach, automatically supported by N2W, one can ensure the backup of a MySQL database is consistent as well as truncating unneeded binary log after the all snapshots succeeded.