You probably know the two regular ways to create an application-consistent snapshot of your MySQL database. Let’s rehash:

  • Option 1. Suspend MySQL service
    The MySQL service is stopped for a few seconds while the snapshot is created then started again. The database (and usually the application on top, too) need to be stopped. This impacts service availability and performance (since all application / database caches are cleared and need to be rebuilt).
  • Option 2. Use Online Dump
    An online dump of each database is taken using the mysqldump command which copies a database to storage accessible from the MySQL server. Extra storage capacity and storage I/O are required to copy the entire file out. This process may take a considerable amount of time.

Option 3. Lock database tables and flush to disk

However, I would like to introduce a third option, conceived and tested by my colleague Jelmer Jaarsma.

  • Option 3. Lock database tables and flush to disk.
    The MySQL service is not stopped, but locked for a few seconds (to prevent any changes to the databases) while open tables are flushed to disk. Then, the VMware snapshot is created and the database is unlocked. There’s no downtime or service availability impact and no need to rebuild cache or create an entire copy of the database.

So how does this thing work?

This option still uses the regular integration (using pre-freeze and post-thaw scripts) between the backup software, the VMware layer and the Linux Guest OS inside the virtual machine.

  • pre-freeze-script
    • Create two temporary lock files and launch background script. Pre-freeze script will run until database is locked.
    • Background script will flush tables to disk and write-lock database.
    • Finally, it removes a temporary lock file. This signals the pre-freeze script that the database is ready for snapshotting; the script terminates without error
    • With the pre-freeze script terminated, Veeam will now create the snapshot (with the database locked)
  • locktables script
    (
echo "FLUSH TABLES WITH READ LOCK;" &&
sleep 5 &&
touch ${WAITFORSNAPSHOT} &&
rm -f ${WAITFORLOCK} &&
while [ -e ${WAITFORSNAPSHOT} ]; do sleep 1; done &&
echo "SHOW MASTER STATUS;" &&
echo "UNLOCK TABLES;" &&
echo "quit"
) | mysql --defaults-file=/root/.my.cnf
  • post-thaw-script
    • During the snapshot creation, the background script is still running, keeping the database locked.
    • After the snapshot has been successfully created, the post-thaw script removes the 2nd temporary lock file. This will shut down the background script, unlocking the database and continuing normal database operation
    • No downtime; just a couple of seconds database query queue

 

How-to

  • Activate “Quiesce” option in your backup client
    vmwt
  • Copy this attached zip-file to /root on your server.
  • Make the three scripts executable
    chmod +x /root/backup/locktables.sh
chmod +x /root/backup/pre-freeze-script.sh
chmod +x /root/backup/post-thaw-script.sh
  • Create symlinks to /usr/sbin/ scripts (where the VMware Tools expects to find the scripts)
    ln -s /root/backup/pre-freeze-script.sh /usr/sbin/pre-freeze-script
ln -s /root/backup/post-thaw-script.sh /usr/sbin/post-thaw-script
  • Set the correct access mode to the scripts:
    chmod 755 /usr/sbin/pre-freeze-script
chmod 755 /usr/sbin/post-thaw-script
  • Create the temporary directory
    mkdir /var/run/veeam
  • Please set the password for MySQL in /root/.my.cnf for automated access. See this post for details.

 

Demo video