Migrating MySQL Monitoring from Zabbix to Prometheus Using mysqld_exporter: Multi‑Instance Setup and Troubleshooting

Background : The author plans to migrate MySQL monitoring from Zabbix to Prometheus because other services already use Prometheus. The standard mysqld_exporter supports a 1:1 relationship (one exporter per MySQL instance), but recent versions claim 1:n support, which introduces several issues.

Environment preparation :

Two MySQL clusters (ports 6666 and 23750) with master/slave roles, all version 5.7.26.

Creation of exporter users (exporter, exporter1, exporter2) with appropriate privileges using the following SQL statements: CREATE USER 'exporter'@'192.168.%' identified by 'exporter'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'192.168.%'; GRANT SELECT ON mysql.* TO 'exporter'@'192.168.%'; CREATE USER 'exporter1'@'192.168.%' identified by 'exporter1'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter1'@'192.168.%'; GRANT SELECT ON mysql.* TO 'exporter1'@'192.168.%'; SET global max_connections = 3000;

Similar commands for the second cluster with user exporter2 and max_connections set to 1000.

mysqld_exporter version 0.14.0 deployed on 192.168.168.11:42001.

Prometheus version 2.40.2 deployed on 192.168.168.11:9999.

Testing multi‑instance collection :

Exporter configuration file ( my.cnf ) containing only a [client] section with user/password.

Exporter started with # ./mysqld_exporter --config.my-cnf=my.cnf --web.listen-address=:42001 >> exporter.log 2>&1 & .

Initial probe using curl 'http://localhost:42001/probe?target=192.168.168.11:6666' returned an HTML page instead of metrics because the target parameter was ignored and the exporter tried to connect to the default 127.0.0.1:3306.

Correct usage requires querying the /metrics endpoint with a proper target parameter, but the shipped release still lacked true multi‑target support.

Problem resolution :

Investigation of exporter commit history showed multi‑instance support was added after the latest release (January 2022). The author compiled the latest source from the main branch ( https://github.com/prometheus/mysqld_exporter/archive/refs/heads/main.zip ) using Go 1.19.

After rebuilding, a new exporter listening on port 42002 was started.

Probing with curl 'http://localhost:42002/probe?target=192.168.168.11:6666' correctly returned mysql_global_variables_max_connections 3000 , and probing the second cluster returned mysql_global_variables_max_connections 1000 , confirming that the target parameter now works.

To handle different credentials per cluster, the auth_module parameter was used. The configuration file was extended with additional sections: [client-exporter1] user = exporter1 password = exporter1 [client-exporter2] user = exporter2 password = exporter2 Probes with various auth_module values demonstrated that only the matching user could retrieve metrics, highlighting the need for matching exporter users to each MySQL instance.

Prometheus configuration :

Initial attempts to place auth_module under params caused YAML parsing errors because params expects a list of strings.

The corrected configuration moves the credential selector to labels attached to each target set, e.g.: - job_name: 'mysql' static_configs: - targets: ['192.168.168.11:6666', '192.168.168.12:6666'] labels: auth_module: client - targets: ['192.168.168.11:23750', '192.168.168.14:23750'] labels: auth_module: client-exporter2 metrics_path: /probe relabel_configs: - source_labels: [__address__] target_label: __param_target - source_labels: [__param_target] target_label: instance - target_label: __address__ replacement: 192.168.168.11:42002 - source_labels: [auth_module] target_label: __param_auth_module - action: labeldrop regex: auth_module

This layout reduces redundancy by keeping a single job while still allowing per‑target credential selection.

Summary :

The [client] section is mandatory in my.cnf ; omission causes startup errors.

Official releases prior to July 2022 do not support true multi‑instance collection; building from source is required.

When using auth_module , follow Prometheus documentation for proper syntax; placing it under params as a string leads to YAML errors.

For production, consider running multiple exporter instances behind a VIP or DNS name and monitor the exporters themselves.

All steps are provided for reference; adapt them to your own environment and test thoroughly before applying to production systems.