#!/bin/bash

# 驼峰写法为函数/小写为变量
# # 安装 xtrabackup 和 qpress
# yum install -y  https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum -y install qpress pv percona-xtrabackup-24.x86_64

########## 总参数 ##########
backup_mode=1              # 全备: 0 / 增倍: 1 
backup_type=0              # 本地备份: 0 / 远程备份: 1 
backup_save_days=30        # 备份保存天数
log_save_days=30           # 备份日志保存天数
backup_tag=10.10.8.66      # 备份路径中添加标识信息
xtarbackup_limit_flow=125  # 远程传输流量限制速度为每秒多少mb: MB/s # 通常内网 1 Gbps 的网络速度等于 125 MB/s 这里就是不限速

########## mysql 相关参数 ##########
mysql_host=10.10.8.66        # mysql 连接地址
mysql_port=3306              # mysql 连接端口
mysql_user=dba_qianyi_m      # mysql 连接用户
mysql_pwd='123456'           # mysql 连接密码
mysql_conf_path='/etc/my3306.cnf'       # mysql  配置文件路径

########## xtrabackup 相关参数 ##########
xtrabackup_parallel=10                           # 并行拷贝 ibd 文件的线程数默认单线程 # 对备份时间影响较大,磁盘性能低的10以下的并发
xtrabackup_compress_threads=6                    # 压缩备份线程数 # 根据 CPU 核心数和使用量判断线程数
kill_long_queries_timeout=120                    # 长查询 kill 等待时间/秒
kill_long_query_type=all                         # kill 的类型 用以取消全局锁 默认: all 可选: select
backup_path='/data/backup'                       # 备份保存路径
backup_log_path="${backup_path}/pxb-log"         # 备份日志保存路径 保持默认即可
xtrabackup_tmpdir="/tmp"                         # 临时目录
xtrabackup_extra_lsndir="/mydata/3306/tmp/" # 额外保存备份位点信息 必须配置十分重要

########## ssh 远程备份相关 非远程不用配置 ##########
scp_user=zcsadmin
scp_host=10.10.8.66
scp_port=22
scp_path='/data/backup/10.10.8.65-3306'

########## 微信告警配置 无需微信告警设置为 OFF 即可 ##########
enterprise_wechat_alarm=ON   # 开启: ON / 关闭: OFF    
enterprise_wechat_webhook_url="https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=f82"   # 企业微信群机器人 webhook
enterprise_wechat_tag="${backup_tag}"   # 在微信告警中添加标识信息


########## 脚本相关 无需配置 ##########
pxb_datatime="$(date '+%Y%m%d%H%M%S')"
log_redirection="${backup_log_path}/2_pxb_${pxb_datatime}.log"  # 脚本日志保存位置
pxb_log_redirection="${backup_log_path}/1_pxb_${pxb_datatime}.log" # xtrabackup 备份日志保存位置

########## 非特殊 无需配置 ##########
mysql_command_path="$(which  mysql 2>/dev/null)"    # mysql  命令路径
#mysqld_command_path="$(which  mysqld 2>/dev/null)"  # mysqld 命令路径
xtrabackup_command_path="$(which  xtrabackup 2>/dev/null)"   # xtrabackup 命令路径

# mysql 连接
mysql_conn="$mysql_command_path -h$mysql_host -P$mysql_port -u$mysql_user -p"$mysql_pwd""
# 写日志函数
LogWrite() {
    message_content=$1
    message_status=$2
    echo "$(date '+%Y-%m-%d %H:%M:%S') ${mysql_host}:${mysql_port} ${message_status}  ${message_content}"  >>${log_redirection}
    echo "$(date '+%Y-%m-%d %H:%M:%S') ${mysql_host}:${mysql_port} ${message_status}  ${message_content}"
}

# 微信告警
WeixinAlarm() {
    if [ "$enterprise_wechat_alarm" == "ON" ];then
        alarm_check(){
            # 网络是否通畅
            ping -c 1 qyapi.weixin.qq.com  >/dev/null  2>&1
            if [ $? -ne 0 ];then
                LogWrite "网络不通 无法使用微信告警功能" "error"
            fi
        }
        alarm_check

        alarm_content="$1"
        curl "${enterprise_wechat_webhook_url}"  -H 'Content-Type: application/json'  -d "{\"msgtype\": \"text\",\"text\": {\"content\": \"${enterprise_wechat_tag}:${mysql_port} ${alarm_content}\"}}" 2>/dev/null |grep 'ok' >/dev/null 2>&1
        if [ $? -ne 0 ];then
            LogWrite "微信消息发送失败 请手动验证 webhook 是否正确" "error"
            exit 1
        fi
    fi
}

