zabbix5.4使用自带模板监控mysql数据库性能

1、在被监控的数据库主机上，新建数据库用户，并赋予权限：
mysql> CREATE USER 'zbx_mon'@'%' IDENTIFIED BY '123456';
mysql> GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_mon'@'%';
mysql> flush privileges;

2、在被监控的数据库主机上，增加和修改配置文件：
2.1复制mysql参数配置文件
注：/usr/local/zabbix-agent/etc 为编译安装配置文件目录
# cp zabbix-5.4.4/conf/zabbix_agentd/userparameter_mysql.conf /usr/local/zabbix-agent/etc/zabbix_agentd.d/template_db_mysql.conf

配置文件描述如下：做参考。

Requirements for template operation:
1. Create MySQL user for monitoring. For example:
CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
For more information please read the MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html.
2. Set in the {$MYSQL.DSN} macro the data source name of the MySQL instance either session name from Zabbix agent 2 configuration file or URI.
Examples: MySQL1, tcp://localhost:3306, tcp://172.16.0.10, unix:/var/run/mysql.sock
For more information about MySQL Unix socket file please read the MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/problems-with-mysql-sock.html.
3. If you had set URI in the {$MYSQL.DSN}, please define the user name and password in host macros ({$MYSQL.USER} and {$MYSQL.PASSWORD}).
Leave macros {$MYSQL.USER} and {$MYSQL.PASSWORD} empty if you use a session name. Set the user name and password in the Plugins.Mysql.<...> section of your Zabbix agent 2 configuration file.
For more information about configuring the Zabbix MySQL plugin please read the documentation https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/mysql/README.md.

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

2.2修改参数文件
# vi /usr/local/zabbix-agent/etc/zabbix_agentd.d/template_db_mysql.conf
UserParameter=mysql.ping[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], HOME=/usr/local/zabbix-agent/etc /usr/local/mysql/bin/mysql -h"$1" -P"$2" -sNX -e "show slave status"
注：/usr/local/zabbix-agent/etc 中存放.my.cnf

3、配置数据库连接验证文件
# vi /usr/local/zabbix-agent/etc/.my.cnf
[mysql]
user=zbx_mon
password=123456
[mysqladmin]
user=zbx_mon
password=123456

4、修改zabbix的agent的配置文件
# vi /usr/local/zabbix-agent/etc/zabbix_agentd.conf
Include=/usr/local/zabbix-agent/etc/zabbix_agentd.conf.d/*.conf

5、重启服务：
# systemctl restart zabbix-agent.service