#!/bin/bash
 
today=$(date +%y%m%d)
 
# local dir where the backups go
myDir='/data/dbbackups'
# remote dir where we'll send them offsite
myRemoteDir='/data/backup/li35-166/dbbackups'
 
 
# this is for PostgreSQL. If you don't need it, you
# could leave it here, but remove the 'backup_pgsql'
# function call at the end of the script
 
function backup_pgsql {
  for db in `su postgres -c "psql -U postgres -qAtc '\l'" | cut -f1 -d\|
| grep -v '^template[01]'`; do
    mkdir -p $myDir/${db};
    su postgres -c "pg_dump $db" | bzip2 > $myDir/${db}/${db}-$today.bz2;
  done;
}
 
# MySQL backup function
 
function backup_mysql {
  myFile='/etc/mysql/debian.cnf'
  myUser=`cat $myFile | grep user | awk '{print $3}' | uniq`
  myPass=`cat $myFile | grep password | awk '{print $3}' | uniq`
 
  for db in `mysql -u$myUser -p$myPass -e "show databases" | cut -f2 -d\
| grep -v Database`; do
    mkdir -p $myDir/${db};
    mysqldump -u$myUser -p$myPass $db | bzip2 >
$myDir/${db}/${db}-$today.bz2;
  done;
}
 
# here are the functions that actually get called.
# You might want to comment out the postgres one
 
backup_pgsql
backup_mysql
 
# rsync the contents of the local backup store
# to the remote machine. Since rsync is incremental
# this will only transfer today's dumps across,
# presuming that the remote end has all up until that
# point already
# obviously using ssh auth keys here
 
rsync -aHPq $myDir/ git.mig5.net:$myRemoteDir/