summaryrefslogtreecommitdiffstats
path: root/mysql
diff options
context:
space:
mode:
Diffstat (limited to 'mysql')
-rwxr-xr-xmysql/clean_backups.sh125
-rw-r--r--mysql/ftp.settings9
-rwxr-xr-xmysql/mysql_backup.sh11
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"