# 备份检查
BackupCheck() {
    check_status=0
    LogWrite "开始备份检查···" "info"
    # 测试 master 连通性
    ${mysql_conn}  -e 'select 1' >/dev/null 2>&1
    if [ $? -ne 0 ];then 
      LogWrite "连接失败 请检查网络及用户密码" "error"
      WeixinAlarm "备连接失败 请检查网络及用户密码"
      exit 1
    fi

    # 检查目录是否存在
    if [ ! -f "$mysql_conf_path" ];then
      LogWrite "配置文件 $xtrabackup_command_path 不存在" "error"
      check_status=1
    fi

    if [ ! -f "$mysql_command_path" ];then
      LogWrite "$mysql_command_path mysql 命令不存在" "error"
      check_status=1
    fi

    #if [ ! -f "$mysqld_command_path" ];then
    #  LogWrite "$mysqld_command_path mysqld 命令不存在" "error"
    #  check_status=1
    #fi

    if [ ! -f "$xtrabackup_command_path" ];then
      LogWrite "$xtrabackup_command_path xtrabackup 命令不存在" "error"
      check_status=1
    fi

    which qpress  >/dev/null 2>&1
    if [ $? -ne 0 ];then
      LogWrite "qpress 命令不存在" "error"
      check_status=1
    fi

    which pv  >/dev/null 2>&1
    if [ $? -ne 0 ];then
      LogWrite "pv 命令不存在" "error"
      check_status=1
    fi

    if [ ! -d "$backup_path" ];then
      LogWrite "$backup_path 备份目录不存在" "error"
      check_status=1
    fi

    if [ ! -d "$xtrabackup_extra_lsndir" ];then
      LogWrite "$xtrabackup_extra_lsndir lsndir目录不存在" "error"
      check_status=1
    fi

    if [ ! -d "$xtrabackup_tmpdir" ];then
      LogWrite "$xtrabackup_tmpdir xtrabackup_tmpdir目录不存在" "error"
      check_status=1
    fi

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

    # 判断是否进行远程scp备份检查
    if [ ${backup_type} -eq 1 ];then
        ssh ${scp_user}@${scp_host} 'date' >/dev/null  2>&1
        if [ $? -ne 0 ];then
            LogWrite "ssh 连接失败 无法远程传输 请配置并检查 ssh 免密登录" "error"
            check_status=1
        fi

        ssh  ${scp_user}@${scp_host} "ls ${scp_path}"  >/dev/null  2>&1
        if [ $? -ne 0 ];then
            LogWrite "scp 远程目录 ${scp_path} 不存在 请创建目录" "error"
            check_status=1
        fi
    fi

    # 判断是否通过
    if [ $check_status -ne 0 ];then
      WeixinAlarm "备份检查失败"
      exit 1
    fi

    LogWrite "备份检查已完成" "info"
    LogWrite "请检查磁盘空间是否充足 避免磁盘写满" "info"

    # 磁盘检查使用情况并微信报警
    DiskCheck(){
        # 磁盘使用率 >=90 则为 0 否则为 1
        disk_check_status=$(df -h | grep -v '/boot' | grep '^/dev/*' | awk '{gsub(/%/, ""); if ($5 >= 90) {print 0; found=1; exit}} END {if (!found) print 1}')
        if [ $disk_check_status -eq 0 ];then
        LogWrite "存在使用率大于 90% 的磁盘" "error"
        WeixinAlarm "存在使用率大于 90% 的磁盘"
        fi
    }
    DiskCheck
    LogWrite "开始备份···" "info"

}

# 删除日志
DeleteLogFile() {
    cd ${backup_log_path} && find ./ -type f -name  "[12]-pxb-*.log" -mtime +${log_save_days} -print|xargs rm -f
    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
        LogWrite "日志清理失败" "error"
    fi  
}


# 删除备份
DeleteBackup() {
    # 判断远程还是本地
    if [ $backup_type -eq 0 ];then
        cd  ${backup_path} && find ./  -type f -name "pxb-*.xb"  -mtime +${backup_save_days} -print|xargs rm -f
        if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
            LogWrite "本地备份清理失败" "error"
        fi
    elif [ $backup_type -eq 1 ];then
        ssh  -p ${scp_port}  ${scp_user}@${scp_host} "cd  ${scp_path}  &&  find ./  -type f -name "pxb-*.xb"  -mtime +${backup_save_days} -print|xargs rm -f"
        if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]];then
            LogWrite "远程备份清理失败" "error"
        fi
    else
        LogWrite "备份类型填写错误 无法定期删除备份" "error"
    fi
}


