Script for mysql backups

Here is my quick mysql backup script that I have used for a couple of years. Posting it here for reference sake...

#!/bin/bash
## if run daily (say, via cron) you will end up with a tar file that contains
## a mysqldump of each database on your server.
## the script will also delete backups that are older than two weeks
## 
## jpope <jpope at jpope dot org>
## licensed under WTFPL http://www.wtfpl.net/

## EDIT OPTIONS ##
workingdir=/path/to/mysql/backups
dte=$(date +%Y%m%d)
dbuser=username
dbpass=userpassword
dbhost=localhost
tmpfile=/tmp/databaselist
## END EDIT OPTIONS ##

dblist=$(mysql -u$dbuser -p$dbpass -h$dbhost -e"select distinct t.TABLE_SCHEMA \
from information_schema.tables t WHERE t.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema');" \
| grep -v "^+" | sed "s,\t,.," | grep -v TABLE_SCHEMA > $tmpfile)

cd $workingdir
mkdir $dte
cd $dte
databases=$( cat $tmpfile | wc -l )
i=1

## backup databases
$dblist

    while [ $i -le $databases ]
    do
    db=$(cat $tmpfile | sed -n $i\p)

    mysqldump -u$dbuser -p$dbpass -h$dbhost $db > backup$dte$db.sql

    i=$(( $i + 1 ))

    done

cd ..
tar zcf backups_$dte.tar.gz $dte/
find ./* -mtime +14 -exec rm {} \;
rm -r $dte/
rm $tmpfile

exit 0

I have a specific directory where I keep the backups (as determined by $workingdir. I also have this run every night to provide daily backups of which, the script will delete any backups older than 14 days. That way, it'll conserve some diskspace while providing me with plenty of backups.

I also have this script posted on my hastebin as well.


Have a response to this post? Please use this link.