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.d

2.修改模板文件

#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

[mysql]

host=localhost

user=zabbixagent

password=123456

socket=/tmp/mysql.sock

[mysqladmin]

host=localhost

user=zabbixagent

password=123456

socket=/tmp/mysql.sock

4.创建授权账号

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模板

wKiom1fZNJXAsQIGAABZkdPa1Xg131.png-wh_50

7.最终效果

wKioL1fZNMKTR9FSAADa92Wcfz8319.png-wh_50

二、自定义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 "\<Slave_SQL_Running\>"|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

wKiom1fZNUCTkrC3AACBIV4okzQ200.png-wh_50

创建slave io线程items

wKioL1fZNUDBQgXxAACBo_iKV8A160.png-wh_50

创建slave sql线程items

wKioL1fZNUDwLB0RAACCXplOhwY819.png-wh_50

三、主从同步延迟报警配置

1.定义触发器

创建主从延迟触发器

wKiom1fZNjqTq4WxAAB0CmArayg906.png-wh_50

2.定义action

wKiom1fZNjqiAcf_AABphb6uO6w151.png-wh_50

wKioL1fZNjuD5HOxAABE1DJFKTk371.png-wh_50

wKioL1fZNjuhqWpWAAB2gSm6NSU579.png-wh_50

3.定义邮件发送用户

修改报警媒介类型的邮件服务器地址

wKiom1fZOCuzrxoSAABaKIKDWJM099.png-wh_50

用户关联到报警媒介并设置邮件账号

wKiom1fZOCuAEmp5AABJjOlyrMY516.png-wh_50

wKioL1fZOCvgjmfoAAA2dRYa7jc292.png-wh_50

查看邮件

wKiom1fZNjzTSE9zAABwX1nHHbo761.png-wh_50

邮件具体内容

wKioL1fZNjzxCtEjAABl7rNYHGY504.png-wh_50

四、主从同步状态报警配置

1.创建主从同步状态触发器

定义io thread状态

wKioL1fZOT_wZFU4AACFD-XS4mg360.png-wh_50

wKioL1fZOUCwEVQEAABvHxPQ0gI363.png-wh_50

定义sql thread状态

wKiom1fZOUDyIH96AABucucu-jQ062.png-wh_50

2.测试

在slave上分别停止io线程和sql线程

模拟停止io线程

mysql> stop slave io_thread;

wKioL1fZOUGweuiMAABVSIsN5xo650.png-wh_50

同时告警邮件发出

wKiom1fZOUHg87_YAABoIEst2_g351.png-wh_50

恢复io线程

mysql> start slave io_thread;

故障恢复通知邮件

wKioL1fZOUKgbi8qAABZAVZbNQc456.png-wh_50

模拟停止sql线程

mysql> stop slave sql_thread;

wKiom1fZOULBOgl-AABpW6S4uyU032.png-wh_50

同时告警邮件发出

wKioL1fZOUKysDDjAABnK8seoqQ072.png-wh_50

恢复sql线程

mysql> start slave sql_thread;

wKiom1fZOUOSrHzgAAB7aF42R2Q189.png-wh_50

故障恢复通知邮件

wKioL1fZOUOj1_WbAABnLXMh_SY151.png-wh_50

©著作权归作者所有：来自51CTO博客作者hnr1017的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql监控Zabbix监控系统