3分钟配置zabbix 监控mysql

月小升在mysql的配置上卡了3天,原因一方面不熟悉,一方面我昨天18:00配置完毕,半天没有数据,就绝望的关闭了,我昨天晚上还在纠结要不要继续研究,今天打开一看,数据图表都有了。原来mysql的监控,需要一点时间来生效。

当知道了正确的办法,我在另一台服务器配置,其实只需要3分钟

1. 关联mysql模版

步骤:配置 > 主机 > 点击主机的主机 > 模版

见图

点击那个模版进去看看模版的描述

Requirements for template operation:
1.Install Zabbix agent and MySQL client.
2.Copy Template_DB_MySQL.conf into folder with Zabbix agent configuration (/etc/zabbix/zabbix_agentd.d/ by default). Don't forget restart zabbix-agent. 
3.Create MySQL user for monitoring. For example:
CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
For more information read the MYSQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html , please. 
4.Create .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. For example:
[client]
user=zbx_monitor
password=<password>
 
 
You can discuss this template or leave feedback on our forum https://www.zabbix.com/forum/zabbix-suggestions-and-feedback/384189-discussion-thread-for-official-zabbix-template-db-mysql
 
Template tooling version used: 0.35

2. 设置模版mysql端
备注:月小升的机器并没有/var/lib/zabbix 也找不到模版Template_DB_MySQL.conf,所以我最终用的都是另外一个userparameter_mysql.conf

1)查看zabbix中的mysql监控模板,find / -name userparameter_mysql.conf

[root@ ~]# find / -name userparameter_mysql.conf
/backup/soft/zabbix-4.4.6/conf/zabbix_agentd/userparameter_mysql.conf

这个文件的第一句话

#template_db_mysql.conf created by Zabbix for "Template DB MySQL" and Zabbix 4.2

2)拷贝到zabbix_agentd.conf.d/目录下

cp –r /backup/soft/zabbix-4.4.6/conf/zabbix_agentd/userparameter_mysql.conf /usr/local/etc/zabbix_agentd.conf.d/

3)测试模版是否有效

# zabbix_agentd -t mysql.ping -c /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf
 
mysql.ping                                    [t|mysqladmin: connect to server at '-P' failed
error: 'Unknown MySQL server host '-P' (2)'

4) 模版修改

# cat /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf
UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping

说-P这个参数不知道 $1 $2 应该从其他地方传入的参数。

测试纠缠的过程下面写,写最终成型的模版

UserParameter=mysql.ping[*], HOME=/etc/zabbix mysqladmin ping
 
UserParameter=mysql.get_status_variables[*],HOME=/etc/zabbix mysql -sNX -e "show global status"
UserParameter=mysql.version[*],HOME=/etc/zabbix mysqladmin version
UserParameter=mysql.uptime,HOME=/etc/zabbix mysqladmin status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "
 
UserParameter=mysql.db.discovery[*],HOME=/etc/zabbix mysql -sN -e "show databases"
UserParameter=mysql.dbsize[*], HOME=/etc/zabbix mysql -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABL
ES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], HOME=/etc/zabbix  mysql -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], HOME=/etc/zabbix  mysql -sNX -e "show slave status"

我的这个模版和拷贝过来的,其实只多了一个 HOME=/etc/zabbix (要相信原生的模版写法)

5)搞一下那个 .my.cnf

[mysql]
user=zbx_monitor
password=Javaer123456
[mysqladmin]
user=zbx_monitor
password=Javaer123456

路径/etc/zabbix/.my.cnf 和上面的HOME=/etc/zabbix 是不是一致,对,那个HOME的意思就是“大哥来这里找我”

6)重启动zabbix_agentd

这一步结束后,要等,等5分钟,10分钟的,不要着急。

7)看数据在监测 > 最新数据

8)看图标在监测 > 图形里

我觉得反复纠结userparameter_mysql.conf的过程是因为不少东西没弄明白了。
END

=====华丽分割线======
记录如下,下面的记录含有不少能继续测试zabbix底层读取配置文件的逻辑。
1. 如何确认配置文件能否读取一个key

zabbix_agentd -t mysql.ping[*] -c /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf

2. 如何确认mysql运作正常的

mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost ping

3. 如何确认Zabbix_server 能顺利读取agent的key

zabbix_get -slocalhost -p 10050 -k mysql.ping

1. 强制测试一个参数

zabbix_agentd -t mysql.ping[*] -c /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf

强制改成写死的,我就测试mysql.ping 一个参数

UserParameter=mysql.ping[*], mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost ping
zabbix_agentd -t mysql.ping -c /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf
mysql.ping                                    [t|mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive]

查看mysqladmin是否正常

#mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive

命令行改进,返回1或者0

# mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost ping|grep -c alive

我在/etc/zabbix下建立一个.my.cnf的测试

UserParameter=mysql.ping,HOME=/etc/zabbix/ mysqladmin ping | grep -c alive

这个命令中”UserParameter”表示这是一个用户自定义的脚本;“=”号后是脚本的内容;“mysql.ping”是Key,“,”号后的命令会在Zabbix Server向Agent发起获取“mysql.ping”这个key的请求时被调用,并将返回值返回给Server。

注释掉其他参数

#UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.ping[*], mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost ping
#UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
#UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
#UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
#UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
#UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
#UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"

重启动

测试server端读取情况

zabbix_get -slocalhost -p 10050 -k mysql.ping

mysqld is alive

2. 测试zabbix里mysql的其他状态extended-status

mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost extended-status

