path: root/localdumpsql

#!/bin/bash
#
# /usr/local/sbin/localdumpsql
# Copyright 2001-2003 Jonas Smedegaard <dr@jones.dk>
#
# $Id: localdumpsql,v 1.13 2003-06-01 16:38:20 jonas Exp $
#
# MySQL/PostgreSQL maintenance script
#
# Automagically runs when symlinked to /etc/cron.{daily,weekly,monthly}/
#

# halt on errors
set -e

# Be paranoid about access to created files
umask 277

function usage()    {
    echo "Usage: `basename $0` daily|weekly|monthly|<whatever> [mysql|postgres [<user> [<db>]]]"
    echo "  If sqltype, user and db are not provided, all are included"
    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=""
    ;;
    /etc/cron.weekly)
    stamp="weekly"
    ;;
    /etc/cron.monthly)
    stamp="monthly"
    ;;
    *)
    if [ $# -lt 1 -o $# -gt 4 ]; then
        usage
    fi
    stamp="$1"
    sqltypes="$2"
    users="$3"
    databases="$4"
    ;;
esac

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

# Define paths
mysql_bin=/usr/bin/mysqldump
postgres_bin=/usr/lib/postgresql/bin/pg_dump

# Define routines
function gethome_user()     { getent passwd "$1" | awk -F: '{print $6}' | head -n 1; }
function getbackupdir_user()    { echo "`gethome_user \"$1\"`/backup"; }

function mysqlvalid()       { [ -x $mysql_bin ]; }
#function mysqlpasswd_user()    { grep password "`gethome_user $1`"/.my.cnf | awk -F= '{print $2}' | awk '{print $1}' | head -1; }
function mysql_users()      { if [ -n "$1" ]; then echo "$1"; else mysql -uroot mysql -e "select User from user;" | grep -Ev '^(User|debian-sys-maint|root|.+-admin)$' | sort -u; fi; }
function mysql_db_user()    { if [ -n "$1" ]; then echo "$1"; else mysql -u root mysql -re "select Db from db where User='$2';" | grep -v '^Db$' | sort -u; fi; }
#function mysqldump_user_db()   { $mysql_bin --opt --user="$1" --password="`mysqlpasswd_user $1`" "$2"; }
#function mysqldump_user_db()   { $mysql_bin --opt --user="root" --password="`mysqlpasswd_user root`" "$2"; }
function mysqldump_user_db()    { $mysql_bin --opt --user="root" "$2"; }

function postgresvalid()    { [ -x $postgres_bin -a -x /usr/lib/postgresql/bin/pg_ctl ]; }
function postgres_users()   { echo "root"; } #FIXME
function postgres_db_user() { if [ -n "$1" ]; then echo "$1"; else 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]$'"; fi; }
function postgresdump_user_db() { su -s /bin/sh postgres -c "$postgres_bin $2"; }

# 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 user in `${sqltype}_users "$users"`; do
            home="`gethome_user \"$user\"`"
            test -d "$home" || continue
            for dir in "`getbackupdir_user \"$user\"`" "`getbackupdir_user \"$user\"`/$sqltype"; do
                targetdir="$dir"
                if [ ! -d "$targetdir" ]; then
                    mkdir "$targetdir"
                fi
                chown "$user": "$targetdir"
                chmod u=rX,go= "$targetdir"
            done
            for db in `${sqltype}_db_user "$databases" "$user"`; do
                targetfile="$targetdir/$db${stamp:+.$stamp}.sql"
                (set +e; ${sqltype}dump_user_db "$user" "$db"; set -e) > "$targetfile"
                chown "$user": "$targetfile"
                chmod u=r,go= "$targetfile"
            done
        done
    fi
done