Mysql dump to separate files with archive exclusion

We run some LAMP server mostly on EC2 infrastructure. Considering the servers in different locations and environments there are no unified automate service for backpacking Mysql databases into separate files. And the other hand we don’t want to regular backup handle the fat archive tables.

So what we need is a mysql backup solution, probably a bash script what makes separate files per each db and ignores archive and memory engines of course with possibilities of gzipping.

I googled but I didn’t found what exactly we need, fortunately I found some similar like this one: http://carrotplant.com/en/blog/how-to-dump-all-mysql-databases-into-separate-files and I combine them together.

Finally there are the dump script.

#!/bin/bash

############
# Settings #
############

# mysql parameters
MYSQL_USER="root"
MYSQL_PASSWORD="******"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
BACKUP_LOCATION="/var/backups/ervername/mysql/"
BACKUP_SQLDIR=$BACKUP_LOCATION$(date +%Y-%m-%d_%H-%M)
EXCLUDE_DBS="(Database|information_schema|phpmyadmin|mysql)"
EXCLUDE_EGX="(MEMORY|ARCHIVE)"
GZIP_ENABLED=1

##############
# THE SCRIPT #
##############

echo "sophisticated dump of mysql databases"
echo "destination: $BACKUP_SQLDIR"
echo "ignore these databases: $EXCLUDE_DBS"
echo "ignore these engines: $EXCLUDE_EGX"
echo "gzipping: $GZIP_ENABLED"

#returns list of ignired tables
ignoredtables () {
   local IGNORES
   TABLES=`$MYSQL --user=$MYSQL_USER --password=$MYSQL_PASSWORD -e "USE $1; SHOW TABLE STATUS;"  | grep -E $EXCLUDE_EGX | awk '{print $1}'`
   for CURRENT_TB in $TABLES; do
       IGNORES="$IGNORES --ignore-table=$1.$CURRENT_TB"
   done
   echo $IGNORES
}

if [ ! -d "$BACKUP_SQLDIR" ]; then
    echo "make dir: "$BACKUP_SQLDIR
    mkdir -p $BACKUP_SQLDIR
fi

# get a list of databases
DATABASES=`$MYSQL --user=$MYSQL_USER --password=$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev $EXCLUDE_DBS`

# dump each database in turn
echo "dumping databases..."
TIME_SPENT=`date +%s`
OVERAL_SPENT=`date +%s`
for CURRENT_DB in $DATABASES; do
    echo $CURRENT_DB
    IGNORED_TABLES=`ignoredtables $CURRENT_DB`
    if [ $GZIP_ENABLED == 1 ]; then
        $MYSQLDUMP --force --opt --routines --user=$MYSQL_USER --password=$MYSQL_PASSWORD $IGNORED_TABLES $CURRENT_DB | gzip > "$BACKUP_SQLDIR/$CURRENT_DB.sql.gz"
    else
        $MYSQLDUMP --force --opt --routines --user=$mYSQL_USER --password=$MYSQL_PASSWORD $IGNORED_TABLES $CURRENT_DB > "$BACKUP_SQLDIR/$CURRENT_DB.sql"
    fi
    TIME_SPENT=$((`date +%s` - $TIME_SPENT))
    echo "spent: "$TIME_SPENT"s         overal: "$((`date +%s` - $OVERAL_SPENT))"s"
    TIME_SPENT=`date +%s`
done

# removes previous backup older then 7 days
find $BACKUP_LOCATION -mtime +7 -type d -exec rm -Rv {} \;

echo "[done]"