Random   •   Archives   •   RSS   •   About   •   Contact


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.



TODAY=`date +%Y-%m-%d`
MYSQLDUMP=`which mysqldump`
MYSQL=`which mysql`
GZIP=`which gzip`


# 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"

# compress all of the files

# 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

© Russell Ballestrini.