#!/bin/bash

# This script makes backup dumps of all of the MySQL databases that the
# specified MySQL user can see, as well as a pg_dumpall PostgreSQL dump.

### Note: This script will only work if it is run as user 'postgres',
### because otherwise the postgres backup will fail.

#### Configuration: Edit the following lines.
# SQLBACKUPS: Directory to store backups in.
# MYSQLUSER: MySQL user to use for backups. Recommended to make a special
#   read-only user and grant it read-only (SELECT) access to the databases.
# MYSQLPWD: Password for MYSQLUSER.
# PGUSER: PostgreSQL root user (only the root user can execute pg_dumpall,
#   apparently). So hopefully you have no sensitive data in your database,
#   or else just make sure that only user 'postgres' can read this file.
# PGPASSWORD: Password for PGUSER.
# Note: It may not be necessary to supply the PGUSER and PGPASSWORD values.
SQLBACKUPS=/opt/miscscripts/sql_backups
MYSQLUSER=YOURMYSQLUSER
MYSQLPWD=YOURMYSQLPWD
PGUSER=ROOTPGUSER
PGPASSWORD=GLOBALPGPWD
#### End of configuration.

# Make a date-marked directory for backups.
DATESTR=`date '+%Y-%m-%d-h%Hm%Ms%S'`
SQLDIR=$SQLBACKUPS/$DATESTR
mkdir $SQLDIR

# Run the postgres backup.
echo "Backing up PostgreSQL"
/usr/bin/pg_dumpall | /bin/gzip > ${SQLDIR}/pgsql-dump.sql.gz

# Run the MySQL backup: first make a list of all the databases, then back up.
/usr/bin/mysql --batch --user=${MYSQLUSER} --password=${MYSQLPWD} -e 'show databases;' | /usr/bin/tail -n +2 > ${SQLDIR}/mysql.lst
for db in `cat ${SQLDIR}/mysql.lst`
do
   echo "Backing up MySQL database $db"
   mysqldump --skip-lock-tables --user=${MYSQLUSER} --password=${MYSQLPWD} $db | /bin/gzip > ${SQLDIR}/mysql-$db.sql.gz
done