部署mysql_exporter并集成至Prometheus

╰+攻爆jí腚メ 2022-10-28 14:25 256阅读 0赞

## 背景 ##

prometheus采集mysql和maridb关系数据库性能指标数据

## 支持版本 ##

最新版[0.12.1 / 2019-07-10][0.12.1 _ 2019-07-10]采集器支持版本

*  MySQL >= 5.6.
 *  MariaDB >= 10.2

## 创建用户 ##

采集数据库指标信息需要连接到数据库，并赋权限

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

## 部署 ##

两种方式是，一种是通过服务应用，另外一种是通过docker服务

### 服务应用 ###

[下载][Link 1]服务插件，选择操作系统对应的版本

解压

> tar -xvf mysqld\_exporter-0.12.1.linux-amd64.tar.gz

启动

有两种方式，一种是通过配置文件，一种是通过环境变量，推荐通过配置文件，服务默认端口9104

1）配置文件（推荐）

my.cnf

[client]
    host=127.0.0.1
    port=3306
    user=exporter
    password=123456

服务启动

root@172.16.24.220[/prometheus/mysqld_exporter-0.12.1.linux-amd64]#./mysqld_exporter --config.my-cnf=./my.cnf 
    INFO[0000] Starting mysqld_exporter (version=0.12.1, branch=HEAD, revision=48667bf7c3b438b5e93b259f3d17b70a7c9aff96)  source="mysqld_exporter.go:257"
    INFO[0000] Build context (go=go1.12.7, user=root@0b3e56a7bc0a, date=20190729-12:35:58)  source="mysqld_exporter.go:258"
    INFO[0000] Enabled scrapers:                             source="mysqld_exporter.go:269"
    INFO[0000]  --collect.global_status                      source="mysqld_exporter.go:273"
    INFO[0000]  --collect.global_variables                   source="mysqld_exporter.go:273"
    INFO[0000]  --collect.slave_status                       source="mysqld_exporter.go:273"
    INFO[0000]  --collect.info_schema.innodb_cmp             source="mysqld_exporter.go:273"
    INFO[0000]  --collect.info_schema.innodb_cmpmem          source="mysqld_exporter.go:273"
    INFO[0000]  --collect.info_schema.query_response_time    source="mysqld_exporter.go:273"
    INFO[0000] Listening on :9104                            source="mysqld_exporter.go:283"

2）环境变量

export DATA_SOURCE_NAME='user:password@(hostname:3306)/'
    ./mysqld_exporter <flags>

验证

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70][]

### 使用docker ###

镜像包

docker pull prom/mysqld-exporter

docker network create my-mysql-network
    docker pull prom/mysqld-exporter
    
    docker run -d \
      -p 9104:9104 \
      --network my-mysql-network  \
      -e DATA_SOURCE_NAME="user:password@(hostname:3306)/" \
      prom/mysqld-exporter

## 集成 ##

集成mysql\_exporter至prometheus，并重启prometheus

- job_name: mysqld
        static_configs:
        - targets: ['172.16.24.220:9104']

查看页面状态

