Bash script for dump all your MySQL databases

mysql-dump.sh

#!/bin/bash

# Specify number of backups to keep in days
NUMBER_OF_BACKUPS=3

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
IGNORE_LIST="SELECT schema_name FROM information_schema.SCHEMATA WHERE schema_name NOT LIKE '% %' AND  schema_name NOT LIKE '%-%' AND schema_name != 'information_schema' AND schema_name != 'mysql' AND schema_name != 'performance_schema';"

echo "Reading databases..."

databases=`$MYSQL -e "${IGNORE_LIST}" | tr -d "| " | grep -v schema_name`

today=`date +%Y-%m-%d`
mkdir $today

# Dump databases
for db in $databases; do
   echo "Dumping $db..."
   $MYSQLDUMP $db > /backup/db/$today/$db.sql
done

# Clean up old backups to save space
# Day before number of backups to keep
dataset_date=`date`
day_to_delete=`date -d "$dataset_date - $((NUMBER_OF_BACKUPS+1)) days" +%Y-%m-%d`

rm -rf $day_to_delete

# todo Generate a log
# todo Notify user of the backup event