dbbackup.sh

#!/bin/bash

# dbbackup.sh
# Descr: Dump MySQL database data into separate compressed SQL files.
# Usage: Run without args for usage info.
# Author: @Wuri Nugrahadi
# Ref: http://unix.stackexchange.com/questions/123189/cronjob-for-automatic-db-backup-to-date-prefixed-file
# Ref: http://stackoverflow.com/q/3669121/138325
# Notes:
#  * Output files are compressed and saved in the current working dir, unless
#    DIR is specified on command-line.
#  * if KEEP SPAN is specified on command-line, files older than KEEP SPAN days
#    will be deleted

[[ $# -lt 3 ]] && echo "Usage: $(basename $0) HOST USER PASSWORD [DIR] [KEEP SPAN]" && exit 1

DB_host=$1
DB_user=$2
DB_pass=$3
BASE_DIR=$4
KEEP_SPAN=$5

[[ -n "$BASE_DIR" ]] && BASE_DIR=$(echo "$BASE_DIR/" | tr -s /) || BASE_DIR=.
DATE="$(date +%F)"
DIR="$BASE_DIR$DB_host-$DATE"

test -d $DIR || mkdir -p $DIR

echo "Dumping database into separate SQL command files into dir=$DIR"

dontdump=(information_schema mysql performance_schema)
db_count=0

for db in $(mysql -NBA -h $DB_host -u $DB_user -p$DB_pass -e 'show databases')
do
    if ! [[ ${dontdump[*]} =~ "$db" ]]; then
        filename="$DIR/$db.sql.gz"

        [[ -f $filename ]] && rm $filename

        echo "Dumping database: $db"
        mysqldump -h $DB_host -u $DB_user -p$DB_pass --databases $db | gzip > $filename
        (( db_count++ ))
    fi
done

echo "Compressing SQL command files"

tarfile="$BASE_DIR$DB_host-$DATE.tar.gz"
cd $DIR
tar -zcvf $tarfile *

echo "$db_count databases dumped into $tarfile"

rm -r $DIR

if [[ -n $KEEP_SPAN ]]; then
    oldfile_count=0

    for oldfile in $(find $BASE_DIR -type f -mtime +$KEEP_SPAN)
    do
        rm $oldfile
        (( oldfile_count++ ))
    done

    [[ $oldfile_count -gt 0 ]] && echo "$oldfile_count dump files older than $KEEP_SPAN has been deleted"
fi