Prometheus exporter 连接mysql报密码错误

环境

• mysql版本： 5.7.28
• mysql_exporter 版本: 1.3.1

过程

创建mysql账号

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

下载软件包

cd /usr/local/bin/
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar axvf mysqld_exporter-0.14.0.linux-amd64.tar.gz
mv mysqld_exporter-0.14.0.linux-amd64/mysqld_exporter ./

创建systemd文件

cat >/etc/systemd/system/mysqld_exporter.service <<EOF
[Unit]
Description=Prometheus mysql Exporter
After=network-online.target
StartLimitInterval=0
StartLimitIntervalSec=0

[Service]
Type=simple
User=exporter
Group=exporter
Environment="DATA_SOURCE_NAME=exporter:PGAMDv0qhyHj1QSxQnJh@(127.0.0.1:3306)/"
ExecStart=/usr/local/bin/mysqld_exporter \
            --web.listen-address 0.0.0.0:9104

SyslogIdentifier=mysqld_exporter
Restart=always
RestartSec=5

LockPersonality=true
NoNewPrivileges=true
MemoryDenyWriteExecute=true
PrivateTmp=true
ProtectHome=true
RemoveIPC=true
RestrictSUIDSGID=true
ProtectSystem=full

[Install]
WantedBy=multi-user.target
EOF

提示： 实际上也可以使用--config.my-cnf /etc/.mysqld_exporter.cnf参数来将凭证解耦。

启动服务

systemctl enable mysqld_exporter --now

到此为止，服务器启动正常，满怀惬意的心情，喝着水点着头。。。然而，Grafana图表中久久没有数据展示出来。嗯，不出所料，估计又是有什么幺蛾子了。

排查过程

1、既然没有数据展示，那就有可能 mysql_exporter没有将数据暴露出来，从而导致Prometheus抓取不到我们要的数据，于是在从节点中执行以下命令：

# curl 127.0.0.1:9104/merics | grep mysql_slave_status_slave_io_running

结果返回空，过滤其它的指标也是如此，一律空空。元凶算不是找到了。

2、查看mysql日志，发现有大量的数据登陆失败的日志，与exporter的用户是一致的。

2022-08-20T05:12:15.209763Z 8611865 [Note] Access denied for user 'exporter'@'::1' (using password: YES)
2022-08-20T05:12:30.207791Z 8611872 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:12:45.205836Z 8611882 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:13:00.205198Z 8611887 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:13:15.205920Z 8611909 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:13:30.209632Z 8611916 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:13:45.205484Z 8611920 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)
2022-08-20T05:14:00.206024Z 8611924 [Note] Access denied for user 'exporter'@'127.0.0.1' (using password: YES)

再三核对用户名、密码，人头担保，没有错呀，使用mysql也可是可以正常登陆的。那只能求援Google了，发现有相同的“病”友与我一样的问题：https://alexkunin.medium.com/prometheus-mysqld-exporter-and-access-denied-errors-8e4d3e54f0af

解决

Environment="DATA_SOURCE_NAME=exporter:PGAMDv0qhyHj1QSxQnJh@(127.0.0.1:3306)/"
改成如下
Environment="DATA_SOURCE_NAME=exporter:PGAMDv0qhyHj1QSxQnJh@unix(/var/lib/mysql/mysql.sock)/?allowCleartextPasswords=true"

解决延伸

cat >/etc/mysqld_exporter/my.cnf<<EOF
[client]
host = 127.0.0.1
user = exporter
password = PGAMDv0qhyHj1QSxQnJh
EOF



cat >/etc/systemd/system/mysqld_exporter.service <<EOF
[Unit]
Description=Prometheus mysql Exporter
After=network-online.target
StartLimitInterval=0
StartLimitIntervalSec=0

[Service]
Type=simple
User=exporter
Group=exporter
ExecStart=/usr/local/bin/mysqld_exporter \
            --web.listen-address 0.0.0.0:9104 \
            --config.my-cnf=/etc/mysqld_exporter/my.cnf 

SyslogIdentifier=mysqld_exporter
Restart=always
RestartSec=5

LockPersonality=true
NoNewPrivileges=true
MemoryDenyWriteExecute=true
PrivateTmp=true
ProtectHome=true
RemoveIPC=true
RestrictSUIDSGID=true
ProtectSystem=full

[Install]
WantedBy=multi-user.target
EOF