出来一堆东西

mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost extended-status | grep -w "Threads_connected" | cut -d "|" -f 3

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
71

测试这个玩意

UserParameter=mysql.status[*],mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost extended-status | grep -w "Threads_connected" | cut -d "|" -f 3
# zabbix_agentd -t mysql.status -c /usr/local/etc/zabbix_agentd.conf.d/userparameter_mysql.conf
mysql.status                                  [t|mysqladmin: [Warning] Using a password on the command line interface can be insecure.
 71]
zabbix_get -slocalhost -p 10050 -k mysql.status
mysqladmin -uzbx_monitor -p'Javaer123456' -hlocalhost extended-status | grep -w "Connections" | cut -d "|" -f 3

抄写个脚本给配置文件用

#!/bin/bash
 
#Filename:chk_mysql.sh
 
MYSQL_SOCK="/var/lib/mysql/mysql.sock"
MYSQL_USER='zbx_monitor'
MYSQL_PASSWORD='Javaer123456'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_Connect="/usr/bin/mysqladmin -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -S$MYSQL_SOCK"
 
if [ $# -ne 1 ];then
echo "please input one arguement"
fi
 
case $1 in
 
Uptime) #查询当前MySQL本次启动后的运行统计时间
result=`${MYSQL_Connect} status 2>/dev/null | cut -d ":" -f 2 | cut -d " " -f 2`
echo $result
;;
 
Slow_queries) #查看当前慢查询语句的个数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Slow_queries" | cut -d "|" -f 3`
echo $result
;;
 
Com_rollback) #执行回滚的个数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_rollback" | cut -d "|" -f 3`
echo $result
;;
 
Questions)
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Questions" | cut -d "|" -f 3`
echo $result
;;
 
Com_commit)
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_commit" | cut -d "|" -f 3`
echo $result
;;
 
Bytes_sent) #发送的字节数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Bytes_sent" | cut -d "|" -f 3`
echo $result
;;
 
Bytes_received) #接受的字节数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Bytes_received" | cut -d "|" -f 3`
echo $result
;;
 
Com_begin)
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_begin" | cut -d "|" -f 3`
echo $result
;;
 
Open_tables) #查看当前打开的表数量
result=`${MYSQL_Connect} status 2>/dev/null | cut -d ":" -f 5 | cut -d " " -f 2`
echo $result
;;
 
Threads_connected) #查看当前打开的连接数量
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_connected" | cut -d "|" -f 3`
echo $result
;;
 
Threads_cached) #查看线程缓存内的线程数量
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_cached" | cut -d "|" -f 3`
echo $result
;;
 
Threads_created) #查看创建用来处理连接的线程数。如果Threads_created较大,可能要增加thread_cache_size值。
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_created" | cut -d "|" -f 3`
echo $result
;;
 
Threads_running) #查看激活的(非睡眠状态)线程数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Threads_running" | cut -d "|" -f 3`
echo $result
;;
 
Slow_launch_threads) #查看创建时间超过slow_launch_time秒的线程数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Slow_launch_threads" | cut -d "|" -f 3`
echo $result
;;
 
Com_select) #查看select语句的执行数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_select" |cut -d "|" -f 3`
echo $result
;;
 
Com_insert) #查看insert语句的执行数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_insert" |cut -d "|" -f 3`
echo $result
;;
 
Com_update) #查看update语句的执行数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_update" | cut -d "|" -f 3`
echo $result
;;
 
Com_delete) #查看delete语句的执行数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Com_delete" | cut -d "|" -f 3`
echo $result
;;
 
Connections) #查看试图连接到MySQL(不管是否连接成功)的连接数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Connections" | cut -d "|" -f 3`
echo $result
;;
 
Table_locks_immediate) #查看立即获得的表的锁的次数
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Table_locks_immediate" | cut -d "|" -f 3`
echo $result
;;
 
Table_locks_waited) #查看不能立即获得的表的锁的次数。如果该值较高,并且有性能问题,你应首先优化查询,然后拆分表或使用复制
result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w "Table_locks_waited" | cut -d "|" -f 3`
echo $result
;;
 
*)
echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"
;;
esac
Usage:./chk.sh(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)

文件路径

/usr/local/etc/zabbix_agentd.conf.d/chk.sh

脚本在配置文件引入

UserParameter=mysql.status[*],/usr/local/etc/zabbix_agentd.conf.d/chk.sh $1

测试chk.sh 是否有用

zabbix_get -slocalhost -p 10050 -k "mysql.status[Com_update]"
zabbix_get -slocalhost -p 10050 -k "mysql.threads_connected"

新版本zabbix对应的key的形式。
mysql.connections.rate

mysql.threads_connected

result=`${MYSQL_Connect} extended-status 2>/dev/null | grep -w “Threads_connected” | cut -d “|” -f 3`
echo $result

单独配置一个参数mysql.threads_connected
UserParameter=mysql.threads_connected, mysqladmin -uzbx_monitor -p’Javaer123456′ -hlocalhost extended-status | grep -w “Connections” | cut -d “|” -f 3

zabbix 日志

cat /tmp/zabbix_server.log


This entry was posted in Linux and tagged . Bookmark the permalink.
月小升QQ 2651044202, 技术交流QQ群 178491360
首发地址:月小升博客https://java-er.com/blog/zabbix-mysql-monitor/
无特殊说明,文章均为月小升原创,欢迎转载,转载请注明本文地址,谢谢
您的评论是我写作的动力.
2020.03.24 评论已经全局关闭,有事加QQ聊天