With a mysql database running on the ReadyNAS duo, it becomes necessary to put in place a backup of the database. This article describes a simple method to automatically backup the mysql database.

All actions described here require that you are connected to your ReadyNAS duo using SSH (See Connecting to a ReadyNAS duo using SSH)

ssh -l root pollux
root@pollux's password:
Last login: Sat Jan  9 12:59:54 2010 from zebulon
Last login: Sat Jan  9 15:34:19 2010 from zebulon on pts/0
Linux nas-D2-24-F2 2.6.17.8ReadyNAS #1 Fri Mar 20 04:41:57 PDT 2009 padre unknown
nas-D2-24-F2:~#

Backup Directory Preparation

First, we have to create a protected directory which will contain the backups:

nas-D2-24-F2:# mkdir /c/backup-mysql
nas-D2-24-F2:# chmod 700 /c/backup-mysql

Mysql Backup User

To make the backup, a special user should be used to restrict the rights to the minimum. Basically, the user only needs the SELECT and LOCK TABLES privileges. The database access should be protected with a password.

nas-D2-24-F2:# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.32-Debian_7etch5~bpo31+1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT SELECT, LOCK TABLES ON *.* 
    TO 'dump'@'localhost' identified by 'XXXX';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye

Backup Script

To make the backup, you have to write a simple script which uses mysqldump and compresses the backup file. This script is written in /etc/cron.daily directory. This way it will be run automatically by the cron daemon each day at 6:25am (Look at the /etc/crontab file).

Create the file /etc/cron.daily/backup-mysql and put the content below.

#!/bin/sh
D=`date --iso-8601`
BKP_DIR=/c/backup-mysql
DB_LIST="bacula mysql"
for i in $DB_LIST; do
  mysqldump --user=dump \
    --password=XXXX \
    --opt $i | gzip -c > $BKP_DIR/$i-$D.sql.gz &&
  chmod 400 $BKP_DIR/$i-$D.sql.gz
done

You have to update the DB_LIST variable to put the name of the databases you want to backup.

You have to protect the script because it contains the password of our backup user. The script must be executable.

nas-D2-24-F2:# cd /etc/cron.daily
nas-D2-24-F2:# chmod 700 backup-mysql

Test the script

It's necessary to execute the script at least once to make sure it backups what you need.

nas-D2-24-F2:# ./backup-mysql

Then, check that a backup file was created correctly.

Test the database backup or restore

You may also test that the backup SQL file is correct by creating a fake database and importing the file. You can do this with the following command:

nas-D2-24-F2:# mysql -u root -p 
create database test-backup;

Then decompress the backup file and import it with the mysql command into new test (or the real database if you want to restore it).

nas-D2-24-F2:# gzip -c \
-d /c/backup-mysql/xxx.sql.gz | \
mysql --force --force -u root -p test-backup

Conclusion

This process remains simple and is very easy to put in place. However, it has some limitations because it is best to make sure no applications are writing to the database when the backup is running. Otherwise you may backup data which are not consistent.