Step 1: Install MySQL Prometheus Exporter

Run the command to install MySQL Prometheus Exporter

apt-get install prometheus-node-exporter

Step 2: Create the MySQL user

Before you can run the MySQL exporter, you first need to create the MySQL user that it will use to fetch database metrics. Log in to your MySQL database server via phpMyAdmin or another SQL tool and run the following commands as a user with administrative privileges.

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'enter_password_here' WITH MAX_USER_CONNECTIONS 3;

As a result, you will see something like this one.

Query OK, 0 rows affected (0.03 sec)

Next, grant this new user the appropriate permissions to fetch database metrics:

GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

Step 3: Test the MySQL exporter

Now that you’ve created the exporter MySQL user, you’re ready to test the exporter.

export DATA_SOURCE_NAME='exporter:enter_password_here@(mysql_hostname:3306)/'

Be sure to replace exporter with the user you created above, enter_password_here with the password you defined, and mysql_hostname with the hostname of the MySQL instance (localhost if you’re on the same machine).

Finally, run the exporter:

prometheus-mysqld-exporter

ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:273 level=info msg="Starting mysqld_exporter" version="(version=0.14.0, branch=debian/sid, revision=0.14.0-3+b5)"
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:274 level=info msg="Build context" (gogo1.19.8,platformlinux/amd64,[email protected],date20230409-10:08:38)=(MISSING)
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=slave_status
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=global_status
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=global_variables
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmp
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=info_schema.innodb_cmpmem
ts=2023-12-29T19:36:36.870Z caller=mysqld_exporter.go:289 level=info msg="Scraper enabled" scraper=info_schema.query_response_time
ts=2023-12-29T19:36:36.871Z caller=tls_config.go:232 level=info msg="Listening on" address=[::]:9104
ts=2023-12-29T19:36:36.871Z caller=tls_config.go:235 level=info msg="TLS is disabled." http2=false address=[::]:9104

If you see the above output, you successfully ran the MySQL Server exporter.

MySQL Exporter publishes MySQL metrics in Prometheus format on port 9104. You can test this using curl. You will need to open a new SSH session or background the MySQL Exporter process to use curl.

curl http://localhost:9104/metrics

# HELP process_resident_memory_bytes Resident memory size in bytes.
# TYPE process_resident_memory_bytes gauge
process_resident_memory_bytes 1.3115392e+07
# HELP process_start_time_seconds Start time of the process since unix epoch in seconds.
# TYPE process_start_time_seconds gauge
process_start_time_seconds 1.70387871201e+09
# HELP process_virtual_memory_bytes Virtual memory size in bytes.
# TYPE process_virtual_memory_bytes gauge
process_virtual_memory_bytes 1.11814656e+09
# HELP process_virtual_memory_max_bytes Maximum amount of virtual memory available in bytes.
# TYPE process_virtual_memory_max_bytes gauge
process_virtual_memory_max_bytes 1.8446744073709552e+19
# HELP promhttp_metric_handler_requests_in_flight Current number of scrapes being served.
# TYPE promhttp_metric_handler_requests_in_flight gauge
promhttp_metric_handler_requests_in_flight 1
# HELP promhttp_metric_handler_requests_total Total number of scrapes by HTTP status code.
# TYPE promhttp_metric_handler_requests_total counter
promhttp_metric_handler_requests_total{code="200"} 0
promhttp_metric_handler_requests_total{code="500"} 0
promhttp_metric_handler_requests_total{code="503"} 0

If you see the above output, you’re ready to begin scraping MySQL metrics using Prometheus.

Step 4: Scraping MySQL Exporter using Prometheus

Now that the MySQL Exporter is up and running on your machine, you can configure a Prometheus scrape job to collect and store MySQL Exporter metrics.

Add the following scrape job config to the scrape_configs section of your prometheus.yml configuration file:

- job_name: mysql
  static_configs:
  - targets: ['mysql_exporter_machine_IP_address:9104']

Step 5: Load recording rules into Prometheus

To load recording rules into Prometheus, add the following to your prometheus.yml configuration file:

rule_files:
  - "mysql_exporter_recording_rules.yml"

Step 6. Start the MySQL exporter

Complete the following steps to start the MySQL exporter using systemd and verify it is running.

To start the service, run the following commands:

systemctl start prometheus-mysqld-exporter
systemctl status prometheus-mysqld-exporter

Step 7. Configure the MySQL exporter to start at boot using systems

To configure the MySQL Exporter to start at boot, run the following command:

systemctl enable prometheus-mysqld-exporter

Step 8. Import a Grafana Dashboard

To import the MySQL Exporter dashboard return to the Grafana dashboard. Click the “Create Dashboard” button and choose “Import a Dashboard”. In the import via grafana.com box, enter the dashboard ID 14057. Then select the “Load” button.

Source:

1. https://grafana.com/oss/prometheus/exporters/mysql-exporter/