#!/bin/bash

DATE_PREFIX=$(date +%G-%m-%d)

MYSQL_USER="root"
MYSQL_PWD="secret"


BACKUP_DIR="/var/backups/mysql"

GZIP="/bin/gzip"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQLCLIENT="/usr/bin/mysql"
FIND="/usr/bin/find"

DAYS_TO_KEEP_OLD_BACKUPS="4"  #how many days should you keep the old backups

#remove old backups
$FIND $BACKUP_DIR -maxdepth 0 -mtime +$DAYS_TO_KEEP_OLD_BACKUPS -exec rm -f {} \;

# get a list of databases
DBLIST=$( ${MYSQLCLIENT} --skip-column-names --batch --user=$MYSQL_USER --password=$MYSQL_PWD  --execute="SHOW DATABASES;" )

#dump databases in seperate files an gzip them

umask 027
cd $BACKUP_DIR || exit 1

for db in $DBLIST; do
    SKIP_LOCK=""
    [ "$db" == "information_schema" ] && SKIP_LOCK="--skip-lock-tables"
    DUMP_FILE_NAME="${DATE_PREFIX}_${db}.tar.gz"
    $MYSQLDUMP --force --opt $SKIP_LOCK --user=$MYSQL_USER --password=$MYSQL_PWD --databases $db | \
         $GZIP > $DUMP_FILE_NAME
    [ -f current_${db}.tar.gz ] && rm current_${db}.tar.gz
    ln $DUMP_FILE_NAME current_${db}.tar.gz
done