MySQL dump

From wiki.welmers.net

Update may 2008 - fixed bug with nonzero test before removing old increments. prevents removing entire dump dir

In September 2007 I needed a mysql dump script that can dump databases for backup purposes.

This script should maintain incremental dumps for the last 7 days.

This script is rsync/hardlink friendly since the previous dumps will not rotate into a new file but stay the same in the date directory until they wil be automaticly removed after 7 days.

This script will usually run under root daily via cron. Root access is not neccessary however.

#!/bin/sh
#
# MySQL dump script
# (C) 2007 Bastiaan Welmers
#
# http://wiki.welmers.net/en/MySQL_dump
#
# NOTE: the mysql password should be stored in ~/.my.cnf under:
# 
# [client]
# password=yourpassword
#
# This file should be mode 0600
#
# SO DO NOT STORE PASSWORDS IN THIS SCRIPT IN A WAY THEY WILL BE SEND
# WITH THE COMMAND LINE OF mysql OR mysqldump SINCE THEY COULD BE READ BY
# EVERYONE VIA THE `ps aux` COMMAND OUTPUT.


# !!!!!!!!!! CONFIGURE THIS VARIABLES TO YOUR LOCAL NEEDS !!!!!!!!!!
USER=root
DUMPDIR=/var/tmp/mysqldump
# !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

TODAY=`date +%Y_%m_%d`
mkdir $DUMPDIR/$TODAY

if [ ! -d $DUMPDIR/$TODAY ]; then
   echo "Error: cannot create directory $DUMPDIR/$TODAY"
   exit
fi

case `uname` in 
   NetBSD | OpenBSD | FreeBSD )
   WEEKAGO=`date -r $((\`date "+%s"\` - 604800)) +%Y_%m_%d`
;;
   Linux )
   WEEKAGO=`date -d "1 weeks ago" +%Y_%m_%d`
;;
   *)
   echo "Error: system 'uname' does not return either NetBSD, OpenBSD, FreeBSD or Linux. Please check the /bin/date of your system before using this script and adapt this script $0 if necessary"
   exit
;;
esac

for database in `echo show databases | mysql --user=$USER -B`; do
    if [ $database != Database ]; then
       mysqldump --user=$USER $database | gzip > $DUMPDIR/$TODAY/$database.sql.gz
    fi;
done;

if [ -n "$WEEKAGO" ]; then
   rm -rf $DUMPDIR/$WEEKAGO
fi