使用relay模式重放binlog，实现自动化

背景：每天有外部来的binlog文件，需要在本地的MySQL环境做重放，在上次重放完成后Slave_SQL_Running_State显示Slave has read all relay log; waiting for more updates 。随后将新的binlog文件重命名为relay文件并按序号后延，也编辑了index文件添加新的relay文件编号，但不会继续重放，每次需要重新reset slave ->  change master to，故编写此脚本来自动重放。

#!bin/bash

cd /data/gscbs
echo "--------------------------------------------"
echo $(date "+%Y-%m-%d %H:%M:%S")""

# 已完成或正在进行的binlog文件编号
export pos=`cat /data/gscbs/binlog_filecode.txt`
export binlogfile=`echo $pos | awk '{printf("%06d\n",$0)}'`

# 获取Slave_SQL_Running_State的状态值
export slave_sql_running_state=($(mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "show slave status\G"|grep "Slave_SQL_Running_State" |awk '{print $2$3$4$5$6$7$8$9$10$11}'))
echo $slave_sql_running_state

# 获取Slave_SQL_Running的状态值
export slave_sql_running=($(mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "show slave status\G"|grep "Slave_SQL_Running" |awk '{print $2}'))
echo $slave_sql_running

# 获取Relay_Log_Pos的值
export relay_log_pos=($(mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "show slave status\G"|grep "Relay_Log_Pos" |awk '{print $2}'))
echo $relay_log_pos

# 获取要执行的binlog文件大小
export relay_log_size=`ls -l /data/gscbs/binlog/mysql-bin."$binlogfile" | awk '{ print $5 }'`
echo $relay_log_size


if [[ $slave_sql_running_state = "Slavehasreadallrelaylog;waitingformoreupdates" ]] && [[ $relay_log_pos -eq 4 ]];then
  echo "重放结束，等待新的binlog文件"
  relay_log_pos=$relay_log_size
elif  [[ $relay_log_pos -eq 123 ]];then
  echo "Slave未启动"
  relay_log_pos=$relay_log_size
fi  


# 从库状态正常，且上次已重放完毕
if [[ $slave_sql_running = "Yes" ]] && [[ $relay_log_pos -eq $relay_log_size ]];then
  echo "slave is OK"
  pos=$(($pos+1))
  binlogpos=`echo $pos | awk '{printf("%06d\n",$0)}'`
  okFile=/data/gscbs/binlog/mysql-bin."$binlogpos".ok
  echo $okFile
  
  if [ ! -f "$okFile" ]; then
    echo "mysql-bin.$binlogpos文件还没传输过来"
  else
    
    # 更新binlog_filecode.txt文件内容
    echo $pos > /data/gscbs/binlog_filecode.txt
    binlogfile=`echo $pos | awk '{printf("%06d\n",$0)}'`
    echo "本次重放的binlog文件是mysql-bin.$binlogfile"
    
    mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "stop slave; reset slave;"
    echo "reset slave成功"
	
    cd /data/gscbs/binlog
    cp mysql-bin."$binlogfile" /mysql-data
	echo "拷贝mysql-bin.$binlogfile成功"
    
    cd /mysql-data
    rm -rf relay-bin.*
    mv mysql-bin."$binlogfile" relay-bin."$binlogfile"
    chown -R mysql:mysql /mysql-data
    echo "./relay-bin."$binlogfile"" > relaylog_bin_index.index
    echo "编辑index文件成功"
	
    mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "change master to master_host='192.168.209.200', relay_log_file='relay-bin."$binlogfile"', relay_log_pos=1;"
    echo "change master to成功"
	mysql -h 192.168.100.100 -P 3306 -u root -p'123456' -e "start slave sql_thread;"
    echo "sql_thread启动成功"    
	
  fi

elif [[ $slave_sql_running = "Yes" ]] && [[ $relay_log_pos -ne $relay_log_size ]];then
  echo "正在重放binlog"

else
  echo "slave is wrong"
  echo "slave故障待处理" | mail -s "slave故障待处理" sinoimage@126.com
fi

加入crontab后，每10分钟执行一次。
PS：
1、为了确保外部binlog文件传输完成，每次传输完生成一个.ok文件，比如mysql-bin.005466传输完生成一个mysql-bin.005466.ok文件。
2、binlog_filecode.txt 存放的值是5466，以便+1运算，之后补零。