系统环境:
[root@data]# cat /etc/redhat-release CentOS release 6.8 (Final)
mysql版本:
[root@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
创建一个用户用于查询主从情况:
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查询主从情况:
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的值:
[root@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应该报警.
zabbix客户端的配置:
[root@data1]# 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文件
[root@data1]# cat /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_replication.conf UserParameter=mysql.replication,/usr/local/zabbix/scripts/mysql_replication.sh
zabbix脚本
[root@data1]# 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 [root@data1]# 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
在zabbix服务器控制端创建一个Template模板:
选择"Configuration"-"Template",然后点击"Create template",
名称为:Template App MySQL Replication
创建Items,
Name:MySQL slave status
Type:Zabbix agent (active)
Key:mysql.replication
创建Triggers
Name:MySQL Replication error on {HOST.NAME}
Expression:{Template App MySQL Replication:mysql.replication.last()}<2
模板创建好之后,将模板Link到要监控的主机.最终的监控状态如下
其中有一段时间内Mysql 主从失败,zabbix会发出报警.
转载于:https://blog.51cto.com/huwei555/1898901