db_backup.sh

#!/bin/bash

#####
# db_backup.sh
# This script is intended to run nightly and makes SQL dumps of each of your
# MySQL and PostgreSQL databases.
#
# Prerequisites:
#  * You must have a database user in each server with proper privileges:
#    * MySQL: SELECT, RELOAD, LOCK TABLES, and EVENTS on each database
#    * Postgres: Superuser? (TODO: Figure out minimum privs necessary)
#  * For MySQL, use a .cnf file only root can read to supply username,
#    password, and options.
#
# CAUTION!
# The Postgres portion of the script is somewhat unintelligent with its
# skipping of non-databases in the output. Double check that all of your
# databases are being backed up, and tweak the sed pattern if any aren't.
#####

DATE=`/bin/date +%Y-%m-%d`

BKUP_DIR=/var/www/dbbackup

####
# Cleanup
####
#Only keep 1 weeks' worth of backups on the server
/usr/bin/find $BKUP_DIR -mtime +7 -delete

####
# MySQL
####
MYSQL_DIR=$BKUP_DIR/mysql/$DATE
/bin/mkdir -p $MYSQL_DIR
#Dump a backup of each database
for DB in $(/usr/bin/mysql --defaults-file=/etc/mysql/backups.cnf -e 'show databases' --skip-column-names | \
        sed '/^\(staging\|performance_schema\|information_schema\|lost+found\)$/d'); do
        /usr/bin/mysqldump --defaults-file=/etc/mysql/backups.cnf $DB | \
                /bin/bzip2 > $MYSQL_DIR/$DB.sql.bz2
done

####
# PostgreSQL
####
PSQL_DIR=$BKUP_DIR/psql/$DATE
/bin/mkdir -p $PSQL_DIR
#Dump a backup of all databases
for DB in $(sudo -ubackups /usr/bin/psql -l | awk '{ print $1 }' | sed '/^[-|(]\|^List\|^Name\|^template[01]/d'); do
        sudo -ubackups /usr/bin/pg_dump $DB | /bin/bzip2 > $PSQL_DIR/$DB.sql.bz2
done

I run this one as simply db_backup in /etc/cron.daily/ -- make sure you remove the extension if you put it here though! Alternatively you can add a line in your crontab, and then the extension doesn't matter.