mysqld_exporter监控MySQL服务

一、MySQL授权
1、登录MySQL服务器对监控使用的账号授权

CREATE USER 'exporter'@'localhost' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
flush privileges;
1
2
3

2、上传mysqld_exporter安装包，解压并mv至/usr/local下

二、防火墙放行

firewall-cmd --add-port=tcp/9104"  --permanent
firewall-cmd –reload
1
2

三、Prometheus抓取配置
[root@sxjk-prometheus prometheus]# cat scrapetarget.json

[
 ......省略
  //增加如下抓取配置，注意增加的配置在全局的位置
  {
    "targets": ["192.168.1.11:9104" ],
    "labels": {
      "os": "Linux",
      "region": "test",
      "middleware": "mysql"
    }
  }
]
1
2
3
4
5
6
7
8
9
10
11
12

四、拷贝MySQL配置文件
cp /etc/my.cnf /root/.my.cnf
注：新版本V0.15.0
cat /usr/local/mysqld_exporter/.my.cnf

[client]
host = 192.168.1.11
user = exporter
password = 123456
socket = /data/mysql/tmp/mysql.sock
1
2
3
4
5

五、配置为daemon
配置服务 vi /etc/systemd/system/mysqld_exporter.service

[Unit]
Description=mysqld_exporter v0.12.1 for sccin production envirenment.
ConditionFileIsExecutable=/usr/local/mysqld_exporter-0.12.1/mysqld_exporter
Requires=network-online.target
After=network-online.target

[Service]
Type=simple
User=root
Group=root
Environment='DATA_SOURCE_NAME=exporter:123456@(localhost:3306)/'
ExecStart=/usr/local/mysqld_exporter-0.12.1/mysqld_exporter
PrivateTmp=true
StartLimitInterval=0
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

注：新版本V0.15.0 vi /etc/systemd/system/mysqld_exporter.service

[Unit]
Description=mysqld_exporter

[Service]
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf /usr/local/mysqld_exporter/.my.cnf --web.listen-address=192.168.1.11:9104 \
--collect.slave_status \
--collect.binlog_size \
--collect.info_schema.processlist \
--collect.info_schema.innodb_metrics \
--collect.engine_innodb_status \
--collect.perf_schema.file_events \
--collect.perf_schema.replication_group_member_stats
Restart=on-failure

[Install]
WantedBy=multi-user.target 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

六、启动检查服务
systemctl daemon-reload
systemctl enable mysqld_exporter
systemctl start mysqld_exporter
systemctl status mysqld_exporter

七、检查数据
在prometheus查询端查询数据是否正确生成