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