mysql-back

mysql-backis a backup utility script to dump (backup) and gzip every MySQL database on a host.

I use mysql-back in combination with cron to perform regular database dumps of MySQL servers to the /archive/db partition on localhost. I then have a central long term storage server that collects the /archive partition from every host.

mysql-back:

#!/bin/bash

USER="root"
PASSWORD=""
TODAY=`date +%Y-%m-%d`
OUTPUTDIR="/archive/db/$TODAY"
MYSQLDUMP=`which mysqldump`
MYSQL=`which mysql`
GZIP=`which gzip`

mkdir $OUTPUTDIR

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.sql"
done

# compress all of the files
$GZIP $OUTPUTDIR/*

# clean up all dumps 30 days old
find /archive/db -ctime +30 -type d | xargs rm -rf

If you ever need to restore, like in my case when I moved all my databases over to a brand new Percona MySQL SmartOS zone, I used the following command:

zcat *sql.gz | mysql -u root -p