VACS Blog

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.

After installing mysql server on a Ready NAS duo, it is necessary to tune the configuration to make the server run well on this small hardware. This article describes a possible configuration for tuning the Mysql server.

Mysql Temporary directory

Mysql uses files in the temporary directory to store temporary tables. Depending on your database and your queries, temporary tables could be quite large. To avoid problems in the /tmp partition becomming full, the best thing is to use a directory in the /c partition

tmpdir          = /c/backup/tmp

Make sure the directory exist before starting mysql:

# mkdir -p /c/backup/tmp

Mysql storage engine

After playing with a reasonably big database and the MyISAM storage engine, it turns out that the mysql server was sometimes crashing and barking at some corrupted myisam tables. I switched to the InnoDB storage engine, which is better for transactions anyway. Since the readynas does not have a lot of memory I've used the following configuration:

default_storage_engine = InnoDB
thread_cache_size = 0

innodb_buffer_pool_size = 6M
innodb_thread_concurrency = 1

Other mysql settings

To reduce the resources used by the mysql server to the minimum, I changed the max number of connections to a small number.

key_buffer_size = 16k
sort_buffer_size = 100k
max_connection = 10

I'm using these settings for almost 6 months now; my bacula database now contains a table with 5 milions of rows. Of course you can't expect big performance but the mysql server is stable.

While managing Planzone R&D development team, I asked our marketing team to promote our team and give visibility of each team member through an interview. Interviews started with Imade Lakhlifi who developped ProjectBar during his internship. He was followed by Chris Immel who is our AJAX, Javascript, CSS wizard, Marc Heuveline who developed the Microsoft Project import and Excel reports, Patrick Albaret who put in place a testing quality suite composed of 1300 tests, Tristan Dupont who developed robust core backend features and Jean-Luc Bernette who defines the Planzone features.

Last interview is mine as manager and architect of R&D team.

Each interview give a different perspective of what is Planzone, how it is developed, and where it's going to fly.

During his internship, Imade developped for us a Firefox plugin to manage projects easily from Firefox. The ProjectBar plugin was developped from scratch. It uses the Planzone API. Imade did a very good job at designing and implementing the plugin. We made an interview (French) Dans les coulisses de Planzone : Interview avec Imade Lakhlifi, développeur de la "ProjectBar". Read it!

Printing with an Epson PX700W printer from Ubuntu is a real challenge. No driver really work out of the box. After looking at many places, digging in dozen of forums and other answers sites, no real solution emerged. The article describes some steps to make the printer work for Ubuntu.

Google analytics is often used by Marketing teams to have a feedback of the web site usage, track visits, entry and leave points. Google analytics is easy to use but it has some drawbacks that you don't see at the beginning. Altering the performance of your AJAX operation is a serious drawback.

Having acquired a ReadyNAS duo for my new backup system, I wanted to explore the system that runs on it and see if I could run more services on it. There is nothing terrific in this article as many people have already done this before. Anyway it describes step by step what must be done to connect to the ReadyNAS.

Tracking changes in a server configuration can be critical to understand problems, identify security breaches and repair a server. When several people are in charge of administering one or several servers, sharing the configuration changes is helpful to inform each other about these modifications. The article describes a simple organization that uses subversion and daily mail notifications in case of change.

Symfony is a PHP framework for building web application. I have two projects that use Symfony 1.1 and I wanted to upgrade to the new 1.2 release. This article lists some issues that I found and how I fixed them.

My problem was to give some flexibility in a JSF page to customize some blocks so that a marketing team could easily change those blocks. Basically the marketing team needs to insert some promotion, advertising or specific presentation blocks. For this, they ask a designer to make those blocks. The designer will give us some piece of HTML (with Javascript) that must be inserted in the page.