#!/bin/sh

#
# For mysqldump command on cron
#
# @example Daily backup wo soutei shiteimasu.
#          crontab -l
#          0 5 * * * /bin/sh /path/to/script_dir/mysqldump4cron.sh
# @dependency Linux (x FreeBSD)
# @author kjirou <kjirou[dot]web[at]gmail[dot]com>
#                <http://kjirou.sakura.ne.jp/mt/>
# @license MIT License http://www.opensource.org/licenses/mit-license.php
#

#----------------------------------
# Env
# * Directory wo arawasu toki ha matsubi ni "/" wo kanarazu tsukeru koto!
#----------------------------------
MYSQLDUMP_CMD='/usr/local/mysql/bin/mysqldump'
MYSQL_HOST='127.0.0.1'
MYSQL_USERNAME='username'
MYSQL_PASSWORD='password'
MYSQL_DATABASE='target_database_name'
BACKUP_BASE_DIR='/path/to/backup_dir/'
WEEKLY_TARGET_DAY_OF_WEEK="0" # ex) "0"=sun ... "6"=sat
MONTHLY_TARGET_DAY="01" # ex) "01", "31"
DAILY_MAX_LIFE="7" # day
WEEKLY_MAX_LIFE="30" # day
MONTHLY_MAX_LIFE="365" # day


#----------------------------------
# Variables
#----------------------------------
DAILY_BACKUP_DIR=$BACKUP_BASE_DIR'daily/'
WEEKLY_BACKUP_DIR=$BACKUP_BASE_DIR'weekly/'
MONTHLY_BACKUP_DIR=$BACKUP_BASE_DIR'monthly/'
CURRENT_DATETIME_STR=`date +%Y%m%d_%H%M%S`
MYSQLDUMP_FILENAME='mysqldump_'$MYSQL_DATABASE'_'$CURRENT_DATETIME_STR'.sql'
MYSQLDUMP4CRON_BASE_CMD=$MYSQLDUMP_CMD' -h'$MYSQL_HOST' -u'$MYSQL_USERNAME' -p'$MYSQL_PASSWORD' '$MYSQL_DATABASE
TODAY=`date +%d`
DAY_OF_WEEK=`date +%w`


#----------------------------------
# Functions
#----------------------------------
delete_old_file()
{
    backup_dir=$1
    max_life=$2

    for i in `ls $backup_dir`
    do
        backuped=`echo $i | sed -e 's/^mysqldump_.\+_\([0-9]\+\)_[0-9]\+\.sql$/\1/'`
        limit=`date -d "-$max_life days" +%Y%m%d`
        if [ "$backuped" -le "$limit" ]; then
            rm $backup_dir$i
            echo " ... Deleted '$backup_dir$i'."
        fi
    done
}


#----------------------------------
# Processing
#----------------------------------

echo "Started 'mysqldump4cron' at '"`date '+%Y-%m-%d %H:%M:%S'`"'."

# For abnormal pattern and error pattern
mkdir -p $DAILY_BACKUP_DIR $WEEKLY_BACKUP_DIR $MONTHLY_BACKUP_DIR

# Dump
$MYSQLDUMP4CRON_BASE_CMD > $DAILY_BACKUP_DIR$MYSQLDUMP_FILENAME
echo " ... Dumped '$DAILY_BACKUP_DIR$MYSQLDUMP_FILENAME'."
if [ "$DAY_OF_WEEK" = "$WEEKLY_TARGET_DAY_OF_WEEK" ]; then
    $MYSQLDUMP4CRON_BASE_CMD > $WEEKLY_BACKUP_DIR$MYSQLDUMP_FILENAME
    echo " ... Dumped '$WEEKLY_BACKUP_DIR$MYSQLDUMP_FILENAME'."
fi
if [ "$TODAY" = "$MONTHLY_TARGET_DAY" ]; then
    $MYSQLDUMP4CRON_BASE_CMD > $MONTHLY_BACKUP_DIR$MYSQLDUMP_FILENAME
    echo " ... Dumped '$MONTHLY_BACKUP_DIR$MYSQLDUMP_FILENAME'."
fi

# Delete
delete_old_file $DAILY_BACKUP_DIR $DAILY_MAX_LIFE
delete_old_file $WEEKLY_BACKUP_DIR $WEEKLY_MAX_LIFE
delete_old_file $MONTHLY_BACKUP_DIR $MONTHLY_MAX_LIFE

echo "Ended 'mysqldump4cron' at '"`date '+%Y-%m-%d %H:%M:%S'`"'."