Island Linux Bits and Bytes of Technology

The HOWTO will show how to automate the backup of your local MySQL server on a daily basis. The backup process uses a bash script and the local logrotate functionality to achieve an automated systems. The backup is processed daily via cron (see /etc/cron.daily/logrotate).

While this HOWTO is written for a local development environment it could easily by modified for remote multi-server environments.

Step1: Create the backup script

Create a file at /usr/local/sbin/backup_mysql.sh using your favorite text editor. I use joe:

sudo joe /usr/lcoal/sbin/backup_mysql.sh

Add the following bash commands to the script:

#!/bin/sh
#
# written by Dallas Vogels 2008-10-01
#
export PATH=/bin:/usr/bin:/sbin:/usr/sbin

OUTPUTDIR="/var/backups/mysql"
OPTIONS="--all --complete-insert --add-drop-table --extended-insert --quote-names"
CONFIG_FILE="/root/.my.cnf.backup"

# check that backup dir exists
if [ ! -d $OUTPUTDIR ]; then
        mkdir $OUTPUTDIR
fi

# get list of databases
DATABASES=`echo "SHOW DATABASES" | mysql --defaults-file="$CONFIG_FILE" mysql`

for DATABASE in $DATABASES; do

  if [ "$DATABASE" != "Database" ]; then
    # backup database
    mysqldump --defaults-file="$CONFIG_FILE" $OPTIONS $DATABASE > $OUTPUTDIR/$DATABASE.sql
  fi

done

exit 0

Note that I am going to run this script as root and must secure the file appropriately. It is completely feasible to run this script under any user if you require. To secure the script to run under root:

sudo chmod og-rwx /usr/local/sbin/backup_mysql.sh

Step 2: Create a MySQL user for backup

We are going to add a user called "backup" to the MySQL database. I use the command line client. You could also use phpMyAdmin if so inclined.

sudo mysql -u root -p mysql

Enter your password and type in the following command:

GRANT SELECT, LOCK TABLES ON *.* TO backup@localhost IDENTIFIED BY 'YOUR STRONG PASSWORD HERE';
FLUSH PRIVILEGES;

Step 3: Create a customized .my.cnf file

Note the CONFIG_FILE="/root/.my.cnf.backup" on line 9 of the MySQL backup script. Create the file and add the following:

[client]
user="backup"
password="YOUR STRONG PASSWORD HERE"

Make sure the file is secure:

sudo chmod og-rwx /root/.my.cnf.backup

Step 4: Add a logrotate configuration file

Create /etc/logrotate.d/mysql-backups and add the following:

/var/backups/mysql/*.sql {
  daily
  copy
  missingok
  rotate 30
  compress
  notifempty
  create 640 root adm
  sharedscripts
  prerotate
    /usr/local/sbin/backup_mysql.sh
  endscript
}

Step 5: Testing

To test:

sudo backup_mysql.sh
sudo logrotate -f /etc/logrotate.d/mysql-backups

You should see two files for each database in /var/backups/mysql. The first will be an uncompressed .sql file; the second will be an archived file as generated by logrotate. MySQL backups will occur daily and 30 days of backups will be maintained.