Shell script to backup MySQL databases into separate directories/files. Written to backup my local databases to Dropbox but could be easily adapted. The `trash` config item can be used to move files to a specified directory instead of erasing them. Be warned; This script cleans up after itself by decompressing and comparing, via MD5 hashes, the …

mysql_backup.sh

#!/bin/bash
#
# Joshua Baker (http://joshuabaker.com/)
# 2013
# 
# Backup MySQL databases to separate directories, compressed with GZIP
# and timestamped. Also cleans up after itself, comparing GZIP 
# contents via MD5 hashes.


#---------------------------------------------------------------------
# Basic config

db_host=""
db_user=""
db_pass=""

backup_path="/backup/mysql/"


#---------------------------------------------------------------------
# Advanced config

mysql="/usr/bin/mysql"
mysqldump="/usr/bin/mysqldump"

trash=""


#---------------------------------------------------------------------
# Database dump logic

# Create the backup directory if it doesn’t exist
if [ ! -d $backup_path ]; then
   mkdir -p $backup_path
fi

# Get a list of all the databases for this server
db_list=`$mysql --user=$db_user --password=$db_pass -e "show databases" | grep -Ev "(Database|information_schema|performance_schema)"`

# Get the current date for our file timestamp
timestamp=`date +"%y-%m-%d_%H-%M"`

# Loop through the databases
for db_name in $db_list; do   
   # Calculate our database path
   db_path="${backup_path%/}/$db_name"
   
   # Create a directory based on the database name
   if [ ! -d $db_path ]; then
      mkdir -p $db_path
   fi
   
   # Dump the SQL into a GZIP file
   $mysqldump --force --opt --user=$db_user --password=$db_pass --databases $db_name --skip-comments | gzip > "$db_path/${db_name}_${timestamp}.sql.gz"
done


#---------------------------------------------------------------------
# Cleanup logic

# Create trash directory
if [ "$trash" != "" ] && [ ! -d $trash ]; then
   mkdir -p $trash
fi

for db_dir in `ls $backup_path`; do
   [[ ! -d $db_dir ]] || continue;
   
   db_path="${backup_path%/}/${db_dir}"
   echo $db_path
   
   # Get most recent database dumps
   newest1="${db_path}/`ls -t $db_path | head -1`"
   newest2="${db_path}/`ls -t $db_path | head -2 | tail -1`"

   # Check that both our files exist
   if [ -f $newest1 ] && [ -f $newest2 ]; then
      ldiff="${db_path}/ldiff"
      cp $newest1 "${ldiff}.gz"
      gzip -d -f "${ldiff}.gz"
      
      rdiff="${db_path}/rdiff"
      cp $newest2 "${rdiff}.gz"
      gzip -d -f "${rdiff}.gz"

      # Compare MD5 hashes
      if [ `md5 -q $ldiff` == `md5 -q $rdiff` ]; then
         # Erase older file or move to specified trash directory
         if [ "$trash" != "" ]; then
            mv $newest2 $trash
         else
            rm $newest2
         fi
      fi
      
      # Clear up the cruft
      rm $ldiff
      rm $rdiff
   fi
done