nagios系列(七)nagios通过自定义脚本的方式监控mysql主从同步

比眉伴天荒 2022-01-09 09:11 199阅读 0赞

nagios监控mysql主从同步  
  
![20160727153626596][]  
起因：nagios可能监控到mysql服务的运行情况，但确不能监控mysql的主从复制是否正常：有时候，同步已经停止，但管理人员却不知道。  
登陆mysql从服务器，通过执行 mysql> show slave status/G 查看其输出，即可判定主从复制是否正常。下面是某个从服务器的输出：  
  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.8.40  
Master\_User: repluser  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin.000008  
Read\_Master\_Log\_Pos: 34797869  
Relay\_Log\_File: relay-bin.000003  
Relay\_Log\_Pos: 34797915  
Relay\_Master\_Log\_File: mysql-bin.000008  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
Replicate\_Do\_DB:  
Replicate\_Ignore\_DB:  
Replicate\_Do\_Table:  
Replicate\_Ignore\_Table:  
Replicate\_Wild\_Do\_Table: TD\_OA.%  
Replicate\_Wild\_Ignore\_Table: mysql.%  
Last\_Errno: 0  
Last\_Error:  
Skip\_Counter: 0  
Exec\_Master\_Log\_Pos: 34797869  
Relay\_Log\_Space: 34798311  
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: 0  
Master\_SSL\_Verify\_Server\_Cert: No  
Last\_IO\_Errno: 0  
Last\_IO\_Error:  
Last\_SQL\_Errno: 0  
Last\_SQL\_Error:  
Replicate\_Ignore\_Server\_Ids:  
Master\_Server\_Id: 1  
1 row in set (0.00 sec)  
  
  
这个输出，最关键处就是"Slave\_IO\_Running: Yes “和“Slave\_SQL\_Running: Yes ”，这两个值全是"Yes"就表明主从复制正常，否则就是有问题。  
  
  
操作步骤：  
1、在从数据库服务器增加一个用户，给予较低的权限，操作为  
mysql> grant replication client on \*.\* to 'nagios'@'%' identified by 'nagios';  
mysql> flush privileges;  
  
  
2、登陆主服务器验证一下，看是否正常。操作为  
/usr/local/mysql/bin/mysql -h192.168.8.45 -unagios -pnagios -e "show slave status\\G"  
注意：这个操作是在 shell下操作的  
  
  
3、在从服务器安装 nrpe,然后在配置文件/usr/local/nagios/etc/nrpe.cfg加入一行  
command\[check\_mysql\_slave\]=/usr/local/nagios/libexec/check\_mysql\_slave  
  
  
4、在nagios client端即mysql从服务器编写脚本vim /usr/local/nagios/libexec/check\_mysql\_slave（这是监控其作用的核心）,其内容如下：  
\#!/bin/sh  
declare -a slave\_is  
slave\_is=($(/usr/local/mysql/bin/mysql -unagios -pnagios -e "show slave status\\G"|grep Running |awk '\{print $2\}'))  
if \[ "$\{slave\_is\[0\]\}" = "Yes" -a "$\{slave\_is\[1\]\}" = "Yes" \]  
then  
echo "OK -slave is running"  
exit 0  
else  
echo "Critical -slave is error"  
exit 2  
fi  
chmod +x /usr/local/nagios/libexec/check\_mysql\_slave  
  
  
5、手动运行这个脚本，观察输出。  
\# /usr/local/mysql/bin/mysql -unagios -pnagios -e "show slave status\\G"|grep Running |awk '\{print $2\}'  
报错：  
ERROR 1045 (28000): Access denied for user 'nagios'@'localhost' (using password: YES)  
发现删除系统mysql库中user表自带的一些'::1'等匿名用户，并且对root配置密码后，问题解决  
  
6、nagios服务端执行  
\# /usr/local/nagios/libexec/check\_nrpe -H 192.168.8.45 -c check\_mysql\_slave  
OK -slave is running  
  
  
7、修改nagios服务端service.cfg文件。  
/usr/local/nagios/etc/objects/service.cfg  
添加  
define service\{  
use generic-service  
host\_name node45.chinasoft.com  
service\_description check\_mysql\_slave  
check\_command check\_nrpe!check\_mysql\_slave  
\}

8、功能测试

![20160727153513330][]

转载于:https://www.cnblogs.com/reblue520/p/6239759.html

[20160727153626596]: /images/20220109/76620a0b9da141f4a33d6d1282b88d21.png
[20160727153513330]: /images/20220109/a6ad6baf0ac14f2f9df4772c14cb9c3e.png