AspiringSysadmin.com

It should come as no surprise to readers of this blog that I really like ZFS. Personally I think it is one of the biggest developments in file system design in many many years. One place where I’ve started to use it recently on my own home server is to make instant and consistent backups of my MySQL databases. In this article I will show you how to install MySQL on a ZFS file system and supply you with a script to make consistent snapshots of the databases. This script may not be 100% fit for busy sites but for most smaller places I think it is perfect.

To start of we will install MySQL. Since I’m also a big fan of Blastwave I will use their binary package.

# /opt/csw/bin/pkg-get -i mysql5
...lots of output...


This will download the MySQL server as well as the client programs and install them into /opt/csw/mysql5. You may want to add this to your PATH.

The next step is to make sure that the databases are stored on a ZFS file system. In the example I will assume you have ZFS pool called data.


# zfs create data/mysql
# zfs set mountpoint=/opt/csw/mysql5/var data/mysql


This creates a new ZFS file system and mounts it under /opt/csw/mysql5/var which is the default location to store databases for the Blastwave packages. If you want to have it in another place you have to edit my.cnf but I won’t cover that here.

We are now ready to create the initial databases. Blastwave supplies a basic script for that.


# /opt/csw/mysql5/share/mysql/quick_start-csw
...


Just accept the default answers and everything should be fine. We can finally start the MySQL.

Another great thing provided with the package is a SMF manifest. So you simply type this to start the database. If you want to have automatic monitoring of the database you can use my SMF monitoring script.


# svcadm enable cswmysql5


To see if it actually works try to connect to the database.


# /opt/csw/mysql5/bin/mysql -u root -p
Password: (just press enter here)


You should get a standard MySQL prompt if everything is setup correctly.

Now we come to the part that was the goal of this article. We want to make fast, automatic and consistent backup of this database. I’ve written a Python script that you can find here that will do a backup according to what is specified in the MySQL manual. It locks the tables, creates a ZFS snapshot and unlocks the tables again. The database will pause all writing during this operation but as you all know ZFS snapshots are fast! Download the script and install it e.g in /opt/scripts/backup_mysql.py. Before you can use it you need to install Python and the Python MySQL module from Blastwave.


# /opt/csw/bin/pkg-get -i python
# /opt/csw/bin/pkg-get -i pymysql


You also need to create a configuration file that contains information about the database and what file system to snapshot. I call my file /opt/scripts/backup_mysql.cfg and it contains the following information. I always try to keep password information in a separate file so that I won’t publish it by mistake.


user=root
password=
hostname=localhost
filesystem=data/mysql


Time to create our first backup.


# cd /opt/scripts
# ./backup_mysql.py ./backup_mysql.cfg
Creating snapshot data/mysql@20070813225258


The output claims that it has created a snapshot and you can confirm it with a simple zfs list.

You may want to put this script into cron so that it creates a new snapshot every day or whatever timeframe suits you. You may also want to have something that manages your snapshots so that you don’t keep them forever. Lastly, if you actually have important data in your database I suggest you copy it somewhere else as well.

Have fun and feel free to use the script wherever you like. If you find any problems with it please don’t hesitate to contact me.

Do you need system administration assistance? If you like what you are reading please consider subscribing to the RSS feed. If you have feedback or if you find the article useful please leave a comment below.