Zabbix监控mysql配置及故障告警配置
本文主要介绍zabbix监控mysql的配置，包含使用zabbix自带模板监控mysql相关信息及自定义key监控mysql同步情况。同时介绍了触发器的创建及zabbix通过邮件方式告警配置。一、配置自带模板监控mysql
1.提供配置文件模板
文件位置在源码包/usr/local/src/zabbix-3.0.4/conf/zabbix_agentd/userparameter_mysql.conf
#cp/usr/local/src/zabbix-3.0.4/conf/zabbix_agentd/userparameter_mysql.conf /usr/local/zabbix/etc/zabbix_agentd.conf.d2.修改模板文件
#sed -i 's@/var/lib/zabbix@/usr/local/zabbix/etc@g' /usr/local/zabbix/etc/zabbix_agentd.conf.d/userparameter_mysql.conf修改后的文件
UserParameter=mysql.status
[*],echo
"show global status where Variable_name='$1';" |
HOME=/usr/local/zabbix/etc /usr/local/mysql/bin/mysql -N | awk '{print $$2}'
UserParameter=mysql.size
[*],bash
-c 'echo "select sum($(case "$3" in both|"") echo
"data_length+index_length";; data|index) echo "$3_length";;
free) echo "data_free";; esac)) from information_schema.tables$([[
"$1" = "all" || ! "$1" ]] || echo " where
table_schema=\"$1\"")$([[ "$2" = "all" || !
"$2" ]] || echo "and table_name=\"$2\"");" |
HOME=/usr/local/zabbix/etc /usr/local/mysql/bin/mysql -N'
UserParameter=mysql.ping,HOME=/usr/local/zabbix/etc
/usr/local/mysql/bin/mysqladmin ping | grep -c alive
UserParameter=mysql.version,/usr/local/mysql/bin/mysql
-V
3.提供agent连数据库的文件
#vi /usr/local/zabbix/etc/.my.cnf
host=localhost
user=zabbixagent
password=123456
socket=/tmp/mysql.sock
host=localhost
user=zabbixagent
password=123456
socket=/tmp/mysql.sock4.创建授权账号
mysql> GRANT USAGE,PROCESS,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'zabbixagent'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;5.修改zabbix_agentd.conf
Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/
重启服务
6.在web界面关联mysql模板
http://s5.运维网.com/wyfs02/M02/87/45/wKiom1fZNJXAsQIGAABZkdPa1Xg131.png-wh_500x0-wm_3-wmp_4-s_801853015.png
7.最终效果
http://s4.运维网.com/wyfs02/M00/87/43/wKioL1fZNMKTR9FSAADa92Wcfz8319.png-wh_500x0-wm_3-wmp_4-s_3998225736.png
二、自定义key监控slave
1.提供监控slave的配置文件
#cat/usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql_slave.conf
UserParameter=mysql.slave.Seconds_Behind_Master,echo
"show slave status\G"| HOME=/usr/local/zabbix/etc
/usr/local/mysql/bin/mysql |grep "Seconds_Behind_Master"|awk '{print
$2}'
UserParameter=mysql.slave.Slave_IO_Running,echo
"show slave status\G"| HOME=/usr/local/zabbix/etc
/usr/local/mysql/bin/mysql |grep "Slave_IO_Running"|awk '{print $2}'
UserParameter=mysql.slave.Slave_SQL_Running,echo
"show slave status\G"|HOME=/usr/local/zabbix/etc
/usr/local/mysql/bin/mysql |grep "\"|awk
'{print $2}'
2.重启agent服务
# service
zabbix_agentd restart
3.命令测试key获取的值
#
/usr/local/zabbix/bin/zabbix_get -s 192.168.1.16 -k
"mysql.slave.Seconds_Behind_Master"
#
/usr/local/zabbix/bin/zabbix_get -s 192.168.1.16 -k
"mysql.slave.Slave_IO_Running"
#
/usr/local/zabbix/bin/zabbix_get -s 192.168.1.16 -k
"mysql.slave.Slave_SQL_Running"
以上命令均返回正常值说明自定义key成功
4.在web界面配置监控项
创建slave延迟items
http://s5.运维网.com/wyfs02/M01/87/45/wKiom1fZNUCTkrC3AACBIV4okzQ200.png-wh_500x0-wm_3-wmp_4-s_3490706135.png
创建slave io线程items
http://s5.运维网.com/wyfs02/M01/87/43/wKioL1fZNUDBQgXxAACBo_iKV8A160.png-wh_500x0-wm_3-wmp_4-s_1761872513.png
创建slave sql线程items
http://s5.运维网.com/wyfs02/M01/87/43/wKioL1fZNUDwLB0RAACCXplOhwY819.png-wh_500x0-wm_3-wmp_4-s_1841307907.png
三、主从同步延迟报警配置
1.定义触发器
创建主从延迟触发器
http://s4.运维网.com/wyfs02/M00/87/45/wKiom1fZNjqTq4WxAAB0CmArayg906.png-wh_500x0-wm_3-wmp_4-s_1795678925.png
2.定义action
http://s4.运维网.com/wyfs02/M02/87/46/wKiom1fZNjqiAcf_AABphb6uO6w151.png-wh_500x0-wm_3-wmp_4-s_1134050000.png
http://s3.运维网.com/wyfs02/M02/87/43/wKioL1fZNjuD5HOxAABE1DJFKTk371.png-wh_500x0-wm_3-wmp_4-s_54504217.png
http://s3.运维网.com/wyfs02/M02/87/43/wKioL1fZNjuhqWpWAAB2gSm6NSU579.png-wh_500x0-wm_3-wmp_4-s_1862202075.png
3.定义邮件发送用户
修改报警媒介类型的邮件服务器地址
http://s2.运维网.com/wyfs02/M01/87/46/wKiom1fZOCuzrxoSAABaKIKDWJM099.png-wh_500x0-wm_3-wmp_4-s_1870314847.png
用户关联到报警媒介并设置邮件账号
http://s3.运维网.com/wyfs02/M00/87/46/wKiom1fZOCuAEmp5AABJjOlyrMY516.png-wh_500x0-wm_3-wmp_4-s_509686980.png
http://s3.运维网.com/wyfs02/M02/87/43/wKioL1fZOCvgjmfoAAA2dRYa7jc292.png-wh_500x0-wm_3-wmp_4-s_1695146595.png
查看邮件
http://s3.运维网.com/wyfs02/M00/87/46/wKiom1fZNjzTSE9zAABwX1nHHbo761.png-wh_500x0-wm_3-wmp_4-s_2813792866.png
邮件具体内容
http://s5.运维网.com/wyfs02/M00/87/43/wKioL1fZNjzxCtEjAABl7rNYHGY504.png-wh_500x0-wm_3-wmp_4-s_389821571.png
四、主从同步状态报警配置
1.创建主从同步状态触发器
定义io thread状态
http://s5.运维网.com/wyfs02/M00/87/43/wKioL1fZOT_wZFU4AACFD-XS4mg360.png-wh_500x0-wm_3-wmp_4-s_193977423.png
http://s4.运维网.com/wyfs02/M01/87/43/wKioL1fZOUCwEVQEAABvHxPQ0gI363.png-wh_500x0-wm_3-wmp_4-s_2781128955.png
定义sql thread状态
http://s4.运维网.com/wyfs02/M02/87/46/wKiom1fZOUDyIH96AABucucu-jQ062.png-wh_500x0-wm_3-wmp_4-s_2177504.png
2.测试
在slave上分别停止io线程和sql线程
模拟停止io线程
mysql> stop slave
io_thread;
http://s4.运维网.com/wyfs02/M01/87/43/wKioL1fZOUGweuiMAABVSIsN5xo650.png-wh_500x0-wm_3-wmp_4-s_1312608781.png
同时告警邮件发出
http://s4.运维网.com/wyfs02/M00/87/46/wKiom1fZOUHg87_YAABoIEst2_g351.png-wh_500x0-wm_3-wmp_4-s_767957959.png
恢复io线程
mysql> start
slave io_thread;
故障恢复通知邮件
http://s4.运维网.com/wyfs02/M02/87/43/wKioL1fZOUKgbi8qAABZAVZbNQc456.png-wh_500x0-wm_3-wmp_4-s_2847544269.png
模拟停止sql线程
mysql> stop slave
sql_thread;
http://s2.运维网.com/wyfs02/M02/87/46/wKiom1fZOULBOgl-AABpW6S4uyU032.png-wh_500x0-wm_3-wmp_4-s_2208605755.png
同时告警邮件发出
http://s2.运维网.com/wyfs02/M02/87/43/wKioL1fZOUKysDDjAABnK8seoqQ072.png-wh_500x0-wm_3-wmp_4-s_2309432044.png
恢复sql线程
mysql> start
slave sql_thread;
http://s2.运维网.com/wyfs02/M01/87/46/wKiom1fZOUOSrHzgAAB7aF42R2Q189.png-wh_500x0-wm_3-wmp_4-s_672992014.png
故障恢复通知邮件
http://s2.运维网.com/wyfs02/M00/87/43/wKioL1fZOUOj1_WbAABnLXMh_SY151.png-wh_500x0-wm_3-wmp_4-s_597439503.png
页:
[1]