#! /bin/bash

# MySQL Backups
# martin.stricker@gmail.com 2013-03-20
# 2013-04-05 Anpassung Rechte

# Parameter
BACKUPDIR="/var/www/mysql-backups"  # Backupverzeichnis Root 
DAILYMAX=14     # Maximal taeglich
WEEKLYMAX=90    # Maximal woechentlich
MONTHLYMAX=360  # Maximal monatlich

# Keine Aenderungen ab hier
###########################

# Info
BACKUPDATE=$(date +%Y-%m-%d-%H-%M-%S)
echo "---------------------------------"
echo "MySQL Backups ${BACKUPDATE}"
echo "---------------------------------"

echo "... wechsle ins Verzeichnis ${BACKUPDIR}"
cd "${BACKUPDIR}"

# Backups erstellen
echo "... erstelle MySQL Dumps"

echo "... ... entferne evtl. vorhandenes Verzeichnis sql"
rm -vrf sql
echo "... ... lege neues Verzeichnis sql an"
mkdir -v sql

echo "... ... Dumps"
for i in $(mysql -BN -e "show databases;")
do 
  if [ $i != "information_schema" -a $i != "performance_schema" ] 
  then
    echo "... ... ... Dumping ${i} in sql/${i}.sql" 
    mysqldump $i > "sql/${i}.sql"
  else
    echo "... ... ... Auslassen: ${i}"
  fi
done

echo "... Komprimiere Verzeichnis sql/ in sql-${BACKUPDATE}.zip"
ZIPFILE="sql-${BACKUPDATE}.zip"
zip -9r ${ZIPFILE} sql

# Daily Backups
PDIR="daily"
PMIN=1
PMAX=${DAILYMAX}
echo "... Backups in ${PDIR}/ jeden Tag fuer maximal ${PMAX} Tage"
mkdir -pv ${PDIR} 
if [ $(find ./${PDIR}/sql* -nowarn -mmin -1410 | awk 'END{ print NR }') -lt 1 ] # Teste 23,5 Stunden, wg. Sekundenfrage/CRON
then
  echo "... ... Kopiere ${ZIPFILE} in ${PDIR}/"
  cp -v ${ZIPFILE} ${PDIR}/
else
  echo "... ... Backup in ${PDIR}/ juenger als ${PMIN} Tag bereits vorhanden"
fi
echo "... ... Loesche in ${PDIR}/ Backups aelter als ${PMAX} Tage"
find ./${PDIR}/sql* -nowarn -mtime +${PMAX} -print -delete
chown -R root:mcback ${PDIR}
chmod -R 750 ${PDIR}

# Weekly Backups
PDIR="weekly"
PMIN=7
PMAX=${WEEKLYMAX}
echo "... Backups in ${PDIR}/ alle ${PMIN} Tage fuer maximal ${PMAX} Tage"
mkdir -pv ${PDIR} 
if [ $(find ./${PDIR}/sql* -nowarn -mtime -${PMIN} | awk 'END{ print NR }') -lt 1 ]
then
  echo "... ... Kopiere ${ZIPFILE} in ${PDIR}/"
  cp -v ${ZIPFILE} ${PDIR}/
else
  echo "... ... Backup in ${PDIR}/ juenger als ${PMIN} Tage bereits vorhanden"
fi
echo "... ... Loesche in ${PDIR}/ Backups aelter als ${PMAX} Tage"
find ./${PDIR}/sql* -nowarn -mtime +${PMAX} -print -delete
chown -R root:mcback ${PDIR}
chmod -R 750 ${PDIR}

# Weekly Backups
PDIR="monthly"
PMIN=30
PMAX=${MONTHLYMAX}
echo "... Backups in ${PDIR}/ alle ${PMIN} Tage fuer maximal ${PMAX} Tage"
mkdir -pv ${PDIR} 
if [ $(find ./${PDIR}/sql* -nowarn -mtime -${PMIN} | awk 'END{ print NR }') -lt 1 ]
then
  echo "... ... Kopiere ${ZIPFILE} in ${PDIR}/"
  cp -v ${ZIPFILE} ${PDIR}/
else
  echo "... ... Backup in ${PDIR}/ juenger als ${PMIN} Tage bereits vorhanden"
fi
echo "... ... Loesche in ${PDIR}/ Backups aelter als ${PMAX} Tage"
find ./${PDIR}/sql* -nowarn -mtime +${PMAX} -print -delete
chown -R root:mcback ${PDIR}
chmod -R 750 ${PDIR}

# Quarterly Backups
PDIR="quarterly"
PMIN=90
echo "... Backups in ${PDIR}/ alle ${PMIN} Tage"
mkdir -pv ${PDIR} 
if [ $(find ./${PDIR}/sql* -nowarn -mtime -${PMIN} | awk 'END{ print NR }') -lt 1 ]
then
  echo "... ... Kopiere ${ZIPFILE} in ${PDIR}/"
  cp -v ${ZIPFILE} ${PDIR}/
else
  echo "... ... Backup in ${PDIR}/ juenger als ${PMIN} Tage bereits vorhanden"
fi
chown -R root:mcback ${PDIR}
chmod -R 750 ${PDIR}

# Abschliessen
echo "... Aufraeumen & aktuelle Dateien in last/ verschieben"
rm -v ${ZIPFILE}
rm -rf last
mv -vTf sql last
chown -R root:mcback last
chmod -R 750 last