MySQL ZFS Snapshot Backups
(4 votes, average: 4.00 out of 5)
Loading ...August 31, 2010
This is a followup on my previous post concerning how to correctly snapshot databases on ZFS. Snapshotting MySQL any other way will just lead to corrupt database states, essentially making your backups useless.
Here is my script that I use to snapshot our MySQL database. It uses my zBackup.rb script for the automated backup rotation.
#!/bin/sh
mysql -h fab2 -u usr -ppass -e ‘flush tables;flush tables with read lock;’
/usr/bin/ruby /opt/zbackup.rb rpool/mydata 7
mysql -h fab2 -u usr -ppass -e ‘unlock tables;’
That won’t work, because you need to keep the connection from the flush tables to the unlock tables, as soon as the first mysql command finishes, an unlock is done, so your ruby command gets the database unlocked.
You need to lock and unlock within the same connection.