mysql5.7使用zabbix监控主从

ookkl

系统环境:

1 2	[iyunv@data]# cat /etc/redhat-release CentOS release 6.8 (Final)

mysql版本:

1 2 3 4 5

[iyunv@data]# /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	[iyunv@data]# 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应该报警.