Freeze Thaw Scripts to improve database backups Print

Introduction

Consistent and reliable backups are crucial for database integrity, especially for dynamic and continuously updated databases like MySQL. One effective method to ensure that backups include all data is through the use of freeze-thaw scripts in conjunction with QEMU (Quick EMUlator). This article explains the benefits of this approach and provides a script template for implementation.

What are Freeze-Thaw Scripts?

Freeze-thaw scripts are used to momentarily pause (freeze) database activities before taking a snapshot, ensuring that the backup captures a consistent state of the database. After the snapshot is taken, the database activities are resumed (thawed).

Benefits of Using Freeze-Thaw Scripts with QEMU for MySQL Backups

1. Consistency in Backups: By freezing the database, the script ensures that the backup reflects a consistent state of the database, with all transactions completed.

2. Reduction in Data Corruption: This method minimizes the risk of data corruption or incomplete data backups, as the database is in a stable state during the snapshot.

3. Automation of Backup Process: Freeze-thaw scripts can be automated as part of the backup process, reducing manual intervention and increasing efficiency.

4. Integration with QEMU Guest Agent: These scripts work in tandem with the QEMU guest agent, a daemon program that facilitates executing functions requiring guest OS assistance, like freezing and thawing filesystems​​.

The Script Template

A basic script template is provided below for implementation in a MySQL environment:

#!/bin/sh
# Flush MySQL tables to the disk before the filesystem is frozen.
# At the same time, this keeps a read lock in order to avoid write accesses
# from the other clients until the filesystem is thawed.
. /etc/qemu/qemu.conf

MYSQL="/usr/bin/mysql"
MYSQL_OPTS="-u$USERNAME -p$PASSWORD"
FIFO=/var/run/mysql-flush.fifo
# Check mysql is installed and the server running
[ -x "$MYSQL" ] && "$MYSQL" $MYSQL_OPTS < /dev/null || exit 0
flush_and_wait() {
    printf "FLUSH TABLES WITH READ LOCK \\G\n"
    trap 'printf "$(date): $0 is killed\n">&2' HUP INT QUIT ALRM TERM
    read _ < $FIFO
    printf "UNLOCK TABLES \\G\n"
    rm -f $FIFO
}
case "$1" in
    freeze)
	if [ ! -p $FIFO ]; then
            mkfifo $FIFO || exit 1
        fi
        flush_and_wait | "$MYSQL" $MYSQL_OPTS &
        # wait until every block is flushed
        while [ "$(echo 'SHOW STATUS LIKE "Key_blocks_not_flushed"' |\
                 "$MYSQL" $MYSQL_OPTS | tail -1 | cut -f 2)" -gt 0 ]; do
            sleep 1
        done
        # for InnoDB, wait until every log is flushed
        INNODB_STATUS=$(mktemp /tmp/mysql-flush.XXXXXX)
        [ $? -ne 0 ] && exit 2
        trap "rm -f $INNODB_STATUS; exit 1" HUP INT QUIT ALRM TERM
        while :; do
            printf "SHOW ENGINE INNODB STATUS \\G" |\
                "$MYSQL" $MYSQL_OPTS > $INNODB_STATUS
            LOG_CURRENT=$(grep 'Log sequence number' $INNODB_STATUS |\
                          tr -s ' ' | cut -d' ' -f4)
            LOG_FLUSHED=$(grep 'Log flushed up to' $INNODB_STATUS |\
                          tr -s ' ' | cut -d' ' -f5)
            [ "$LOG_CURRENT" = "$LOG_FLUSHED" ] && break
            sleep 1
        done
        rm -f $INNODB_STATUS
        ;;
    thaw)
        [ ! -p $FIFO ] && exit 1
        echo > $FIFO
        ;;
    *)
        exit 1
        ;;
esac

This requires a config file which we have put in /etc/qemu/qemu.conf. An example would be:

# username and password for mysql database
USERNAME=myuser
PASSWORD=ssshh-secret

Notes

• The script is designed to flush the MySQL database during the freeze operation and thaw the database afterward.
• It's important to run the script with appropriate privileges, typically as the root user​​​​.

Troubleshooting

In some cases, the qemu-guest-agent may not pick up the default script at its designated path. If this occurs, you may need to specify the full path to the fsfreeze-hook script using the -F option, as shown in the following example:

sudo /usr/sbin/qemu-ga --daemonize -m virtio-serial -p /dev/virtio-ports/org.qemu.guest_agent.0 -F/etc/qemu/fsfreeze-hook