diff options
Diffstat (limited to 'mysql')
| -rwxr-xr-x | mysql/clean_backups.sh | 125 | ||||
| -rw-r--r-- | mysql/ftp.settings | 9 | ||||
| -rwxr-xr-x | mysql/mysql_backup.sh | 11 |
3 files changed, 136 insertions, 9 deletions
diff --git a/mysql/clean_backups.sh b/mysql/clean_backups.sh new file mode 100755 index 0000000..a0e60af --- /dev/null +++ b/mysql/clean_backups.sh @@ -0,0 +1,125 @@ +#!/bin/bash + +source $(dirname $0)/ftp.settings + +last_day=0 +last_month=0 +last_year=0 + +two_years_ago_y=$(date -d '-2years' +%Y) +two_years_ago_m=$(date -d '-2years' +%m) + +six_months_ago_y=$(date -d '-6month' +%Y) +six_months_ago_m=$(date -d '-6month' +%m) + +one_month_ago_y=$(date -d '-1month' +%Y) +one_month_ago_m=$(date -d '-1month' +%m) +one_month_ago_d=$(date -d '-1month' +%d) + +ncftpls -u"${FTP_USER}" -p"${FTP_PASSWORD}" "ftp://${FTP_SERVER}/${FTP_TARGET}/" | \ + sed -nr 's/(^.*)([0-9]{4})-([0-9]{2})-([0-9]{2}).*/\2\t\3\t\4 \1/gp' | \ + sort -n | uniq | awk -F'\t' '{ print $1" "$2" "$3}' | while read y m d name +do + + # older than 2 years, only keep 1 per month + + if [ $y -lt $two_years_ago_y -o \ + \( $y -eq $two_years_ago_y -a $m -lt $two_years_ago_m \) ]; then + if [ $last_month -eq $m ]; then + + ncftp -u"${FTP_USER}" \ + -p"${FTP_PASSWORD}" \ + "ftp://${FTP_SERVER}/${FTP_TARGET}/" >/dev/null <<EOF +rm "${name}${y}-${m}-${d}"* +quit +EOF + echo ${name}$y-$m-$d + + else + last_day=$d + last_month=$m + last_year=$y + fi + else + + # older than 6 months, only keep every 14 days + + if [ $y -lt $six_months_ago_y -o \ + \( $y -eq $six_months_ago_y -a \ + $((10#$m)) -lt $((10#$six_months_ago_m)) \) ]; then + + fourteen_days_ago_y=$(date -d "$y-$m-$d -13days" +%Y) + fourteen_days_ago_m=$(date -d "$y-$m-$d -13days" +%m) + fourteen_days_ago_d=$(date -d "$y-$m-$d -13days" +%d) + + if [ $last_year -gt $fourteen_days_ago_y -o \ + \( $last_year -eq $fourteen_days_ago_y -a \ + $((10#$last_month)) -gt \ + $((10#$fourteen_days_ago_m)) \) -o \ + \( $last_year -eq $fourteen_days_ago_y -a \ + $((10#$last_month)) -eq \ + $((10#$fourteen_days_ago_m)) -a \ + $((10#$last_day)) -ge \ + $((10#$fourteen_days_ago_d)) \) ]; then + + ncftp -u"${FTP_USER}" \ + -p"${FTP_PASSWORD}" \ + "ftp://${FTP_SERVER}/${FTP_TARGET}/" >/dev/null <<EOF +rm "${name}${y}-${m}-${d}"* +quit +EOF + echo ${name}$y-$m-$d + + else + last_day=$d + last_month=$m + last_year=$y + fi + else + + # older than 1 month, keep weekly + + if [ $y -lt $one_month_ago_y -o \ + \( $y -eq $one_month_ago_y -a \ + $((10#$m)) -lt \ + $((10#$one_month_ago_m)) \) -o \ + \( $y -eq $one_month_ago_y -a \ + $((10#$m)) -eq \ + $((10#$one_month_ago_m)) -a \ + $((10#$d)) -lt \ + $((10#$one_month_ago_d)) \) ]; then + + + seven_days_ago_y=$(date -d "$y-$m-$d -6days" +%Y) + seven_days_ago_m=$(date -d "$y-$m-$d -6days" +%m) + seven_days_ago_d=$(date -d "$y-$m-$d -6days" +%d) + + if [ $last_year -gt $seven_days_ago_y -o \ + \( $last_year -eq $seven_days_ago_y -a \ + $((10#$last_month)) -gt \ + $((10#$seven_days_ago_m)) \) -o \ + \( $last_year -eq $seven_days_ago_y -a \ + $((10#$last_month)) -eq \ + $((10#$seven_days_ago_m)) -a \ + $((10#$last_day)) -ge \ + $((10#$seven_days_ago_d)) \) ]; then + + + ncftp -u"${FTP_USER}" \ + -p"${FTP_PASSWORD}" \ + "ftp://${FTP_SERVER}/${FTP_TARGET}/" \ + >/dev/null <<EOF +rm "${name}${y}-${m}-${d}"* +quit +EOF + echo ${name}$y-$m-$d + + else + last_day=$d + last_month=$m + last_year=$y + fi + fi + fi + fi +done diff --git a/mysql/ftp.settings b/mysql/ftp.settings new file mode 100644 index 0000000..04a797b --- /dev/null +++ b/mysql/ftp.settings @@ -0,0 +1,9 @@ +# ftp server data +FTP_SERVER="backup" +FTP_USER="ftp" +FTP_PASSWORD="password" + +# target directory on the ftp (if no / at the beginning, relativ to +# the first directory after login) +FTP_TARGET="mysql" + diff --git a/mysql/mysql_backup.sh b/mysql/mysql_backup.sh index 2110b14..b48f3f6 100755 --- a/mysql/mysql_backup.sh +++ b/mysql/mysql_backup.sh @@ -2,6 +2,8 @@ # this script will backup all mysql databases from the server to a ftp # server +source $(dirname $0)/ftp.settings + # mysql server informations MYSQL_SERVER="localhost" MYSQL_USER="backup" @@ -11,15 +13,6 @@ MYSQL_PASSWORD="password" # TODO #MYSQL_EXCLUDE_DB="" -# ftp server data -FTP_SERVER="backup" -FTP_USER="ftp" -FTP_PASSWORD="password" - -# target directory on the ftp (if no / at the beginning, relativ to -# the first directory after login) -FTP_TARGET="mysql" - # temporary directory TMP_DIRECTORY="/tmp" |