[http://172.16.24.220:9091/targets][http_172.16.24.220_9091_targets]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70 1][]

mysql\_exporter 采集指定参数及对应最低数据库版本

<table> 
 <thead> 
  <tr> 
   <th>Name</th> 
   <th>MySQL Version</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>collect.auto_increment.columns</td> 
   <td>5.1</td> 
   <td>Collect auto_increment columns and max values from information_schema.</td> 
  </tr> 
  <tr> 
   <td>collect.binlog_size</td> 
   <td>5.1</td> 
   <td>Collect the current size of all registered binlog files</td> 
  </tr> 
  <tr> 
   <td>collect.engine_innodb_status</td> 
   <td>5.1</td> 
   <td>Collect from SHOW ENGINE INNODB STATUS.</td> 
  </tr> 
  <tr> 
   <td>collect.engine_tokudb_status</td> 
   <td>5.6</td> 
   <td>Collect from SHOW ENGINE TOKUDB STATUS.</td> 
  </tr> 
  <tr> 
   <td>collect.global_status</td> 
   <td>5.1</td> 
   <td>Collect from SHOW GLOBAL STATUS (Enabled by default)</td> 
  </tr> 
  <tr> 
   <td>collect.global_variables</td> 
   <td>5.1</td> 
   <td>Collect from SHOW GLOBAL VARIABLES (Enabled by default)</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.clientstats</td> 
   <td>5.5</td> 
   <td>If running with userstat=1, set to true to collect client statistics.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.innodb_metrics</td> 
   <td>5.6</td> 
   <td>Collect metrics from information_schema.innodb_metrics.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.innodb_tablespaces</td> 
   <td>5.7</td> 
   <td>Collect metrics from information_schema.innodb_sys_tablespaces.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.innodb_cmp</td> 
   <td>5.5</td> 
   <td>Collect InnoDB compressed tables metrics from information_schema.innodb_cmp.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.innodb_cmpmem</td> 
   <td>5.5</td> 
   <td>Collect InnoDB buffer pool compression metrics from information_schema.innodb_cmpmem.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.processlist</td> 
   <td>5.1</td> 
   <td>Collect thread state counts from information_schema.processlist.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.processlist.min_time</td> 
   <td>5.1</td> 
   <td>Minimum time a thread must be in each state to be counted. (default: 0)</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.query_response_time</td> 
   <td>5.5</td> 
   <td>Collect query response time distribution if query_response_time_stats is ON.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.replica_host</td> 
   <td>5.6</td> 
   <td>Collect metrics from information_schema.replica_host_status.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.tables</td> 
   <td>5.1</td> 
   <td>Collect metrics from information_schema.tables.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.tables.databases</td> 
   <td>5.1</td> 
   <td>The list of databases to collect table stats for, or '<code>*</code>' for all.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.tablestats</td> 
   <td>5.1</td> 
   <td>If running with userstat=1, set to true to collect table statistics.</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.schemastats</td> 
   <td>5.1</td> 
   <td>If running with userstat=1, set to true to collect schema statistics</td> 
  </tr> 
  <tr> 
   <td>collect.info_schema.userstats</td> 
   <td>5.1</td> 
   <td>If running with userstat=1, set to true to collect user statistics.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventsstatements</td> 
   <td>5.6</td> 
   <td>Collect metrics from performance_schema.events_statements_summary_by_digest.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventsstatements.digest_text_limit</td> 
   <td>5.6</td> 
   <td>Maximum length of the normalized statement text. (default: 120)</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventsstatements.limit</td> 
   <td>5.6</td> 
   <td>Limit the number of events statements digests by response time. (default: 250)</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventsstatements.timelimit</td> 
   <td>5.6</td> 
   <td>Limit how old the 'last_seen' events statements can be, in seconds. (default: 86400)</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventsstatementssum</td> 
   <td>5.7</td> 
   <td>Collect metrics from performance_schema.events_statements_summary_by_digest summed.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.eventswaits</td> 
   <td>5.5</td> 
   <td>Collect metrics from performance_schema.events_waits_summary_global_by_event_name.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.file_events</td> 
   <td>5.6</td> 
   <td>Collect metrics from performance_schema.file_summary_by_event_name.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.file_instances</td> 
   <td>5.5</td> 
   <td>Collect metrics from performance_schema.file_summary_by_instance.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.file_instances.remove_prefix</td> 
   <td>5.5</td> 
   <td>Remove path prefix in performance_schema.file_summary_by_instance.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.indexiowaits</td> 
   <td>5.6</td> 
   <td>Collect metrics from performance_schema.table_io_waits_summary_by_index_usage.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.memory_events</td> 
   <td>5.7</td> 
   <td>Collect metrics from performance_schema.memory_summary_global_by_event_name.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.memory_events.remove_prefix</td> 
   <td>5.7</td> 
   <td>Remove instrument prefix in performance_schema.memory_summary_global_by_event_name.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.tableiowaits</td> 
   <td>5.6</td> 
   <td>Collect metrics from performance_schema.table_io_waits_summary_by_table.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.tablelocks</td> 
   <td>5.6</td> 
   <td>Collect metrics from performance_schema.table_lock_waits_summary_by_table.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.replication_group_members</td> 
   <td>5.7</td> 
   <td>Collect metrics from performance_schema.replication_group_members.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.replication_group_member_stats</td> 
   <td>5.7</td> 
   <td>Collect metrics from performance_schema.replication_group_member_stats.</td> 
  </tr> 
  <tr> 
   <td>collect.perf_schema.replication_applier_status_by_worker</td> 
   <td>5.7</td> 
   <td>Collect metrics from performance_schema.replication_applier_status_by_worker.</td> 
  </tr> 
  <tr> 
   <td>collect.slave_status</td> 
   <td>5.1</td> 
   <td>Collect from SHOW SLAVE STATUS (Enabled by default)</td> 
  </tr> 
  <tr> 
   <td>collect.slave_hosts</td> 
   <td>5.1</td> 
   <td>Collect from SHOW SLAVE HOSTS</td> 
  </tr> 
  <tr> 
   <td>collect.heartbeat</td> 
   <td>5.1</td> 
   <td>Collect from&nbsp;<a href="https://github.com/prometheus/mysqld_exporter#heartbeat" rel="nofollow">heartbeat</a>.</td> 
  </tr> 
  <tr> 
   <td>collect.heartbeat.database</td> 
   <td>5.1</td> 
   <td>Database from where to collect heartbeat data. (default: heartbeat)</td> 
  </tr> 
  <tr> 
   <td>collect.heartbeat.table</td> 
   <td>5.1</td> 
   <td>Table from where to collect heartbeat data. (default: heartbeat)</td> 
  </tr> 
  <tr> 
   <td>collect.heartbeat.utc</td> 
   <td>5.1</td> 
   <td>Use UTC for timestamps of the current server (<code>pt-heartbeat</code>&nbsp;is called with&nbsp;<code>--utc</code>). (default: false)</td> 
  </tr> 
 </tbody> 
</table>

启动通用参数

<table> 
 <thead> 
  <tr> 
   <th>Name</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>config.my-cnf</td> 
   <td>Path to .my.cnf file to read MySQL credentials from. (default:&nbsp;<code>~/.my.cnf</code>)</td> 
  </tr> 
  <tr> 
   <td>log.level</td> 
   <td>Logging verbosity (default: info)</td> 
  </tr> 
  <tr> 
   <td>exporter.lock_wait_timeout</td> 
   <td>Set a lock_wait_timeout (in seconds) on the connection to avoid long metadata locking. (default: 2)</td> 
  </tr> 
  <tr> 
   <td>exporter.log_slow_filter</td> 
   <td>Add a log_slow_filter to avoid slow query logging of scrapes. NOTE: Not supported by Oracle MySQL.</td> 
  </tr> 
  <tr> 
   <td>web.listen-address</td> 
   <td>Address to listen on for web interface and telemetry.</td> 
  </tr> 
  <tr> 
   <td>web.telemetry-path</td> 
   <td>Path under which to expose metrics.</td> 
  </tr> 
  <tr> 
   <td>version</td> 
   <td>Print the version information.</td> 
  </tr> 
 </tbody> 
</table>

参考：

[https://registry.hub.docker.com/r/prom/mysqld-exporter/][https_registry.hub.docker.com_r_prom_mysqld-exporter]

[https://github.com/prometheus/mysqld\_exporter][https_github.com_prometheus_mysqld_exporter]

[0.12.1 _ 2019-07-10]: https://github.com/prometheus/mysqld_exporter/releases/tag/v0.12.1
[Link 1]: https://prometheus.io/download/#mysqld_exporter
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70]: /images/20221024/d9bf3b822d594866a49d0dee1098ea25.png
[http_172.16.24.220_9091_targets]: http://172.16.24.220:9091/targets
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzI2NDQ5_size_16_color_FFFFFF_t_70 1]: /images/20221024/076bbd1d0b23479f9d8e345c20a4b186.png
[https_registry.hub.docker.com_r_prom_mysqld-exporter]: https://registry.hub.docker.com/r/prom/mysqld-exporter/
[https_github.com_prometheus_mysqld_exporter]: https://github.com/prometheus/mysqld_exporter