added script to cleanup old backups but keep someHEADmaster

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"