使用zabbix对mysql5.7主从进行监控

系统环境:

1 2	`[[email protected]]# cat /etc/redhat-release` `CentOS release 6.8 (Final)`

mysql版本:

[[email protected]]# /usr/local/mysql/bin/mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 69372
Server version: 5.7.17-log Source distribution

创建一个用户用于查询主从情况:

1
2
3
4
5
6
7
8
9
10
11


mysql> create user 'zabbix'@'localhost';
Query OK, 0 rows affected (0.14 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
 

mysql> grant replication client on *.* to 'zabbix'@'localhost';
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

mysql查询主从情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> show slave status \G
*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.245

                  Master_User: apprankback

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000169

          Read_Master_Log_Pos: 471806851

               Relay_Log_File: zabbix_data-relay-bin.000511

                Relay_Log_Pos: 24101450

        Relay_Master_Log_File: mysql-bin.000169

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

主从正常的情况是

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

现在使用命令语句获取主从状态YES的值:

1 2	`[[email protected]]# echo "show slave status \G"\|/usr/local/mysql/bin/mysql -uzabbix 2>/dev/null\|grep -E "Slave_IO_Running\|Slave_SQL_Running"\|awk '{print $2}'\|grep -c Yes` `2`

值为2说明状态正常,如果为0或者1,主从复制不正常,这个时候zabbix应该报警.

zabbix客户端的配置:

[[email protected]]# cat zabbix_agentd.conf

LogFile=/tmp/zabbix_agentd.log
Server=X.X.X.224
ServerActive=X.X.X.224
Hostname=jiankong_data
RefreshActiveChecks=60
BufferSize=10000
MaxLinesPerSecond=200
Timeout=30

HostMetadataItem=system.uname

Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/*.conf

Include包含文件下的conf文件

1 2	`[[email protected]]# cat /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_replication.conf` `UserParameter=mysql.replication,/usr/local/zabbix/scripts/mysql_replication.sh`

zabbix脚本

[[email protected]]# ll -a /usr/local/zabbix/scripts/mysql_replication.sh

-rwx------ 1 zabbix zabbix 162 2月  17 17:45 /usr/local/zabbix/scripts/mysql_replication.sh

[[email protected]]# cat /usr/local/zabbix/scripts/mysql_replication.sh
#!/bin/bash

echo "show slave status \G"|/usr/local/mysql/bin/mysql -uzabbix 2>/dev/null|grep -E "Slave_IO_Running|Slave_SQL_Running"|awk '{print $2}'|grep -c Yes