Tag Archives: MySql

Home / MySql
1 Post

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.