====== 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 =====
<code bash>
#!/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
</code>