# Prometheus-采集MySQL指标

> mysql\_exporter 项目地址：[https://github.com/prometheus/mysqld\_exporter](https://github.com/prometheus/mysqld_exporter)

* 云厂商地址: 1.1.1.1
* 服务器地址: 2.2.2.2

### **1. 安装 mysql\_exporter** <a href="#id-1.-e5-ae-89-e8-a3-85-mysql_exporter" id="id-1.-e5-ae-89-e8-a3-85-mysql_exporter"></a>

```
mkdir /data

cd /usr/local/src

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz

mv mysqld_exporter-0.15.0.linux-amd64 /data/mysqld_exporter

cd /data/mysqld_exporter

cat >> mysql_node.cnf << EOF
[client]
host="1.1.1.1"
port=3306
user=exporter
password=123456
EOF
```

### **2. 数据库授权** <a href="#id-2.-e6-95-b0-e6-8d-ae-e5-ba-93-e6-8e-88-e6-9d-83" id="id-2.-e6-95-b0-e6-8d-ae-e5-ba-93-e6-8e-88-e6-9d-83"></a>

```
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'exporter'@'%' identified by '123456';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';
flush privileges;
```

### **3. 启动** <a href="#id-3.-e5-90-af-e5-8a-a8" id="id-3.-e5-90-af-e5-8a-a8"></a>

```
./mysqld_exporter --config.my-cnf=="./mysql_node.cnf" &
ss -tln |grep 9104
```

* 方式二，使用 systemd 管理

```
cat >> /usr/lib/systemd/system/mysqld_exporter.service << EOF

[Unit]
Description=mysqld_exporter
After=network.target

[Service]
Type=simple
ExecStart=/data/mysqld_exporter/mysqld_exporter --config.my-cnf=/data/mysqld_exporter/.my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target

EOF
```

```
systemctl daemon-reload
systemctl enable mysqld_exporter
systemctl start mysqld_exporter
systemctl status mysqld_exporter

$ ss -antpl |grep 9104
LISTEN     0      128       [::]:9104                  [::]:*                   users:(("mysqld_exporter",pid=3141,fd=3))
```

### **4. 检查是否收集了数据** <a href="#id-4.-e6-a3-80-e6-9f-a5-e6-98-af-e5-90-a6-e6-94-b6-e9-9b-86-e4-ba-86-e6-95-b0-e6-8d-ae" id="id-4.-e6-a3-80-e6-9f-a5-e6-98-af-e5-90-a6-e6-94-b6-e9-9b-86-e4-ba-86-e6-95-b0-e6-8d-ae"></a>

```
curl -s localhost:9104/metrics |grep ^mysql_up
mysql_up 1    ----> 1 说明收集监控信息成功， 0 代表不成功
```

### **5. Prometheus 配置** <a href="#id-5.-prometheus-e9-85-8d-e7-bd-ae" id="id-5.-prometheus-e9-85-8d-e7-bd-ae"></a>

```
root@monitor-1:/data/monitor/prometheus cat prometheus.yml
  #- job_name: 'mysql'
  #  static_configs:
  #    - targets: ['x.x.x.x:9104']
  #      labels:
  #        instance: db1
  ... 略
  # MySQL数据库相关
  - job_name: 'MySQL数据库'
    static_configs:
    file_sd_configs:
    - refresh_interval: 30s
      files:
      - "vhosts/mysql/*.json" 
```

```
root@monitor-1:/data/monitor/prometheus# cat vhosts/mysql/saas.json 
[
  {
    "targets": ["2.2.2.2:9104"],
    "labels": {
         "label": "xxxx使用",
         "account": "account@xxx.com",
         "cloud": "xxxxx",
         "instance": "1.1.1.1:3306"          # mysql不在服务器，覆盖 instance
    }
  }
]
```

* Prometheus 命令热加载, 我这里端口: 8090

```
./promtool  check config  prometheus.yml
curl  -XPOST localhost:8090/-/reload    # 主配置文件热加载，修改子配置会自动加载
```

### **6. Grafana 配置** <a href="#id-6.-grafana-e9-85-8d-e7-bd-ae" id="id-6.-grafana-e9-85-8d-e7-bd-ae"></a>

> 根据喜好自行选择: [https://grafana.com/grafana/dashboards](https://grafana.com/grafana/dashboards)
