MySQL_Exporter

# 5.7+
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'passwd' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

# 5.6
CREATE USER `exporter`@`%` IDENTIFIED BY 'passwd';
GRANT USAGE ON *.* TO `exporter`@`%` WITH MAX_USER_CONNECTIONS 3;
GRANT Process, Replication Client, Select ON *.* TO `exporter`@`%`;

临时启动

tar mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64
export DATA_SOURCE_NAME='exporter:passwd@(localhost:3306)/'
mysqld_exporter

使用systemctl控制服务

tar -zxf mysqld_exporter-0.14.0.linux-amd64.tar.gz

cp mysqld_exporter-0.14.0.linux-amd64/mysqld_exporter /usr/bin/

mkdir -p /etc/prometheus/mysqld_exporter/

cat > /etc/prometheus/mysqld_exporter/my.cnf << EOF
[client]
host=localhost
port=3306
user=exporter
password=passwd
EOF


cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=mysqld_exporter Daemon

[Service]
ExecStart=/usr/bin/mysqld_exporter \
--config.my-cnf=/etc/prometheus/mysqld_exporter/my.cnf \
--collect.auto_increment.columns \
--collect.binlog_size \
--collect.global_status \
--collect.global_variables \
--collect.info_schema.innodb_metrics \
--collect.info_schema.innodb_cmp \
--collect.info_schema.innodb_cmpmem \
--collect.info_schema.processlist \
--collect.info_schema.query_response_time \
--collect.info_schema.tablestats \
--collect.info_schema.userstats \
--collect.perf_schema.eventswaits \
--collect.perf_schema.file_events \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.tablelocks \
--collect.slave_status \
--web.listen-address=0.0.0.0:9104

User=root
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

服务控制

systemctl enable mysqld_exporter
systemctl start mysqld_exporter
systemctl status mysqld_exporter

部分错误处理记录

问题

mysqld_exporter[9203]: ts=2022-08-08T07:28:42.919Z caller=exporter.go:149 level=error msg="Error pinging mysqld" err="Error 1045: Access denied for user 'exporter'@'::1' (using password: YES)"

原因：因为exporter用户可访问的主机范围不匹配 解决：增加exporter用户可访问的主机范围为::1或%

5.7.24版本 mysqld_exporter-0.15.1.linux-amd64

"Error from scraper" scraper=auto_increment.columns target=localhost:3306 err="Error 1064 (42000): You have an error in your SQL syntax; check the manua...
Hint: Some lines were ellipsized, use -l to show in full.

使用systemctl status mysqld_exporter -l查看全部信息，发现报错日志中的关键字auto_increment，去掉/etc/systemd/system/mysqld_exporter.service中的--collect.auto_increment.columns配置即可

Error from scraper" scraper=auto_increment.columns target=localhost:3306 err="Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING (table_schema,table_name)\n\t\t  WHERE c.extra = 'auto_increment' AND t.auto_' at line 10"

--collect.slave_status以下的几个参数是5.7专有的

cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=mysqld_exporter Daemon

[Service]
ExecStart=/usr/bin/mysqld_exporter \
--config.my-cnf=/etc/prometheus/mysqld_exporter/my.cnf \
--collect.binlog_size \
--collect.global_status \
--collect.global_variables \
--collect.info_schema.innodb_metrics \
--collect.info_schema.innodb_cmp \
--collect.info_schema.innodb_cmpmem \
--collect.info_schema.processlist \
--collect.info_schema.query_response_time \
--collect.info_schema.tablestats \
--collect.info_schema.userstats \
--collect.perf_schema.eventswaits \
--collect.perf_schema.file_events \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.tablelocks \
--collect.slave_status \

--collect.info_schema.innodb_tablespaces \
--collect.perf_schema.eventsstatementssum \
--collect.perf_schema.memory_events \
--collect.perf_schema.memory_events.remove_prefix \
--collect.perf_schema.replication_group_members \
--collect.perf_schema.replication_group_member_stats \
--collect.perf_schema.replication_applier_status_by_worker \
--collect.sys.user_summary \
-- version \
--web.listen-address=0.0.0.0:9104

User=root
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

--collect.slave_status以下的几个参数是5.6专有的

cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=mysqld_exporter Daemon

[Service]
ExecStart=/usr/bin/mysqld_exporter \
--config.my-cnf=/etc/prometheus/mysqld_exporter/my.cnf \
--collect.binlog_size \
--collect.global_status \
--collect.global_variables \
--collect.info_schema.innodb_metrics \
--collect.info_schema.innodb_cmp \
--collect.info_schema.innodb_cmpmem \
--collect.info_schema.processlist \
--collect.info_schema.query_response_time \
--collect.info_schema.tablestats \
--collect.info_schema.userstats \
--collect.perf_schema.eventswaits \
--collect.perf_schema.file_events \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.perf_schema.tablelocks \
--collect.slave_status \

--collect.engine_tokudb_status \
--collect.info_schema.replica_host \
--collect.perf_schema.eventsstatements \
--collect.perf_schema.eventsstatements.digest_text_limit \
--collect.perf_schema.eventsstatements.limit \
--collect.perf_schema.eventsstatements.timelimit \
-- version \
--web.listen-address=0.0.0.0:9104

User=root
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF