监控前的环境:
- 我们这边已经搭建好了zabbix并且将mysql进行了主从复制;
- 看一下客户端的主从数据库状态是否都为yes;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.26.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 12166212
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 1693369
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12166212
Relay_Log_Space: 11611797
- 创建一个监控mysql主从的脚本
[root@localhost scripts]# vim wzy.sh
#!/bin/bash
a=$(mysql -uroot -pyong123! -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk -F: '{print $2}')
b=$(mysql -uroot -pyong123! -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk -F: '{print $2}')
c=$(echo $a $b)
echo $c|grep 'Yes'|wc -w
- 将脚本所在的路径写到配置文件里面,并赋予它间值
[root@localhost scripts]# vim /usr/local/etc/zabbix_agentd.conf
...
# TLSPSKFile=
UserParameter=check_mysql,/scripts/wzy.sh
- 主数据库所在的服务端上读取一下check_mysql的值,说明网页zabbix能获取到/scripts/yong.sh脚本的键值
[root@wuliyong ~]# zabbix_get -s 192.168.26.18 -k check_mysql
0
通过监控刚刚所创建的mysql主从脚本所得到的值来监控mysql主从的状态:
- 创建监控mysql主从状态的监控项
- 创建触发器
- 这边所赋予的条件是2,相对应的就是脚本里面的值,如果脚本里面的值小于2,就会报出故障
我们这边测验一下故障的报错:
- 首先关掉客户端从数据库的slave
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.26.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 12687018
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 2214175
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
- 在网页zabbix上进行查看
通过监控刚刚所创建的mysql主从脚本所得到的值来监控mysql延迟
- 创建一个监控mysql延迟的脚本
[root@localhost scripts]# vim yong.sh
#!/bin/bash
d=$(mysql -uroot -pyong123! -e 'show slave status\G' 2>/dev/null |grep ' Read_Master_Log_Pos:'|awk -F: '{print $2}')
e=$(mysql -uroot -pyong123! -e 'show slave status\G' 2>/dev/null |grep ' Exec_Master_Log_Pos:'|awk -F: '{print $2}')
if [ $d -eq $e ];then
echo 0
else
echo "$d-$e"
fi
- 将脚本所在的路径写到配置文件里面,并赋予它间值
[root@localhost scripts]# vim /usr/local/etc/zabbix_agentd.conf
...
# TLSPSKFile=
UserParameter=check_koko,/scripts/yong.sh
- 主数据库所在的服务端上读取一下check_mysql的值,说明网页zabbix能获取到/scripts/yong.sh脚本的键值
[root@wuliyong ~]# zabbix_get -s 192.168.26.18 -k check_koko
0
- 创建监控mysql主从状态的监控项
- 创建触发器
- 这边设定一个赋予的条件大于1就开始弹出延迟
我们这边模拟一下延迟报警
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.26.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 12687018
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 2214175
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12687018
Relay_Log_Space: 12132603