#!/bin/sh
#
#db_backup.sh
#
# Script to dump mysql and postgresql databases before nightly backup
#
# Use chmod 700 on this file so only root (or sudo) can view contents

# Backup destination dir
BACKUP="/root/sql_backup"

# Mysql password
MYSQLPW="yourpassword"

# Mysql username
MYSQLUSER="root"

# Location of the backup logfile.
logfile="$BACKUP/sql_backup.log"

################### end of configuration variables ###################

# Create the backup destination if it doesn't exist
if [ ! -d $BACKUP ]; then 
    mkdir -p $BACKUP
    echo "Created backup destination directory..." >> $logfile
fi

# set the date and time
timeslot=`date +%H-%M`

#
# Postgresql
#

# backup individual databases
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
    timeinfo=`date '+%T %x'`
    /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1 
    /usr/bin/pg_dump $i -h localhost -U postgres | gzip > "$BACKUP/postgresql-$i-database.sql.gz"
    echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
done

# dump all databases at once
/usr/bin/pg_dumpall --clean -h localhost -U postgres | gzip > "$BACKUP/postgresql-all-databases.sql.gz"
echo "Backup complete at $timeinfo for time slot $timeslot on all PostreSQL databases at once" >> $logfile

#
# Mysql
#

# One big dump
/usr/bin/mysqldump -u $MYSQLUSER -p$MYSQLPW --add-locks --add-drop-table -A -Q | gzip > "$BACKUP/mysql-all-databases.sql.gz"
echo "Backup complete at $timeinfo for time slot $timeslot on all Mysql databases at once" >> $logfile

# Individual databases

for db in `mysql -u $MYSQLUSER -p$MYSQLPW -BNre "show databases;"`
do
    mysqldump -u $MYSQLUSER -p$MYSQLPW --add-drop-table -QB "$db" | gzip > "$BACKUP/mysql-$db-database.sql.gz"
    echo "Backup complete at $timeinfo for time slot $timeslot on database: $db" >> $logfile
done

exit