zabbix3.0.4监控mysql主从同步

忘是亡心i 2022-05-20 10:21 160阅读 0赞

1.监控mysql主从同步原理：

执行一个命令  
mysql -u zabbix -pzabbix -e 'show slave status\\G'  
  
  
我们在输出的信息中选择  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
这两项来监控  
  
当操作的数据出现异常的时候，Slave\_SQL\_Running就会变成No  
当执行slave  stop的时候，两个都会变成No

2.添加一个具有权限的用户zabbix，只需要赋予replication\_client服务器权限即可

![20161117101623969][]

3.编辑zabbix-agent配置文件  
vim /etc/zabbix/zabbix\_agentd.conf  
  
UserParameter=db\_status,mysql -uzabbix -pzabbixpass -e "show slave status\\G" 2>/dev/null|egrep 'Slave\_IO\_Running|Slave\_SQL\_Running'|grep -v 'Slave\_SQL\_Running\_State'|awk '\{print $NF\}'|grep -c Yes  
  
重启使之生效  
  
先用zabbix这个用户来获取slave的所有状态，然后grep出这两个状态，然后再输出第二列，最后查看有几个Yes状态的  
  
服务端测试：

zabbix\_get -s x.x.x.x -p 10050 -k "db\_status"

zabbix服务端web配置

添加监控项item  
Confuguration --> Hosts --> 找到对应的主机，点开 Items --> Create item  
  
![20161114181856555][]  
创建对该监控项的触发器  
Confuguration --> Hosts --> 找到对应的主机，点开 Triggers --> Create trigger

![20161114181907844][]

`运行一段时间后报错:`

`MariaDB [zabbix]> show slave status \G`

`*************************** 1. row ***************************`

`Slave_IO_State: Waiting for master to send event`

`Master_Host: 10.19.50.236`

`Master_User: repluser`

`Master_Port: 3306`

`Connect_Retry: 60`

`Master_Log_File: mysql-bin.000004`

`Read_Master_Log_Pos: 52002057`

`Relay_Log_File: relay-bin.000008`

`Relay_Log_Pos: 46774145`

`Relay_Master_Log_File: mysql-bin.000004`

`Slave_IO_Running: Yes`

`Slave_SQL_Running: No`

`Replicate_Do_DB:`

`Replicate_Ignore_DB:`

`Replicate_Do_Table:`

`Replicate_Ignore_Table:`

`Replicate_Wild_Do_Table: zabbix.%`

`Replicate_Wild_Ignore_Table: mysql.%`

`Last_Errno: 1062`

`Last_Error: Error 'Duplicate entry '149' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into escalations (escalationid,actionid,status,triggerid,itemid,eventid,r_eventid) values (149,7,0,16272,null,3334811,null)'`

`Skip_Counter: 0`

`Exec_Master_Log_Pos: 46773861`

`Relay_Log_Space: 52003816`

`Until_Condition: None`

`Until_Log_File:`

`Until_Log_Pos: 0`

`Master_SSL_Allowed: No`

`Master_SSL_CA_File:`

`Master_SSL_CA_Path:`

`Master_SSL_Cert:`

`Master_SSL_Cipher:`

`Master_SSL_Key:`

`Seconds_Behind_Master: NULL`

`Master_SSL_Verify_Server_Cert: No`

`Last_IO_Errno: 0`

`Last_IO_Error:`

`Last_SQL_Errno: 1062`

`Last_SQL_Error: Error 'Duplicate entry '149' for key 'PRIMARY'' on query. Default database: 'zabbix'. Query: 'insert into escalations (escalationid,actionid,status,triggerid,itemid,eventid,r_eventid) values (149,7,0,16272,null,3334811,null)'`

`Replicate_Ignore_Server_Ids:`

`Master_Server_Id: 1`

`1 row in set (0.00 sec)`

`解决的办法是在从库上执行:`

`mysql> slave stop;`

`mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;`

`mysql> slave start;`

`上面的方法可以解决问题，还有一种解决问题的办法是通过修改mysql的配置文件，让从库的同步线程忽略这个错误，方法：`

`修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行 slave_skip_errors = 1062 ,保存重启mysql`

`mysql slave可以正常同步了.`

[20161117101623969]: /images/20220520/a36f3a218a63439bbcbca5fb2cb9e819.png
[20161114181856555]: /images/20220520/6633ee749d744e54bed8ea9adc139d55.png
[20161114181907844]: /images/20220520/ef2bbfb394b14585a311a6fed9f96632.png