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.