监控主从异常和延迟异常

目录

• zabbix监控mysql主从状态
  • 数据库主从状态配置
  • 配置主从延迟配置文件

---

zabbix监控mysql主从状态

主从复制搭建完成在master删除一个数据库slave主机也会自动删除

[root@master ~]# mysql -uroot -pliuyang123!
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang            |
| liuyang123         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop liuyang
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang123         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang            |
| liuyang123         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuyang123         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据库主从状态配置

配置监控脚本 从库中配置

添加配置文件
[root@zabbix scripts]# tail -2 /usr/local/etc/zabbix_agentd.conf
UserParameter=check_logs[*],/scripts/log.py $1 $2 $3
UserParameter=check_mysql_status,/bin/bash /scripts/mysql_status.sh
重启zabbix让配置文件生效
[root@zabbix scripts]# pkill zabbix
[root@zabbix scripts]# zabbix_agentd 
编写脚本
[root@zabbix scripts]# cat mysql_status.sh
#!/bin/bash
count=`mysql -uroot -pliuyang123!  -e "show slave status\G" 2> /dev/null |grep Yes -c`
if [ $count != 2 ];then
	echo "1"
else 
	echo "0"
fi
给脚本执行权限
[root@zabbix scripts]# chmod +x mysql_status.sh

去服务端检查key是否可用
[root@master ~]# zabbix_get -s 192.168.124.128 -k check_mysql_status
0

添加监控项

添加触发器

查看数据

手动触发告警

[root@zabbix scripts]# mysql -uroot -pliuyang123! -e "stop slave"
mysql: [Warning] Using a password on the command line interface can be insecure.

查看邮箱信息

配置主从延迟配置文件

//Seconds_Behind_Master是监控延迟的值

添加配置文件
[root@zabbix scripts]# tail -1 /usr/local/etc/zabbix_agentd.conf
UserParameter=check_mysql_zcdelay,/binbash /scripts/mysql_zcdelay.sh
重启zabbix让其配置文件生效
[root@zabbix scripts]# pkill zabbix
[root@zabbix scripts]# zabbix_agentd 
编写脚本
[root@zabbix scripts]# cat mysql_zcdelay.sh 
#!/bin/bash
zcvalue=`mysql -uroot -pliuyang123! -e "show slave status\G" 2> /dev/null |awk '/Seconds_Behind_Master/ {print $2}'`		//Seconds_Behind_Master是监控延迟的值
echo $zcvalue
给脚本添加可执行权限
[root@zabbix scripts]# chmod +x mysql_zcdelay.sh 
在服务端检查key是否可用
[root@master ~]# zabbix_get -s 192.168.124.128 -k check_mysql_zcdelay
0

添加监控项

添加触发器

查看邮件信息