Backup mySQL-database with a cronjob

The script described below and the backups should be saved somewhere on volume1. You have to edit the script to match your preferred path. You also have to enter your sql-password in the script.

1. Put the code below into a file called mysqlbackup.sh:

#!/bin/bash
# number of backups to be saved
KEEP=2
BACKUPS=`find /volume1/path/sqlbackup -name "mysqldump-*.gz" | wc -l | sed 's/\ //g'`
while [ $BACKUPS -ge $KEEP ]
do
ls -tr1 /volume1/path/sqlbackup/mysqldump-*.gz | head -n 1 | xargs rm -f 
BACKUPS=`expr $BACKUPS - 1` 
done
DATE=`date +%Y%m%d%H%M%S`
rm -f /volume1/path/sqlbackup/.mysqldump-${DATE}.gz_INPROGRESS
/usr/syno/mysql/bin/mysqldump --opt -uroot -psqlpassword --all-databases | gzip -c -9 > /volume1/path/sqlbackup/.mysqldump-${DATE}.gz_INPROGRESS
mv -f /volume1/path/sqlbackup/.mysqldump-${DATE}.gz_INPROGRESS /volume1/path/sqlbackup/mysqldump-${DATE}.gz
exit 0

2. This file has to be saved to /volume1/path.

3. Make a subdirectory /volume1/path/sqlbackup

4. For a test you can run the script with "sh /volume1/path/mysqlbackup.sh". When you start the script, the old backups will be deleted and after that the backup will start.

5. Finally you have to add this script to /etc/crontab to have the script run at a specific time. Add the following line into the crontab:

1       0       *       *       *       root    sh /volume1/path/mysqlbackup.sh

The script will start every day at 0:01h (just after the time update).

6. Restart your Synology Station to activate the new crontab.