This article describes a way to setup a regular backup for your MySQL databases. This method uses the crontab to schedule backup jobs (you could also use the logrotate method).

The database backups are stored in separate files. Once database backup is completed the file is zipped and password encrypted with openssl.

First create a .my.cnf file in your home directory with the following contents:

[client]
user=backup
password=1234567890

Make sure the file is only readable by your self:

chown 600 ~/.my.cnf

Next create a SQL user as shown below:

mysql> grant lock tables,select,reload,show databases,event on *.* to 'backup'@'localhost' identified by '1234567890';
mysql> flush privileges;

Create a folder in for example /var/backups/ and name it mysql

mkdir /var/backups/mysql

Create a script called backup_mysql_dbs.sh at a suitable location, for example your $HOME/cron/scripts folder (make it NOT world readable / writeable):

#!/bin/bash
BACKUPDEST=/var/backups/mysql

if [ ! -d "$BACKUPDEST" ]; then
echo "$BACKUPDEST does not exist, exiting"
exit 1
fi

# Remove backup files older dan 7 days
/usr/bin/find $BACKUPDEST/*.sql.gz.enc -type f -mtime +5 -exec rm {} \;

# Backup all mysql databases to separate files. The files are gzipped and encoded with a password.
umask 077
/usr/bin/mysql -Ne "show databases" | grep -v schema |
while read db; do
CDATE=`date '+%Y-%m-%d-%H%M%S'`
/usr/bin/mysqldump --skip-events --events $db | /bin/gzip | /usr/bin/openssl aes-256-cbc -salt -k $CDATE > $BACKUPDEST/$db.$CDATE.sql.gz.enc;
done

Now it is time to add a cron.d file; go to /etc/cron.d and add a crontab file, e.g. backup_mysql, with the following contents:

# /etc/cron.d/anacron: crontab entries for the anacron package
MAILTO=user@host.ext
M H * * * root /var/www/backup_mysql_dbs.sh

Replace the M and H with minute and hour you want the backup to be performed.

That’s it; your mysql databases are backedup at the sepcified interval.