Backup database using mysqldump with daily, weekly and monthly incremental

backup.sh

#!/bin/bash

#----------------------------------------------------
# Preventive Maintenance Script
# Coded by Pikri Mohammad
# Updated on 5 April 2017
#----------------------------------------------------

MYSQL_USER="root"
MYSQL_HOST="localhost"
MYSQL_PASSWORD="password"
MYSQL_BIN="$(which mysql)"
MYSQLDUMP_BIN="$(which mysqldump)"

STORE_FOLDER="/root/backup/mysql/"

TODAY=$(date +"%Y-%m-%d")

for db in 'db1' 'db2' 'db3'
do
  THE_PATH=$STORE_FOLDER/$db

  if [ ! -d "$THE_PATH" ]; then
    mkdir -p "$THE_PATH"
  fi

  #daily
  $MYSQLDUMP_BIN -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PASSWORD $db | gzip -9 > $THE_PATH/daily-$TODAY.sql.gz

  #weekly
  if [ `date +%u` -eq 7 ];then
    mv $THE_PATH/daily-$TODAY.sql.gz $THE_PATH/weekly-$TODAY.sql.gz
    rm -f $THE_PATH/daily-*
  fi

  #monthly
  if [ `date +%d` -eq 28 ];then
    mv $THE_PATH/daily-$TODAY.sql.gz $THE_PATH/monthly-$TODAY.sql.gz
    rm -f $THE_PATH/weekly-*
    find $THE_PATH/* -name "monthly-*" -mtime +60 -exec rm -f {} \;
  fi
done