Category Archives: Linux

Home / Operating Systems / Linux
2 Posts

It doesn’t harm to backup your MySql database server. In case of an emergency you probably will be glad you did. This post describes a simple method to do so.

The first action is to create a user in the MySql database server to use with just enough rights to fulfill its task. Open your favorite MySql management tool and create a new user with just a name and a password. (Don’t assign any databases.) In this case we will use BackupUser as username with the password P@$$w0rd.

Assign the following global rights :

  • Select
  • Reload
  • Show Databases
  • Lock Tables

Save the user in MySql and close the MySql management tool.

The next thing to do is creating a shell script. Open a SSH connection to your NAS. Start VI and type the following script:

#!/bin/bash
DIR=/volume1/backup/sqlbackup/
DATESTAMP=$(date+%Y%m%d%H%M%S)
DB_USER=BackupUser
DB_PASS=P@$$w0rd

# create backup dir if it does not exist
mkdir -p ${DIR}
# remove all backups except the $KEEP latest
KEEP=5
BACKUPS=`find ${DIR} -name "mysqldump-*.gz" | wc -l | sed 's/\ //g'`
while [ $BACKUPS -ge $KEEP ]
do
  ls -tr1 ${DIR}mysqldump-*.gz | head -n 1 | xargs rm -f
  BACKUPS=`expr $BACKUPS - 1`
done

#
# create backups securely
#umask 006

# dump all the databases in a gzip file
FILENAME=${DIR}mysqldump-${DATESTAMP}.gz
/usr/syno/mysql/bin/mysqldump -u $DB_USER -p$DB_PASS --opt --all-databases --flush-logs | gzip > $FILENAME

The constant DIR contains the path where the script must save its backup file. You can change this to fulfill your own needs.

You can run this script by assigning a tob to the crontab. How crontab works and how to add the task to crontab is described in Crontab explained. All you have to do is adding the following line to /etc/crontab :

1       0       *       *       *       root    sh /volume1/backup/backupMySql.sh

This ask will run every day at 0:01. Change it to the moments you want to run this backup. Don’t forget to restart the cron demon.

Restoring a database can be done with:

mysql -uroot -pecare2@ < alldatabases.sql

Change the root password according to your’s. The alldatabases.sql  file name must be replaced with the file name of your backup.

As a relative newbie I was confronted with the phenomenon of crontab to automate all kinds of jobs on my Linux driven NAS. I somehow never remembered how the format of a crontab line must be read. There are masses of sites that explains this. I felt the need to collect those information and put it into one convenient place: here.

Let’s take the following crontab line as an example:

1    0    *    *    *    root    sh /volume1/backup/backupMySql.sh

This line starts a backup script to backup a MySql database server. It does this with the root account and runs daily at 0:01 (12:01 AM).

The cron line syntax is mm hh dd MMM DDD user task where:

  • mm is the minute (0..59)
  • hh is the hour (0..23)
  • dd is the day in the month (1..31)
  • MMM is the month (jan, feb, … or 1..12)
  • DDD is the day in the week (sun, mon, … or 0..7 where 0 and 7 being both Sunday)

The following values are also valid:

  • * : a every unit (0, 1, 2, 3, 4…)
  • 5,8 : at unit 5 et 8
  • 2-5 : units 2 to 5 (2, 3, 4, 5)
  • */3 : every 3 units (0, 3, 6, 9…)
  • 10-20/3 : every 3 units, from 10th to 20th (10, 13, 16, 19)

If you take a closer look to the crontab line example, you see that the values are divided by some space. On Synology’s DiskStation it’s preferred to use a TAB as a divider. However a space is also allowed.

After you changed your crontab, witch can be found on the DiskStation at /etc/crontab, you have to restart the cron demon in an SSH shell with:

/usr/syno/etc/rc.d/S04crond.sh stop
/usr/syno/etc/rc.d/S04crond.sh start