slave_parallel_workers=0
slave_status=0
# 修改从库线程数
SlaveMultiThreadChange(){
    slave_parallel_workers=$(${mysql_conn} -N -e "select @@global.slave_parallel_workers;" 2>/dev/null)
    slave_status=$(${mysql_conn} -e 'show slave status\G' 2>/dev/null |egrep  -w 'Slave_IO_Running|Slave_SQL_Running' | grep -c  'Yes')

    if [ "${slave_status}" == '2' ];then
        if [[ ${slave_parallel_workers} -ne 0 || ${slave_parallel_workers} -ne 1 || ${slave_parallel_workers} != '' ]];then
            ${mysql_conn} -e "set global slave_parallel_workers=1;stop slave;start slave;" 2>/dev/null
            LogWrite "slave_parallel_workers=${slave_parallel_workers} 备份完成后复原" "info"
        fi
    fi
}

# 恢复从库线程数
SlaveMultiThreadRecovery(){
    if [ "${slave_status}" == '2' ];then
        if [[ ${slave_parallel_workers} -ne 0 || ${slave_parallel_workers} -ne 1 || ${slave_parallel_workers} != '' ]];then
            ${mysql_conn} -e "set global slave_parallel_workers=${slave_parallel_workers};stop slave;start slave;" 2>/dev/null
            LogWrite "slave_parallel_workers=${slave_parallel_workers} 已复原" "info"
        fi
    fi
}

# 参数拼接
ParametersSplicing() {

    gtid_status="$($mysql_conn -N -e "show variables like 'gtid_mode'" 2>/dev/null |awk '{print $2}')"
    if [ "$gtid_status" == 'ON' ];then
        slave_info='--slave-info'
    else
        slave_info=''
    fi

    # trabackup 参数拼接
    xtrabackup_splicing="$xtrabackup_command_path \
    --defaults-file=$mysql_conf_path \
    --backup \
    --user=$mysql_user \
    --password="$mysql_pwd" \
    --host=$mysql_host \
    --port=$mysql_port  \
    --compress \
    --compress-threads=$xtrabackup_compress_threads \
    --ftwrl-wait-query-type=$kill_long_query_type \
    --ftwrl-wait-timeout=$kill_long_queries_timeout \
    --kill-long-queries-timeout=$kill_long_queries_timeout \
    --kill-long-query-type=${kill_long_query_type} \
    --no-timestamp \
    --stream=xbstream \
    --tmpdir=$xtrabackup_tmpdir \
    --parallel=$xtrabackup_parallel \
    $slave_info "


    # 限制流量参数拼接
    limit_flow_splicing="pv -q -L ${xtarbackup_limit_flow}m"

    # scp 远程参数拼接
    RemoteSplicing(){
    
        file_name=$1
        # scp 远程参数拼接
        remote_splicing="ssh  -p ${scp_port}  ${scp_user}@${scp_host}  cat > ${scp_path}/${file_name}"
        echo $remote_splicing
    }
}

# 本地全备
LocalFullBackup() {
    $xtrabackup_splicing --extra-lsndir=$xtrabackup_extra_lsndir   --target-dir=./  >${backup_path}/pxb-${backup_tag}-${mysql_port}-${pxb_datatime}-full.xb  2>>$pxb_log_redirection
    if [ $? -ne 0 ];then
      LogWrite "本地全备失败" "error"
      # 微信告警
      WeixinAlarm "本地全备失败"
      exit 1
    fi

    pxb_status=$(tail -n 1 $pxb_log_redirection 2>/dev/null|grep -c  'completed OK!' 2>/dev/null)

    if [ $pxb_status -ne 1 ];then
      LogWrite "本地全备失败" "error"
      # 微信告警
      WeixinAlarm "本地全备失败"
      exit 1
    fi
    #cd ${backup_path} && rm -rf pxb_full_${pxb_datatime}
    LogWrite "本地全备已完成" "info"


}

# 远程全备
RemoteFullBackup() {

    scp_remote="$(RemoteSplicing "pxb-${backup_tag}-${mysql_port}-${pxb_datatime}-full.xb")"
    ${xtrabackup_splicing}  --extra-lsndir=$xtrabackup_extra_lsndir    --target-dir=./  2>>${pxb_log_redirection}  | ${limit_flow_splicing}  2>>${log_redirection}  |  ${scp_remote} 2>>${log_redirection}

    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 || ${PIPESTATUS[2]} -ne 0 ]];then
      LogWrite "远程全备失败" "error"
      # 微信告警
      WeixinAlarm "远程全备失败"
      exit 1
    fi

    pxb_status=$(tail -n 1 $pxb_log_redirection 2>/dev/null|grep -c  'completed OK!' 2>/dev/null)

    if [ $pxb_status -ne 1 ];then

      LogWrite "远程全备失败" "error"

      # 微信告警
      WeixinAlarm "远程全备失败"
      exit 1
    fi
    #cd ${backup_path} && rm -rf pxb_full_${pxb_datatime}
    LogWrite "远程全备已完成" "info"

}

