localdumpsql



#!/bin/bash
# /etc/cron.daily/localdumpsql: MySQL/PostgreSQL maintenance script
# Written by Jonas Smedegaard <dr@jones.dk>

# halt on errors
set -e

function usage()    {
    echo "Usage: `basename $0` daily|weekly|monthly|<whatever> [mysql|postgres]"
    echo "  If sqltype is missing, all are attempted"
    echo "  Tip: Automagically runs when symlinked to /etc/cron.{daily,weekly,monthly}/"
    exit 1
}

# automagically configure when run from cron dirs
case `dirname $0` in
    /etc/cron.daily)
    stamp=daily
    ;;
    /etc/cron.weekly)
    stamp=weekly
    ;;
    /etc/cron.monthly)
    stamp=monthly
    ;;
    *)
    if [ $# -lt 1 -o $# -gt 2 ]; then
        usage
    fi
    stamp=$1
    sqltypes=$2
    ;;
esac

# Default is all sqltypes
[ -z $sqltypes ] && sqltypes="mysql postgres"

# Define paths
targetdir=/var/local/backups/localhost
mysql_bin=/usr/bin/mysqldump
postgres_bin=/usr/lib/postgresql/bin/pg_dump

# Define routines
function mysql_valid()      { [ -x $mysql_bin ]; }
function mysql_get_pw()     { grep password /root/.my.cnf | awk -F= '{print $2}' | head -1 | sed 's/^ //g'; }
function mysql_list_db()    { echo "show databases"|mysql -uroot -p`mysql_get_pw`|grep -v '^Database$'; }
function mysql_dump_db()    { $mysql_bin -c --add-drop-table -uroot -p`mysql_get_pw` $1; }

function postgres_valid()   { [ -x $postgres_bin -a -x /usr/lib/postgresql/bin/pg_ctl ]; }
function postgres_list_db() { su -s /bin/sh postgres -c "/usr/bin/psql -t -c 'select datname from pg_database order by datname' -d template1 | sed -e 's/ //' | grep -v '^template[01]$'"; }
function postgres_dump_db() { su -s /bin/sh postgres -c "$postgres_bin $1"; }

# Exit if the directory isn't there
if [ ! -d "$targetdir" ]; then
    echo "Directory $targetdir doesn't exist!"
    exit 1
fi

# Check for valid input
for sqltype in $sqltypes; do
    case "$sqltype" in
        mysql|postgres)
        ;;
        *)
        usage
        ;;
    esac
done

for sqltype in $sqltypes; do
    if ${sqltype}_valid; then
        for db in `${sqltype}_list_db`; do
            targetfile=$targetdir/dump_${stamp}_${db}.${sqltype}
            [ -f $targetfile ] && savelog -c 3 $targetfile >/dev/null
            ${sqltype}_dump_db $db > $targetfile
        done
    fi
done