Log in



Backing up MySQL Databases

Sunday, April 17th, 2016 by

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

Comments are closed.

We (CLUG, or CLUG.Org) don't have a formal mission statement. Our goals are very simple. We have discovered GNU/Linux and want to talk to others who have made the same discovery. We are experts and novices, professionals and hobbyists, young and old. Whoever you are, we hope we have something for you. Come and check us out!