Backup MySQL on FreeBSD using Snapshots

Introduction

This page contains details on backing up MySQL on the FreeBSD Operating System.
1. Backup MySQL on FreeBSD using Snapshots

Snapshots on FreeBSD allow an administrator to take a frozen image of a filesystem at a given instant in time.

The following commands should be run with root privileges.
1.1 Creating a snapshot of your MySQL data directory

Creating a snapshot under FreeBSD is simple but first you need to know where your MySQL data directory is.
If you used the ports collection to install MySQL this directory is most likely /var/db/mysql
If you are still unsure and have MySQL running you can find out by issuing the following command mysql -e "show variables like 'datadir';"
Step 1 - Flush MySQL queries and lock tables

* Within the mysql client issue the following command:

FLUSH TABLES WITH READ LOCK;

This command may take a while to complete if you have long running queries. The command FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running queries. If you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step. To keep the lock open you will need to keep your MySQL client connected and open. The easiest way to do this would be to open another shell to perform the actual snapshot or script the whole process as outlined later.

Step 2 - Create Snapshot

*

Snapshot files must be created in the filesystem that is being snapshotted, so if your MySQL data directory is located on the /var filesystem, an obvious folder would be /var/.snap If this folder does not exist create it as follows:

# mkdir /var/.snap

You can create a snapshot using the mount command or the more simplified mksnap_ffs command as follows:

mount command

# mount -u -o snapshot /var/.snap/mysqlsnap1 /var

mksnapp_ffs command

# mksnap_ffs /var /var/.snap/mysqlsnap1

The above commands will take a snapshot of your /var filesystem and place it in the file /var/.snap/mysqlsnap1.
Note: There is a limit of 20 snapshots per filesystem.
Active snapshots are recorded in the superblock, so they persist across unmount and remount operations and across system reboots.

You can use the snapinfo command to list the current snapshot files by issuing the command:

# snapinfo -a

Step 3 - Unlock MySQL tables

* Now that we have taken the snapshot it is safe to unlock the tables and exit the MySQL client.

UNLOCK TABLES;
exit;

1.2 Mount a snapshot to access the frozen datafiles

To mount the snapshot (Read-Only) you need to do the following.
Step 1 - Attach snapshot file to a memory disk

*

# mdconfig -a -t vnode -f /var/.snap/mysqlsnap1 -u 4

Step 2 - Mount memory disk for access (Read Only)

*

# mkdir /mnt/mysql-backup
# mount -r /dev/md4 /mnt/mysql-backup

1.3 Remove a snapshot to reclaim space

When you are done with a snapshot, it can be removed with the rm command.
If you have already mounted the snapshot you will have to unmount and detach the memory disk before issuing the rm command as follows:
Step 1 - Unmount the snapshot

*

# umount /mnt/mysql-backup

Step 2 - Detach the memory disk

*

# mdconfig -d -u 4

Step 3 - Delete the snapshot

*

# rm -f /var/.snap/mysqlsnap1

Note: Snapshot removal of an 8Gb filesystem takes about two minutes. Filesystem activity is never suspended during snapshot removal.

1.4 Scheduling periodic snapshots

The easiest way to schedule snapshots would be to write a script and use the native Cron tool to run your script as needed. There are tools in the ports collection that can help with scheduling snapshots but as they do not lock the tables you may end up with inconsistent data in your snapshot. To ensure that you get a clean snapshot of your MySQL database you need to run the command FLUSH TABLES WITH READ LOCK from within a seperate session to your snapshot shell and keep that session open for the duration of the snapshot. For this reason it is often easier to script the process, you can download a sample Perl script here for inspiration:

*

mysqlsnap.pl

connect('DBI:mysql:mysql', "$username", "$password") || die "Could not connect to database: $DBI::errstr";
$dbh->do('FLUSH TABLES WITH READ LOCK');
print "Creating snapshot file $snapshot\n";
system("mksnap_ffs $filesystem $snapshot");
$dbh->do('UNLOCK TABLES');
$dbh->disconnect();
print "Snapshot created.\n";
exit(0);

1.5 References

For more general information on snapshots, please see http://www.mckusick.com/softdep/

*

FreeBSD Handbook
*

A brief history of the BSD Fast File System