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
- 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.
Hi. This procedure only works for MyISAM tables? or will work with InnoDB ? Thanks
I have already tested and it works like a charm with MyISAM and InnoDB tables. Thanks for sharing!!
How would you modify locktables.sh to process properly on a server that is running multiple instances?
According your’re storing your sockets under /mysql/admin and the sockets have the name of the instance after the @.
for INST in $( ps -ef | grep mysqld | grep daemon | cut [email protected] -f2 | cut -d” ” -f1)
do
(
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 –user=root –password=xxx –socket=/mysql/admin/${INST}.sock
done
is this an alternative to sync driver in vmware tools? Do we need to turn sync driver off?
Caveat emptor! In general this does not work. To understand why, read about the InnoDB write buffer and dirty pages.
Why doesn’t it work? This seems to be discussed everywhere for Linux with no clarification. No one seems to know what VMware tools quiescing actually does either.
Hi, this works with MariaDB too?