#!/bin/sh
## crontab: min  hr mday month wday command
# 15   9  *    *     *    /Users/[your user name]/scripts/auto_mysql_backup.sh
## Restore from Backup
# gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
# or
# gunzip [backupfile.sql.gz]
# mysql -u [uname] -p[pass] [dbname] < [backupfile.sql]

HOSTNAME=localhost
MYSQLUSER=root
MYSQLPASS=123

##
BACKUP_DIR=/data/backup/mysql
BACKUP_SEQ=$(date '+%Y%m%d')_$(date '+%H%M')

mkdir -p $BACKUP_DIR

# Don't backup databases with these names
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
IGNORE_DB="^mysql\|_schema$\|sys"


for DB in $(mysql -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --batch --skip-column-names --execute="show databases" | grep -v ${IGNORE_DB} );
do
    FILENAME=${DB}_${BACKUP_SEQ}.sql.gzip
    echo "exec dump db ${DB}"
    mysqldump $DB -h $HOSTNAME -u $MYSQLUSER --password=${MYSQLPASS} --triggers --routines --events | gzip -c >  $BACKUP_DIR/$FILENAME
done