#!/bin/bash
# mysql_backup.sh: backup mysql databases and keep newest $backup_day(config.conf) days backup.  
#  
# ${db_user} is mysql username  
# ${db_password} is mysql password  
# ${db_host} is mysql host   
# —————————–  
#/root/mysql_backup.sh
# everyday 3:00 AM execute database backup
# 0 3 * * * /root/mysql_backup.sh
#/etc/cron.daily

CURRENT_DIR=$(cd "$(dirname "$0")";pwd)
source ${CURRENT_DIR}/config.conf

# the directory for story the newest backup  #
test ! -d ${backup_dir} && mkdir -p ${backup_dir}

# 计算忽略表选项
calc_ignore_table()
{
    dbname=$1
    ignore_table_string=''
    for it in ${ignore_table}
    do
        if [ ${dbname} == ${it%.*} ]
        then
            ignore_table_string=${ignore_table_string}" --ignore-table ${it}"
        fi
    done
}


#备份数据库函数#
mysql_backup()
{
    # 取所有的数据库名 #
    for db in ${all_db}
    do
        backname=${db}.${time}
        dumpfile=${backup_dir}${backname}

        ignore_table_string=''
        calc_ignore_table ${db}

        #将备份的时间、数据库名存入日志
        echo "-----"$(date +'%Y-%m-%d %T')" Beginning database "${db}" backup-----" >>${logfile}
        ${mysqldump} -F -u${db_user} -h${db_host} -p${db_password} ${ignore_table_string} ${db} > ${dumpfile}.sql 2>>${logfile} 2>&1
        
        #开始将压缩数据日志写入log
        echo $(date +'%Y-%m-%d %T')" Beginning zip ${dumpfile}.sql" >>${logfile}
        #将备份数据库文件库压成ZIP文件，并删除先前的SQL文件. #
        tar -czvf ${backname}.tar.gz ${backname}.sql 2>&1 && rm ${dumpfile}.sql 2>>${logfile} 2>&1 
        
        #将压缩后的文件名存入日志。
        echo "backup file name:"${dumpfile}".tar.gz" >>${logfile}
        echo -e "-----"$(date +'%Y-%m-%d %T')" Ending database "${db}" backup-----\n" >>${logfile}    
    done
}

delete_old_backup()
{    
    echo "delete backup file:" >>${logfile}
    # 删除旧的备份 查找出当前目录下指定天数前生成的文件，并将之删除
    find ${backup_dir} -type f -mtime +${backup_day} | tee delete_list.log | xargs rm -rf
    cat delete_list.log >>${logfile}
}

rsync_mysql_backup()
{
    # rsync 同步到其他Server中 #
    for j in ${web_ip}
    do                
        echo "mysql_backup_rsync to ${j} begin at "$(date +'%Y-%m-%d %T') >>${logfile}
        ### 同步 ###
        rsync -avz --progress --delete $serverPath -e "ssh -p "${ssh_port}" -i "${id_rsa} ${id_rsa_user}@${j}:$clientPath >>${logfile} 2>&1 
        echo "mysql_backup_rsync to ${j} done at "$(date +'%Y-%m-%d %T') >>${logfile}
    done
}

#进入数据库备份文件目录
cd ${backup_dir}

mysql_backup
delete_old_backup
rsync_mysql_backup

echo -e "============================mysql backup & rsync done at "$(date +'%Y-%m-%d %T')"============================\n\n">>${logfile}
cat ${logfile}