Random   •   Archives   •   RSS   •   About   •   Contact

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




Want comments on your site?

Remarkbox — is a free SaaS comment service which embeds into your pages to keep the conversation in the same place as your contentr. It works everywhere, even static HTML sites like this one!

Remarks: mysql-back

© Russell Ballestrini.