Skip to content

Instantly share code, notes, and snippets.

@duzun
Last active Aug 29, 2015
Embed
What would you like to do?
MySQL auto-backup script
#! /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
#!/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)}';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment