MySQL Backup Script

Here’s a simple bash script to backup your MySQL databases. It will dump each database to a seperate file, handy for mass virtual hosting where only one database needs to be restored. After a defined number of days, the script will clean up old backups using rm -rf, almost slightly cautiously.

This should ideally be run from cron.daily, but alternatively can be run manually in order to create a snapshot of all MySQL databases.

#!/bin/bash
 
########
# Author: David Cannings <david@edeca.net>
#   Date: 21/05/04
#
# TODO -> Create a backup user so that root isn't used for this.
#
# This script will dump all databases and tables MySQL knows about
# using the root account and password, then compress them using bzip2.
########
 
# The destination directory for backups
DESTDIR=/home/backup/mysql/
 
# The number of days you wish to keep backups for, excluding todays.  Thus,
# 6 would give a weeks worth of backups (7 files).
DAYS_TO_KEEP=6
 
# The mysql root password.  If you don't like setting it here, see ~/.my.cnf.
MYSQL_PASSWORD="password"
 
 
#################################################
# Nothing below this point should need changing #
#################################################
 
set -e
 
BZIP=$(which bzip2)
MYSQLDUMP=$(which mysqldump)
 
if [ ! -x "$BZIP" ]; then
   echo "ERROR: Could not locate or run bzip2";
   exit 1;
fi
 
if [ ! -x "$MYSQLDUMP" ]; then
   echo "ERROR: Could not locate or run mysqldump";
   exit 1;
fi
 
echo " * Dumping MySQL databases ..."
 
DATE=$(date +%d%b%Y)
 
mkdir -p ${DESTDIR}/${DATE}
 
for DBNAME in $(mysql --batch -e'SHOW DATABASES' -N); do
 
  FILENAME=${DESTDIR}/${DATE}/mysql_dump_${DBNAME}.sql
 
  echo -n "   - $DBNAME "
 
  if [ -z $MYSQL_PASSWORD ]; then
     MYSQL_PASSWORD=$( cat /root/.my.cnf | grep "password =" | awk -F'= ' '{print $2}' );
  fi
 
  $MYSQLDUMP -aieQ --hex-blob --add-drop-table --single-transaction -u root ${DBNAME} -p${MYSQL_PASSWORD} > $FILENAME
 
  echo "done: $(wc -l ${FILENAME} | awk '{print $1}') lines."
 
  echo -n "   - Compressing using bzip2 "
  $BZIP -f --best $FILENAME
  echo "done."
 
done
 
echo -n "Removing old backups.. "
find $DESTDIR -type f -name 'mysql_dump_*' -mtime +${DAYS_TO_KEEP} -exec rm -f '{}' \;
find $DESTDIR -type d -empty -exec rmdir '{}' \;
echo "done."