Backing up MySQL (was: Re: Drupal talk pdf and webcast)

[david-8uUts6sDVDvs2Lz0fTdYFQ at public.gmane.org (David Kramer)]

Doug wrote:
> Hello:
> 
> Links for my talk on Drupal are up on Blu.org.  There is even 1 bonus
> slide that provides a few comments on service providers and backups.
> After the talk, I did a little more search on backups.  There is a
> module that makes backing up the mysql database just a few mouse
> clicks away.  One can still do the phpmyadmin way, but this approach
> automatically skips the cache files, a nice feature.

Here's how  I back up MySQL:

BACKDIR=/FOO/backups
DATE=`date '+%y%m%d'`
TARBALL=backup_mysql_${HOSTNAME}_${DATE}.tgz
BACKUP_DAYS=14

cd ${BACKDIR}

DATABASES=`echo "show databases" | mysql -uroot -pFOO -qN`

for DB in ${DATABASES} ; do
    mysqldump -uroot -pFOO --add-drop-table --databases ${DB} >
mysql_${DB}.sql ;
done

# Tar/gzip the individual files into one file
tar czf ${TARBALL} *.sql
chmod 700 ${TARBALL}
rm *.sql

# Remove old backups
if [[ -f ${TARBALL} ]] ; then
    find . -name "backup_mysql_${HOSTNAME}_"'*.tgz' -mtime
+${BACKUP_DAYS} | xargs -r rm
fi