MySQL auto-backup script

backup.sh

#! /bin/sh
#
# Author: Dumitru Uzun (DUzun)
#
# Ussage: 
#   backup [-k <days>] [-s <subdir>] [-d <database>] [-e <exclude_tables_list>]
#

pass='<best-password-ever>'
user=backup
bak_dir='/home/backups/db/'
sub_dir=''
keep_days=2
keep_year=1461
grep='-v mysql'
excl_tns=''

while getopts :k:s:d:e: arg;
do
    case "$arg" in
        k) keep_days="$OPTARG";;
        s) sub_dir="$OPTARG";;
        d) grep="$OPTARG";;
        e) excl_tns="$OPTARG";;
        \?) echo '-?-';;
    esac;
done


if [ -n "$excl_tns" ]; 
then 
    excl_tns=$(echo $excl_tns | tr ',; \t' "\n\n\n\n");
    a=''
    for i in $excl_tns; do 
        if [ -n "$a" ]; then a=$a' OR '; fi; 
        a="${a}table_name LIKE '$i'"; 
    done;
    excl_tns=$a;
fi;

bdir="$bak_dir/$sub_dir";
mysql="/usr/bin/mysql -s -u $user -p$pass";
mysqldump="/usr/bin/mysqldump -u $user -p$pass";

if [ ! -d $bdir ]; then mkdir $bdir; fi;

databases=`echo show databases | $mysql | grep -v _schema | grep -v admin | grep $grep`;

sleep=1;
for db in $databases; 
do
    date=`date +%Y-%m-%d_%H.%M`;
    if [ "$sleep" -gt 0 ]; then t1=`date +%s`; fi;
    if [ -n "$excl_tns" ];
    then
        tl="SELECT table_name FROM information_schema.tables WHERE table_schema='$db' AND NOT ($excl_tns) ORDER BY DATA_LENGTH ASC"
        tl=`$mysql -e"$tl"`
    else
        tl=''
    fi;
    $mysqldump $db $tl | bzip2 -9 > $bdir/${date}_$db.sql.bz2;
    
    t2=`date +%s`;
    sleep=$(( $t2 - $t1 ));
    sleep $sleep;
done;

if [ "$keep_year" -gt 365 ]; then 
    find $bdir -type f -mtime +$keep_year -delete; 
fi

# On FreeBSD there is realpath command
#dir=`dirname $(realpath $0)`/;

# When realpath is missing, user readlink instead
dir=`dirname $(readlink -f $0)`/;

$dir/bak_cln.sh $bdir $keep_days

bak_cln.sh

#!/bin/sh
bak_dir="/home/backups/db/"
keep_days=2
keep_mon=14

if [ $# -gt 0 ]; then bak_dir=$1;
if [ $# -gt 1 ]; then keep_days=$2;
fi; fi;

spnd() {                                                                                    # dir date  short_name                               basename 
   while read f; do echo $(dirname $f)/ $(basename $f); done | awk '{split($2, a, "_"); print $1, a[1], substr($2, length(a[1])+length(a[2])+3), $2}';
}

spnm() {                                                                                    # dir date                short_name                  basename 
   while read f; do echo $(dirname $f)/ $(basename $f); done | awk '{split($2, a, "_"); print $1, substr(a[1], 0, 7), substr($2, length(a[1])+2), $2}';
}

find $bak_dir -type f -mtime +$keep_days"" -name "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]_[0-9][0-9].[0-9][0-9]_*.sql*" | spnd | awk '{system("cp -p "$1$4" "$1$2"_"$3);system("unlink "$1$4)}';
find $bak_dir -type f -mtime +$keep_mon"" -name "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]_*.sql*" | spnm | awk '{system("cp -p "$1$4" "$1$2"_"$3);system("unlink "$1$4)}';