#!/bin/bash

#author cuckooyang

export PATH=/bin:/sbin:/usr/bin:/usr/sbin:$PATH

database=/data/database
logdir=/data/logs
host=127.0.0.1
user=root
passwd='Ky@2020!'
backdir=/data/backup
bindata=/usr/bin
EXCLUDE="information_schema|performance_schema"

#全数据库备份
function backupall() {
   dt=`date +%Y%m%d%H%M%S`
   dd=`date +%Y%m%d`
   echo "################start is time : $dt #################################">>${logdir}/mysql_back_${dd}.log
   ${bindata}/xtrabackup --defaults-file=/usr/local/appserver/mysql8/my.cnf --backup --host=${host} --user=root --password=${passwd} --port=3306 --target-dir=${backdir}/mysql --no-server-version-check
   cd ${backdir}
   tar zcvf mysql_${dd}.tar.gz mysql
   rm -fr ./mysql/*
   sleep 3
   de=$(date +%Y%m%d%H%M%S)
   echo "####################end is time : $de ################################">>${logdir}/mysql_back_${dd}.log
   find ${backdir} -name \*.tar.gz -mtime +90|xargs rm -fr
}

function backupsingle() {
   dt=`date +%Y%m%d%H%M%S`
   dd=`date +%Y%m%d`
   echo "################start is time : $dt #################################">>${logdir}/mysql_back_${dd}.log
   listnum=$(/usr/bin/mysql --host=${host} --user='root' --password=${passwd} --port=3306 -e"show databases"| sed 's/\ //g' | grep -v "Database" | grep -vE ${EXCLUDE})
   echo -e "mariadb数据库详细列表：" $listnum >> ${logdir}/mysql_back_${dd}.log

   for db in ${listnum}
     do 
       backupdb ${db}
     done
   de=$(date +%Y%m%d%H%M%S)
   echo "####################end is time : $de ################################">>${logdir}/mysql_back_${dd}.log
   find ${backdir}/dbms -name \*.tar.gz -mtime +12|xargs rm -fr 
}

function backupdb() {
   dd=$(date +%Y%m%d)
   cd ${backdir}/dbms
   dbname=$1
   sleep 1
   ${bindata}/xtrabackup --defaults-file=/etc/my.cnf --backup --host=${host} --user=root --password=${passwd} --port=3306 --databases=${dbname} --target-dir=${backdir}/dbms/${dbname}

   sleep 1   
   tar czvf ${dbname}_${dd}.tar.gz ${dbname}
   sleep 1
   rm -fr ${dbname}
}

# 同步慢日志到下载下载目录
function anaylseSlowlog() {
    day1ago=$(date -d "yesterday" +"%Y%m%d")
    slow_log=slow.${day1ago}.log
    slow_tgz=/data/mallslow/slow.${day1ago}.tbz
    slow_copy=/data/mallslow/slow.${day1ago}.log

    cd /data/mysqllog

    if [ -f ${slow_log} ]; then
      cp -f ${slow_log} ${slow_copy}
      tar cjf ${slow_tgz} ${slow_log}
    fi

    find /data/mallslow -mtime +3 | xargs rm -f
    /usr/local/webserver/script/mysqlsla -lt slow -sort c_sum -top 30  /data/mallslow/slow.*.log > /data/mallslow/slow.stat.txt
}

# 每天一次切割慢日志
function cutSlowlog() {
    mysqllogdir=/data/database/logs
    mysqlbin=/usr/local/appserver/mysql8/bin
    today=$(date +%Y%m%d)
    slow_log=mysql_slow_query-${today}.log
    slow_tgz=${mysqllogdir}/mysql_slow_query-${today}.tbz

    cd ${mysqllogdir}
    ${mysqlbin}/mysql -h${host} -u${user} -p${passwd} -e "set global slow_query_log_file='/data/database/logs/mysql_slow_query-${today}.log';"   

#    if [ -f ${slow_log} ]; then
#      tar czvf ${slow_tgz} ${slow_log}
#    fi

    find ${mysqllogdir} -name \*.log -mtime +5 | xargs rm -f

}

case "$1" in
  all)
    backupall
  ;;
  single)
    backupsingle
  ;;
  newall)
   newbackupall
  ;;
  clean)
   cleanBackup
  ;;
  slowanaylse)
    anaylseSlowlog
  ;;
  cutlog)
    cutSlowlog
  ;;
  *)
   echo "使用说明: $0 {all|single|list}"
   echo "参数说明："
   echo "all 备份所有数据库"
   echo "single 备份指定的数据库"
   echo "list 列出备份文件"
   echo "clean 清除过期的备份文件"
   echo "rotate 切换查询日志与慢日志"
   echo "forcesync 强制同步主从数据库"
   echo "slowsync  同步数据库慢日志到下载目录"
   exit 3
 ;;
esac