MySQL backup to disk

This script wraps mysqldump to efficiently back up and compress multiple databases to flat files. It supports different passwords and different hosts for each database and a fallback default to simplify configuration if all of the databases are in the same place.

The easiest way to run the script nightly is to use cron, either with a crontab or a reference (link or script) in /etc/cron.daily.

mysql-backup

#!/bin/sh
 
# Databases
db_list="site_wp"
 
# Defaults
default_db=""
default_host="localhost"
default_user="mysql-backup"
default_password="1337-pa33w0rd"
 
# example
#db_dbname_db="dbname"
#db_dbname_host="localhost"
#db_dbname_user="bob"
#db_dbname_password="supersecret"
 
# site WP
db_site_wp_db="site_wp"
 
# Directories
#output_dir=.
output_dir=/var/backup/mysql
 
# End of edit section
 
# vars to copy
vars="db host user password"
 
# Process databases
for dbi in $db_list
do
 
  echo "Backing up DB $dbi"
 
  for var in $vars
  do
    eval val=$`echo db_${dbi}_${var}`
    if [ -n "$val" ] ; then
      eval $var=${val}
    else
      eval $var=$`echo default_${var}`
    fi
  done
 
  mysqldump --opt --host=$host --user=$user --password=$password --compress $db | bzip2 > $output_dir/$dbi.sql.bz2
 
done