MySQL Backup Script

This is my almost no configuration needed bash backup script for MySQL. The script first uses the mysql command line application to get a list of every database on your MySQL server, then loops through the list of databases and creates a separate dump file for each database.

You do need to create a ~/.my.cnf for the user the script will be running as.

1. touch ~/.my.cnf
2. chmod 600 ~/.my.cnf
   

The format for .my.cnf should be:

[client]
user = MySQL_User
password = MySQL_Password

Now the backup script. Configure the MYSQLUSER to match the MySQL user you will be backing up using (should match what you configured in .my.cnf.) Change the DIR variable to match where you want database dumps to be stored. Update the paths for MYSQL and MYSQLDUMP if needed. Then schedule the script to run as a daily cron job.

#!/bin/sh
MYSQLUSER=root
DIR=/srv/mysql-backup/dumps
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# Day 1 is Monday.
DAY=`date +%u`

# Query MySQL for the list of databases in the MySQL Server.
# Create a seperate dump file for each database.
for DBNAME in $(${MYSQL} --user=${MYSQLUSER} -Bse 'show databases');
do
      DUMPFILE=${DIR}/${DBNAME}/${DBNAME}_DAY${DAY}.sql
      # Make the directory structure if needed.
      /bin/mkdir -p ${DIR}/${DBNAME}
      ${MYSQLDUMP} --add-drop-table ${DBNAME} --user=${MYSQLUSER} > ${DUMPFILE}
      # Remove last weeks backup if it exists.
      if [ -e ${DUMPFILE}.bz2 ]
      then
              /bin/rm ${DUMPFILE}.bz2
      fi
      # Compress the dumpfile using bzip2.
      /bin/bzip2 ${DUMPFILE}
done
# End Loop

This script will create a seven day rotation of MySQL dumps. (One dump for each day of the week.) The script alone isn't a complete backup solution. Use your backup solution of choice to backup the dump files to tape or other media. I'm using CrashPlan for all of my personal backup needs.