江苏龙网

  1. 主页 > 生活百科 > >

监控mysql主从同步状态是否异常

mysql

标签：监控MySQL主从同步状态是否异常
阶段1：开发一个守护进程脚本每30秒实现检测一次 。
阶段2：如果同步出现如下错误号(1158,1159,1008,1007,1062)，请跳过错误
阶段3：请使用数组技术实现上述脚本（获取主从判断及错误号部分）
[root@slave ~]# mysql -u root -proot -e "show slave statusG;"*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.1.2#当前的mysql master服务器主机Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000003Read_Master_Log_Pos: 471Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 252Relay_Master_Log_File: master-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesMaster_SSL_Key:Seconds_Behind_Master: 0#和主库比同步延迟的秒数 准备：egrep "_Running|Behind_Master" slave.log#过滤Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0[root@slave ~]# egrep "_Running|Behind_Master" slave.log | awk ‘{print $NF}‘YesYes0阶段一：开发一个守护进程脚本每30秒实现检测一次 。
#!/bin/bashwhile truedoarray=($(egrep "_Running|Behind_Master" slave.log|awk ‘{print $NF}‘))if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "0" ]thenecho "MySQL is slave is ok"elsechar="MySQL slave is not ok"echo "$char"echo "$char"|mail -s "$char" 995345781@qq.combreakfisleep 30done执行结果：[root@slave ~]# sh test.shMySQL is slave is okMySQL is slave is ok终极版：
#!/bin/bash#Date:2017-7-3#Author:xcn(baishuchao@yeah.net)#version 1.0mysql_cmd="mysql -u root -proot"errorno=(1158 1159 1008 1007 1062)while truedoarray=($($mysql_cmd -e "show slave statusG"|egrep ‘_Running|Behind_Master|Last_SQL_Errno‘|awk ‘{print $NF}‘))if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "0" ]thenecho "MySQL is slave is ok"elsefor ((i=0;i<${#errorno[*]};i++))doif [ "${array[3]}" = "${errorno[$i]}" ];then$mysql_cmd -e "stop slave &&set global sql_slave_skip_counter=1;start slave;"fidonechar="MySQL slave is not ok"echo "$char"echo "$char"|mail -s "$char" 995345781@qq.combreakfisleep 30done提示：这个脚本可以用于生产环境中，监控mysql主从同步状态是否异常，根据
‘_Running|Behind_Master|Last_SQL_Errno‘这个进行判断，如果不正常的话则会进一步判断状态码，然后进行输出，则会发邮件或短信给运维人员

【监控mysql主从同步状态是否异常】


    推荐阅读


    上一篇：南宋韩侂胄后人,韩侂胄是什么样的人

    下一篇：李隆基之子谁当皇帝,李隆基的哪个儿子继位了