MySQL Backup Script

mysqldump-backup.sh

#!/bin/bash

FREQUENCY=${1:-daily}
DB_BACKUP_DIR_ROOT="/root/mysqlbackups/$FREQUENCY"
DB_BACKUP_DIR_TODAY="$DB_BACKUP_DIR_ROOT/`date +%Y-%m-%d`"
DATESTRING=$(date +%Y.%m.%dT%H-%M-%S)

# Create the backup directory
mkdir -p $DB_BACKUP_DIR_TODAY

# Remove backups older than 1 day
find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime +1 -exec rm -rf {} \;

# Backup each db in the server, skip schema dbs though
# Note, no username or password in this script, you should use mysql_config_editor to store is securely
# See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
for db in $(mysql -Bse 'show databases'|egrep -vi 'information_schema|performance_schema')
do
  echo "Dump '$db' to '$DB_BACKUP_DIR_TODAY/$db-$DATESTRING.sql.gz'";
  mysqldump $db | gzip > "$DB_BACKUP_DIR_TODAY/$db-$DATESTRING.sql.gz";
done

# Export privileges
mysql -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' | gzip > "$DB_BACKUP_DIR_TODAY/privileges.sql.gz";