` to the request. Sample config file for multiple configurations [client] user = foo password = foo123 [client.servers] user = bar password = ${PASSWORD_FROM_ENV} [client.servers2] user = baz password = MY_$$ECRET If your password contains a dollar sign ($) and you don't want it to be interpreted as an environment variable, escape it by doubling it ($$). In the example above, the password for `client.servers2` is `MY_$ECRET`. On the prometheus side you can set a scrape config as follows - job_name: mysql # To get metrics about the mysql exporter’s targets metrics_path: /probe params: # Not required. Will match value to child in config file. Default value is `client`. auth_module: [client.servers] static_configs: - targets: # All mysql hostnames or unix sockets to monitor. - server1:3306 - server2:3306 - unix:///run/mysqld/mysqld.sock relabel_configs: - source_labels: [__address__] target_label: __param_target - source_labels: [__param_target] target_label: instance - target_label: __address__ # The mysqld_exporter host:port replacement: localhost:9104 ##### Flag format Example format for flags for version > 0.10.0: --collect.auto_increment.columns --no-collect.auto_increment.columns Example format for flags for version <= 0.10.0: -collect.auto_increment.columns -collect.auto_increment.columns=[true|false] ### Collector Flags Name | MySQL Version | Description -------------------------------------------------------------|---------------|------------------------------------------------------------------------------------ collect.auto_increment.columns | 5.1 | Collect auto_increment columns and max values from information_schema. collect.binlog_size | 5.1 | Collect the current size of all registered binlog files collect.engine_innodb_status | 5.1 | Collect from SHOW ENGINE INNODB STATUS. collect.engine_tokudb_status | 5.6 | Collect from SHOW ENGINE TOKUDB STATUS. collect.global_status | 5.1 | Collect from SHOW GLOBAL STATUS (Enabled by default) collect.global_variables | 5.1 | Collect from SHOW GLOBAL VARIABLES (Enabled by default) collect.heartbeat | 5.1 | Collect from [heartbeat](#heartbeat). collect.heartbeat.database | 5.1 | Database from where to collect heartbeat data. (default: heartbeat) collect.heartbeat.table | 5.1 | Table from where to collect heartbeat data. (default: heartbeat) collect.heartbeat.utc | 5.1 | Use UTC for timestamps of the current server (`pt-heartbeat` is called with `--utc`). (default: false) collect.info_schema.clientstats | 5.5 | If running with userstat=1, set to true to collect client statistics. collect.info_schema.innodb_metrics | 5.6 | Collect metrics from information_schema.innodb_metrics. collect.info_schema.innodb_tablespaces | 5.7 | Collect metrics from information_schema.innodb_sys_tablespaces. collect.info_schema.innodb_trx | 5.5 | Collect active InnoDB transaction counts and oldest transaction age by state (disabled by default). collect.info_schema.innodb_cmp | 5.5 | Collect InnoDB compressed tables metrics from information_schema.innodb_cmp. collect.info_schema.innodb_cmpmem | 5.5 | Collect InnoDB buffer pool compression metrics from information_schema.innodb_cmpmem. collect.info_schema.processlist | 5.1 | Collect thread state counts from information_schema.processlist. collect.info_schema.processlist.min_time | 5.1 | Minimum time a thread must be in each state to be counted. (default: 0) collect.info_schema.query_response_time | 5.5 | Collect query response time distribution if query_response_time_stats is ON. collect.info_schema.replica_host | 5.6 | Collect metrics from information_schema.replica_host_status. collect.info_schema.rocksdb_perf_context | 5.6 | Collect RocksDB metrics from information_schema.ROCKSDB_PERF_CONTEXT. collect.info_schema.tables | 5.1 | Collect metrics from information_schema.tables. collect.info_schema.tables.databases | 5.1 | The list of databases to collect table stats for, or '`*`' for all. collect.info_schema.tablestats | 5.1 | If running with userstat=1, set to true to collect table statistics. collect.info_schema.schemastats | 5.1 | If running with userstat=1, set to true to collect schema statistics collect.info_schema.userstats | 5.1 | If running with userstat=1, set to true to collect user statistics. collect.mysql.user | 5.5 | Collect data from mysql.user table collect.perf_schema.eventsstatements | 5.6 | Collect metrics from performance_schema.events_statements_summary_by_digest. collect.perf_schema.eventsstatements.digest_text_limit | 5.6 | Maximum length of the normalized statement text. (default: 120) collect.perf_schema.eventsstatements.limit | 5.6 | Limit the number of events statements digests by response time. (default: 250) collect.perf_schema.eventsstatements.timelimit | 5.6 | Limit how old the 'last_seen' events statements can be, in seconds. (default: 86400) collect.perf_schema.eventsstatementssum | 5.7 | Collect metrics from performance_schema.events_statements_summary_by_digest summed. collect.perf_schema.eventswaits | 5.5 | Collect metrics from performance_schema.events_waits_summary_global_by_event_name. collect.perf_schema.file_events | 5.6 | Collect metrics from performance_schema.file_summary_by_event_name. collect.perf_schema.file_instances | 5.5 | Collect metrics from performance_schema.file_summary_by_instance. collect.perf_schema.file_instances.remove_prefix | 5.5 | Remove path prefix in performance_schema.file_summary_by_instance. collect.perf_schema.indexiowaits | 5.6 | Collect metrics from performance_schema.table_io_waits_summary_by_index_usage. collect.perf_schema.memory_events | 5.7 | Collect metrics from performance_schema.memory_summary_global_by_event_name. collect.perf_schema.memory_events.remove_prefix | 5.7 | Remove instrument prefix in performance_schema.memory_summary_global_by_event_name. collect.perf_schema.tableiowaits | 5.6 | Collect metrics from performance_schema.table_io_waits_summary_by_table. collect.perf_schema.tablelocks | 5.6 | Collect metrics from performance_schema.table_lock_waits_summary_by_table. collect.perf_schema.replication_group_members | 5.7 | Collect metrics from performance_schema.replication_group_members. collect.perf_schema.replication_group_member_stats | 5.7 | Collect metrics from performance_schema.replication_group_member_stats. collect.perf_schema.replication_applier_status_by_worker | 5.7 | Collect metrics from performance_schema.replication_applier_status_by_worker. collect.slave_status | 5.1 | Collect from SHOW SLAVE STATUS (Enabled by default) collect.slave_hosts | 5.1 | Collect from SHOW SLAVE HOSTS collect.sys.user_summary | 5.7 | Collect metrics from sys.x$user_summary (disabled by default). ### General Flags Name | Description -------------------------------------------|-------------------------------------------------------------------------------------------------- mysqld.address | Hostname and port used for connecting to MySQL server, format: `host:port`. (default: `localhost:3306`) mysqld.username | Username to be used for connecting to MySQL Server config.my-cnf | Path to .my.cnf file to read MySQL credentials from. (default: `~/.my.cnf`) log.level | Logging verbosity (default: info) exporter.lock_wait_timeout | Set a lock_wait_timeout (in seconds) on the connection to avoid long metadata locking. (default: 2) exporter.enable_lock_wait_timeout | Enable the lock_wait_timeout connection parameter. Makes the exporter compatible with older versions of MySQL. (default: true) exporter.log_slow_filter | Add a log_slow_filter to avoid slow query logging of scrapes. NOTE: Not supported by Oracle MySQL. exporter.query_timeout | Per-scraper query timeout (in seconds). 0 disables the timeout. (default: 0, disabled) exporter.max_open_connections | Maximum number of open connections to the database per scrape. Must be >= 1. The pool is per scrape request, so in multi-target mode total connections scale with concurrent targets; keep the value within the exporter user's `MAX_USER_CONNECTIONS` grant. (default: 2) tls.insecure-skip-verify | Ignore tls verification errors. web.config.file | Path to a [web configuration file](#tls-and-basic-authentication) web.listen-address | Address to listen on for web interface and telemetry. web.telemetry-path | Path under which to expose metrics. version | Print the version information. ### Environment Variables Name | Description -------------------------------------------|-------------------------------------------------------------------------------------------------- MYSQLD_EXPORTER_PASSWORD | Password to be used for connecting to MySQL Server ### Configuration precedence If you have configured cli with both `mysqld` flags and a valid configuration file, the options in the configuration file will override the flags for `client` section. ## TLS and basic authentication The MySQLd Exporter supports TLS and basic authentication. To use TLS and/or basic authentication, you need to pass a configuration file using the `--web.config.file` parameter. The format of the file is described [in the exporter-toolkit repository](https://github.com/prometheus/exporter-toolkit/blob/master/docs/web-configuration.md). ## Customizing Configuration for a SSL Connection If The MySQL server supports SSL, you may need to specify a CA truststore to verify the server's chain-of-trust. You may also need to specify a SSL keypair for the client side of the SSL connection. To configure the mysqld exporter to use a custom CA certificate, add the following to the mysql cnf file: ``` ssl-ca=/path/to/ca/file ``` To specify the client SSL keypair, add the following to the cnf. ``` ssl-key=/path/to/ssl/client/key ssl-cert=/path/to/ssl/client/cert ``` It's possible to also restrict the TLS versions that can be used between the MySQL server and mysqld exporter by specifying versions in the mysql cnf file like this: ``` tls-min-version=TLSv1.2 tls-max-version=TLSv1.3 ``` In some environments the MySQL server's TLS certificate SN or SAN may not include the host that mysqld exporter uses to connect to it. This could happen in cases when the certificate includes only DNS names, while mysqld exporter uses an IP address to connect. To allow secure connections in these cases, tls-server-name can be used to specify the server's name to use during verification. The parameter works the same as server_name in Prometheus's scrape config configuration. ``` tls-server-name=mysql.example ``` ## Using Docker You can deploy this exporter using the [prom/mysqld-exporter](https://hub.docker.com/r/prom/mysqld-exporter/) Docker image. For example: ```bash docker network create my-mysql-network docker pull prom/mysqld-exporter docker run -d \ -p 9104:9104 \ -v /home/user/user_my.cnf:/.my.cnf \ --network my-mysql-network \ prom/mysqld-exporter ``` ## heartbeat With `collect.heartbeat` enabled, mysqld_exporter will scrape replication delay measured by heartbeat mechanisms. [Pt-heartbeat][pth] is the reference heartbeat implementation supported. [pth]:https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html ## InnoDB transactions Enable `--collect.info_schema.innodb_trx` to collect from `information_schema.innodb_trx`. This collector supports MySQL >= 5.6, Percona Server for MySQL >= 5.6, and MariaDB >= 10.3. It requires the `PROCESS` privilege, included in the [required grants](#required-grants). The `5.5` entry in the collector flags table reflects availability of `INNODB_TRX` in [MySQL 5.5](https://downloads.mysql.com/docs/refman-5.5-en.a4.pdf); the exporter's supported MySQL minimum remains 5.6. See the [MySQL](https://dev.mysql.com/doc/refman/8.4/en/information-schema-innodb-trx-table.html) and [MariaDB](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-innodb-tables/information-schema-innodb_trx-table) table documentation. Metric | Type | Description -------|------|------------ `mysql_info_schema_innodb_trx_transactions` | Gauge | Number of active transactions in each state. `mysql_info_schema_innodb_trx_oldest_transaction_age_seconds` | Gauge | Age in seconds of the oldest active transaction in each state. Both metrics have a `state` label using the database's values: `RUNNING`, `LOCK WAIT`, `ROLLING BACK`, or `COMMITTING`. Only states present in the table are emitted; an empty table produces no transaction metrics. The collector includes transactions of all ages. Choose alert thresholds in PromQL, for example: ```promql max by (instance) (mysql_info_schema_innodb_trx_oldest_transaction_age_seconds) > 300 ``` ## Filtering enabled collectors The `mysqld_exporter` will expose all metrics from enabled collectors by default. This is the recommended way to collect metrics to avoid errors when comparing metrics of different families. For advanced use the `mysqld_exporter` can be passed an optional list of collectors to filter metrics. The `collect[]` parameter may be used multiple times. In Prometheus configuration you can use this syntax under the [scrape config](https://prometheus.io/docs/prometheus/latest/configuration/configuration/#). ```yaml params: collect[]: - foo - bar ``` This can be useful for having different Prometheus servers collect specific metrics from targets. ## Example Rules There is a set of sample rules, alerts and dashboards available in the [mysqld-mixin](mysqld-mixin/) [hub]: https://hub.docker.com/r/prom/mysqld-exporter/ [travis]: https://travis-ci.org/prometheus/mysqld_exporter [quay]: https://quay.io/repository/prometheus/mysqld-exporter [parsetime]: https://github.com/go-sql-driver/mysql#parsetime