Zabbix5.0监控MySQL

Zabbix5.0监控MySQL

一.准备工作

监控MySQL数据库，需要安装5.0版本的agent
如果安装了旧版本，须先卸载rpm -e zabbix-agent
1.添加安装源及yum安装
rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-release-5.0-1.el7.noarch.rpm
yum install -y zabbix-agent

2.修改配置
vim /etc/zabbix/zabbix_agentd.conf
Server=127.0.0.1改为实际服务端地址

3.启动服务及开机自启
systemctl start zabbix-agent.service
systemctl enable zabbix-agent.service

4.添加防火墙端口
firewall-cmd --zone=public --add-port=10050/tcp --permanent
systemctl restart firewalld.service

关闭Selinux:(这点非常重要 ，否则监控服务器没数据)
vim /etc/selinux/config
将 SELINUX=enforcing 改为 SELINUX=disabled

二．关联mysql模板

点击那个模版进去看看模版的描述
Requirements fortemplate operation:
1.Install Zabbixagent 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 userfor monitoring. For example:
CREATE USER ‘zbx_monitor’@‘%’ IDENTIFIED BY ‘’;
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON . TO ‘zbx_monitor’@‘%’;
For more informationread the MYSQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html, please.
4.Create .my.cnf inhome directory of Zabbix agent for Linux (/var/lib/zabbix by default ) ormy.cnf in c:\ for Windows. For example:
[client]
user=zbx_monitor
password=<password>

三．创建mysql账户用于监控

1）mysql下新增一个监控用户zabbix

CREATE USER 'zabbix'@'%' IDENTIFIED BY 'zabbix2024';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'%';
FLUSH PRIVILEGES;

2）/var/lib/zabbix下新增一个文件 .my.cnf （隐藏文件查看需要 ls -al）
#创建目录
mkdir -p /var/lib/zabbix
#写入用户名和密码
vim /var/lib/zabbix/.my.cnf

[client]
user = zabbix
password = zabbix2024
socket = /var/lib/mysql/mysql.sock

[mysql]
user = zabbix
password = zabbix2024
socket = /var/lib/mysql/mysql.sock

[mysqladmin]
user = zabbix
password = zabbix2024
socket = /var/lib/mysql/mysql.sock

#修改/var/lib/zabbix目录权限
chown -R zabbix:zabbix /var/lib/zabbix

四．设置模板mysql端

因为机器上（Zabbixserver）找不到Template_DB_MySQL.conf，所以查找zabbix自带的模板userparameter_mysql.conf

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

[root@ ~]# find / -name userparameter_mysql.conf
/usr/share/doc/zabbix-agent-5.0.42/userparameter_mysql.conf
打开看到这个文件的第一句话
#template_db_mysql.confcreated by Zabbix for “Template DB MySQL” and Zabbix 4.2

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

cp /usr/share/doc/zabbix-agent-5.0.42/userparameter_mysql.conf /etc/zabbix/zabbix_agentd.d/

3. 模版修改

vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

把原来的

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"

其实只多了一个 HOME=/var/lib/zabbix

检查zabbix账号是否能正常连接数据库： HOME=/var/lib/zabbix/ mysqladmin ping | grep -c alive

4）重启动zabbix_agentd

# systemctl restart zabbix-agent

5）看 监测 > 最新数据