#!/bin/bash

 # mysql_backup.sh: backup mysql databases and keep newest  days backup.

 #

 # ${db_user} is mysql username

 # ${db_password} is mysql password

 # ${db_host} is mysql host

 # —————————–

 #/root/mysql_backup.sh

 # everyday : AM execute database backup

 #   * * * /root/mysql_backup_to_oss.sh

 #/etc/cron.daily

 # the directory for story your backup file.  #

 backup_dir="/opt/backup/mysql/"

 # 要备份的数据库名 #

 all_db="mydb1 mydb2 mydb3"

 # 要保留的备份天数 #

 backup_day=

 #数据库备份日志文件存储的路径

 logfile="/var/log/mysql_backup.log"

 # Aliyun OSS Bucket Name.

 bucket_name=aliyunoss-backup

 # local IP address

 local_ip=`hostname -i`

 # date format for backup file (dd-mm-yyyy)  #

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

 # mysql, ${mysqldump} and some other bin's path  #

 mysql="/usr/bin/mysql"

 mysqldump="/usr/bin/mysqldump"

 # the directory for story the newest backup  #

 test ! -d ${backup_dir} && mkdir -p ${backup_dir}

 #备份数据库函数#

 mysql_backup()

 {

     # 取所有的数据库名 #

     for db in ${all_db}

     do

         backname=${db}.${time}

         dumpfile=${backup_dir}${backname}

         #将备份的时间、数据库名存入日志

         echo "------"$(date +'%Y-%m-%d %T')" Beginning database "${db}" backup--------" >>${logfile}

         ${mysqldump} --login-path=my3306 ${db} > ${dumpfile}.sql >>${logfile} >&

         #开始将压缩数据日志写入log

         echo $(date +'%Y-%m-%d %T')" Beginning zip ${dumpfile}.sql" >>${logfile}

         #将备份数据库文件库压成ZIP文件，并删除先前的SQL文件. #

         tar -czvf ${backname}.tar.gz ${backname}.sql >& && rm ${dumpfile}.sql >>${logfile} >& 

         #将压缩后的文件名存入日志。

         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}

 }

 upload_backups_toAliyunOSS()

 {

     # upload backfile to Aliyun OSS bucket

     for db in ${all_db}

     do

         backname=${db}.${time}

         echo "upload ${backname}.tar.gz to AliyunOSS begin at "$(date +'%Y-%m-%d %T') >>${logfile}

         ### Upload to AliyunOSS by storage Monthly ###

         ossutil64 cp ${backname}.tar.gz oss://${bucket_name}/DB_DATA/$(date +'%Y%m')/${local_ip}/ -f

         echo "upload ${backname}.tar.gz to AliyunOSS done at "$(date +'%Y-%m-%d %T') >>${logfile}

     done

 }

 #进入数据库备份文件目录

 cd ${backup_dir}

 mysql_backup

 delete_old_backup

 upload_backups_toAliyunOSS

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

 #cat ${logfile}