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