Backing up MySQL Databases

This is the script I run every morning to back up all of the databases in my MySQL database – it gets every database, including mysql, which has the users and access rights for users.

Obviously, you’ll need to modify a few of the variables in the script, but it shouldn’t be difficult. There are a few comments in the script. Feel free to ask questions if you have them, you can always e-mail me with steve at clug dot org.

#!/bin/sh
# MySQL backup script
# With a few modifications by Steve Jones
### System Setup ###
BACKUP=$HOME/.MySQL-Backup
### MySQL Setup ###
MUSER="root"  ;  MPASS="Secret!"  ;  MHOST="localhost"
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
GZIP=$(which gzip)
NEW=$(date +%Y-%m-%d)
OLD=$(date -d "7 days ago" +%Y-%m-%d)
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### Start MySQL Backup ###
# Get all databases name
ALL=$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')
for DB in $ALL
  do
  ### The perf_schema DB doesn't have events, it isn't even real.
  if [ "$DB" = "performance_schema" ]; then
    OPTS="--single-transaction --add-drop-table"
    else
    OPTS="--single-transaction --events --add-drop-table"
  fi
  NEWFILE=$BACKUP/$NEW-$DB.sql.gz
  OLDFILE=$BACKUP/$OLD-$DB.sql.gz
  $MYSQLDUMP $OPTS -u $MUSER -h $MHOST -p$MPASS $DB \
  | $GZIP -9 > $NEWFILE
  ### If an oldfile exists, remove it. An added feature of this is that
  ### if you drop a DB, the last few days of its life will be here forever
  [ -f $OLDFILE ] && rm -f $OLDFILE
done