系统环境:
1
2
|
[root@data]
# cat /etc/redhat-release
CentOS release 6.8 (Final)
|
mysql版本:
1
2
3
4
5
|
[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
|
创建一个用户用于查询主从情况:
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
|
[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客户端的配置:
1
2
3
4
5
6
7
8
9
10
11
|
[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文件
1
2
|
[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脚本
1
2
3
4
5
|
[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会发出报警.
本文转自 yawei555 51CTO博客,原文链接:http://blog.51cto.com/huwei555/1898901,如需转载请自行联系原作者