由于mysql本身有一个针对主从同步的跳过错误命令,该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况
#表示跳过一步错误,后面的数字可变 set global sql_slave_skip_counter =1;
脚本内容
#!/bin/bash . /etc/profile . ~/.bash_profile cnt=$(ps -ef |grep -v grep|grep 'sh /root/hotback.sh' -c) if [ $cnt -gt 2 ];then exit fi rm -f /root/slave.log MIP='192.168.0.41' MU='root' MP='123456' SU='root' SP='123456' mysql -h $MIP -u$MU -p$MP -e " show master status\G quit" > /root/slave.log mysql -u$SU -p$SP -e " show slave status\G quit" >> /root/slave.log #show master status\G #M日志文件 File=$(grep -w "File" /root/slave.log|sed "s#File..##g"|sed "s#[ \t]##g") #M版本号 Position=$(grep -w "Position" /root/slave.log|sed "s#Position..##g"|sed "s#[ \t]##g") #show slave status\G #S获取的M日志文件 Master_Log_File=$(grep -w "Master_Log_File" /root/slave.log|sed "s#Master_Log_File..##g"|sed "s#[ \t]##g") #S获取的M版本号 Read_Master_Log_Pos=$(grep -w "Read_Master_Log_Pos" /root/slave.log|sed "s#Read_Master_Log_Pos..##g"|sed "s#[ \t]##g") #S连接M的状态 Slave_IO_Running=$(grep -w "Slave_IO_Running" /root/slave.log|sed "s#Slave_IO_Running..##g"|sed "s#[ \t]##g") #S执行sql命令状态 Slave_SQL_Running=$(grep -w "Slave_SQL_Running" /root/slave.log|sed "s#Slave_SQL_Running..##g"|sed "s#[ \t]##g") if [ "$File" != "$Master_Log_File" ]||[ "$Position" != "$Read_Master_Log_Pos" ] then if [ "$Slave_IO_Running" = "No" ] then echo "--------------------" >> /root/error.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/error.log echo "主从服务器连接失败,重启slave服务。" >> /root/error.log mysql -u$SU -p$SP -e " STOP SLAVE; START SLAVE; quit" exit fi fi if [ "$Slave_SQL_Running" = "No" ] then echo "--------------------" >> /root/serror.log echo $(date +%Y-%m-%d\ %H:%M:%S) >> /root/serror.log echo "从服务器执行sql失败,跳过错误" >> /root/serror.log mysql -u$SU -p$SP -e " STOP SLAVE; set global sql_slave_skip_counter =1; START SLAVE; quit" fi
本文链接:https://jeff.xin/post/41.html
--EOF--
Comments
发表评论:
◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。