Zabbix监控MySQL数据库

官方文档：https://www.zabbix.com/cn/integrations/mysql

部署过程：

1. 在MySQL数据库中创建监控账号并授权

CREATE USER 'zabbix'@'%'IDENTIFIED BY 'Likun@123';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'%';
flush privileges;

2. 找到自己MySQL的mysql.sock文件位置

find / -name  mysql.sock

3. 创建.my.cnf文件并写入相关信息

#创建目录
mkdir -p /var/lib/zabbix
#写入用户名和密码
vi /var/lib/zabbix/.my.cnf
 
[client]
user=zabbix
password=Likun@123
socket=/var/lib/mysql/mysql.sock
 
[mysql]
user=zabbix
password=Likun@123
socket=/var/lib/mysql/mysql.sock
 
[mysqladmin]
user=zabbix
password=Likun@123
socket=/var/lib/mysql/mysql.sock

官方说明 Create .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. The file must have three strings:

4. 修改zabbix目录权限

chown zabbix:zabbix /var/lib/zabbix -R

5. 找到userparameter_mysql.conf文件（一般在zabbix_agentd目录中）

find / -name userparameter_mysql.conf

6. 修改 userparameter_mysql.conf内容（其实每行只多了一个 HOME=/var/lib/zabbix ）

#原配置文件
UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" 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"

#修改为
UserParameter=mysql.ping[*],HOME=/var/lib/zabbix mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*],HOME=/var/lib/zabbix mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*],HOME=/var/lib/zabbix mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*],HOME=/var/lib/zabbix mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*],HOME=/var/lib/zabbix 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[*],HOME=/var/lib/zabbix mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*],HOME=/var/lib/zabbix mysql -h"$1" -P"$2" -sNX -e "show slave status"

7. 修改zabbix_agentd.cof,打开一个Include

vim /usr/local/zabbix/conf/zabbix_agentd.conf
#原内容：
# Include=/usr/local/etc/zabbix_agentd.conf.d/*.conf
#改为userparameter_mysql.conf所在位置
 Include=/usr/local/zabbix/conf/zabbix_agentd/*.conf

8. 重启zabbix_agent

systemctl restart zabbix-agent

 注意：重启过程中可能会报错：Job for zabbix-agent.service failed because a configured resource limit was exceeded. See "systemctl status zabbix-agent.service" and "journalctl -xe" for details.

使用journalctl -xe 查看错误信息

systemd[1]: PID file /tmp/zabbix_agentd.pid not readable (yet?) after start.

 原因：在zabbix_agentd.conf中打开了一个Include位置，在那个位置中除了userparameter_mysql.conf文件外，还有一个userparameter_examples.conf文件，移走或者删除这个文件即可

rm /usr/local/zabbix/conf/zabbix_agentd/userparameter_examples.conf 

 9. 在zabbix web页面关联MySQL模板

 10. 等待几分钟，zabbix6.0 自带模板监控MySQL有数据，监测成功 ！

 可能出现的问题：Failed: cannot extract XML value with xpath "/resultset/row[field/text()='Uptime']/field[@name='Value']/text()": Zabbix was compiled without libxml2 support

原因：可能在编译zabbix-server是没有指定libxml2模块。从新编译即可

./configure --prefix=/usr/local/zabbix --enable-server --enable-agent --with-mysql --with-net-snmp --with-libcurl --with-libxml2 --with-unixodbc --enable-java && make && make install