# 本地增备
LocalIncrementBackup(){
    $xtrabackup_splicing  --target-dir=./  --incremental-basedir=${xtrabackup_extra_lsndir}  >${backup_path}/pxb-${backup_tag}-${mysql_port}-${pxb_datatime}-inc.xb  2>>$pxb_log_redirection
    if [ $? -ne 0 ];then
      LogWrite "本地增备失败" "error"
      # 微信告警
      WeixinAlarm "本地增备失败"
      exit 1
    fi

    pxb_status=$(tail -n 1 $pxb_log_redirection 2>/dev/null|grep -c  'completed OK!' 2>/dev/null)

    if [ $pxb_status -ne 1 ];then
      LogWrite "本地增备失败" "error"
      # 微信告警
      WeixinAlarm "本地增备失败"
      exit 1
    fi
    #cd ${backup_path} && rm -rf pxb_full_${pxb_datatime}
    LogWrite "本地增备已完成" "info"

}

# 远程增倍
RemoteIncrementBackup(){
    scp_remote="$(RemoteSplicing "pxb-${backup_tag}-${mysql_port}-${pxb_datatime}_inc.xb")"
    $xtrabackup_splicing  --target-dir=./  --incremental-basedir=${xtrabackup_extra_lsndir} 2>>${pxb_log_redirection} | ${limit_flow_splicing}  2>>${log_redirection}  |  ${scp_remote} 2>>${log_redirection} 

    if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 || ${PIPESTATUS[2]} -ne 0 ]];then
      LogWrite "远程增备失败" "error"
      # 微信告警
      WeixinAlarm "远程增备失败"
      exit 1
    fi

    pxb_status=$(tail -n 1 $pxb_log_redirection 2>/dev/null|grep -c  'completed OK!' 2>/dev/null)

    if [ $pxb_status -ne 1 ];then

      LogWrite "远程增备失败" "error"

      # 微信告警
      WeixinAlarm "远程增备失败"
      exit 1
    fi
    #cd ${backup_path} && rm -rf pxb_full_${pxb_datatime}
    LogWrite "远程增备已完成" "info"

}

# 增备的判断逻辑
IncrementBackupLogic(){

    # 判断 lsn 文件是否存在  存在继续判断  不存在 设置为全备
    if [[ $(ls  $xtrabackup_extra_lsndir |grep -c 'xtrabackup_checkpoints') -eq 1 &&  $(ls  $xtrabackup_extra_lsndir |grep -c 'xtrabackup_info') -eq 1 ]];then
        # 获取 lsn 的日期
        lsn_date="$(cat ${xtrabackup_extra_lsndir}/xtrabackup_info  2>/dev/null |grep 'end_time' 2>/dev/null |awk  '{print $3}')"
        
        # 判断是否为空 不为空 判断日期是否为本周 为空 设置为全备
        if [ $lsn_date != '' ];then
            # 对比 lsn 的日期是否为本周的日期
            if [ $(date -d "$lsn_date" +%Y%V) -eq "$(date  +%Y%V)" ];then
                # 本周的日期则为增量备份
                backup_mode=1
            else
                # 不是本周的日期则全备
                backup_mode=0
            fi
        else 
            backup_mode=0
        fi
    else
        backup_mode=0
    fi
}

# 本地备份逻辑
LocalBackupLogic(){
    # 等于 0 则全备
    if [ $backup_mode -eq 0 ];then
        LocalFullBackup
    
    # 等于 1 则增备
    elif [ $backup_mode -eq 1 ];then

        # 本地增倍
        LocalIncrementBackup
    else
        LogWrite "backup_mode 参数只能为 0/1" "error"

    fi
}

# 远程备份逻辑
RemoteBackupLogic(){
    # 等于 0 则全备
    if [ $backup_mode -eq 0 ];then
        RemoteFullBackup
    
    # 等于 1 则增备
    elif [ $backup_mode -eq 1 ];then
        # 远程增倍
        RemoteIncrementBackup
    fi
}

# 总备份逻辑
TotalBackupLogic(){

    # 调用备份检查
    BackupCheck

    # 参数拼接函数引入
    ParametersSplicing

    # 判断 是否为增量备份
    if [ $backup_mode -eq 1 ];then
        # 调用增量备份的逻辑处理
        IncrementBackupLogic
    fi

    # 修改从库线程数为1
    SlaveMultiThreadChange
    # 等于0 就是本地备份
    if [ $backup_type -eq 0 ];then
        LocalBackupLogic

    # 等于 1 就是远程备份
    elif [ $backup_type -eq 1 ];then
        RemoteBackupLogic
    
    else
        LogWrite "backup_type 参数只能为 0/1" "error"
    fi 

    # 恢复从库线程数
    SlaveMultiThreadRecovery
    # 清理过期日志文件
    DeleteLogFile
    # 清理过期备份
    DeleteBackup
}
TotalBackupLogic