Monitoring MySQL with Prometheus and Grafana in Docker

If you want to test Prometheus for monitoring a MYSQL server and its integration with Grafana, through this blog post you will learn how to run it in a local environment using Docker containers as it could be useful to get familiar with this tool before using it in a production environment.

Running MySQL using Docker

First, create a network:

$ docker network create prom-network

Run the following command to get MySQL up and running in a container:

$ docker run -d -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root --network prom-network mysql:8.3.0

Running MySQL Server Exporter with Docker

From the documentation of Prometheus, there are a number of libraries and servers which help in exporting existing metrics from third-party systems as Prometheus metrics.

Some of these exporters are maintained as part of the official Prometheus GitHub organization, those are marked as official, others are externally contributed and maintained.

For MySQL, you can use the official MySQL Server Exporter that supports:

• MySQL >= 5.6.
• MariaDB >= 10.3

To install it you can follow the instructions in the GitHub repository and you can run it in a Docker container as well.

First, grant permissions to the exporter to access the database. Login to your MySQL server and run the following queries:

$ mysql -u root -p -h 127.0.0.1

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

Then, create the configuration file (config.my-cnf), adding login details as follows:

[client]
user = exporter
password = password
host = mysql

And finally, initialize the container:

$ docker run -p 9104:9104 --name exporter --network prom-network -v $(pwd)/config.my-cnf:/cfg/config.my-cnf prom/mysqld-exporter:main --config.my-cnf=/cfg/config.my-cnf

Running Prometheus in a container

After configuring the exporter, run Prometheus in a container, but first, create the configuration file (prometheus.yml). You can start with the basic configuration example from the documentation and add the configuration needed for the exporter as described in the GitHub repository.

global:
  scrape_interval:     15s # By default, scrape targets every 15 seconds.

  # Attach these labels to any time series or alerts when communicating with
  # external systems (federation, remote storage, Alertmanager).
  external_labels:
    monitor: 'codelab-monitor'

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'

    # Override the global default and scrape targets from this job every 5 seconds.
    scrape_interval: 5s

    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mysql'

    params:
      auth_module: [client]

    scrape_interval: 5s

    static_configs:
      - targets: ['mysql:3306']

    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: exporter:9104

Now run the container:

$ docker run -p 9090:9090 --network prom-network --name prometheus -v ./prometheus.yml:/etc/prometheus/prometheus.yml prom/prometheus

Once Prometheus is running, you can go to the browser and visit http://localhost:9090 or if you go to http://localhost:9090/targets, you can see the exporter listed there.

Run Grafana with Docker

To run Grafana with Docker, just type the following command:

$ docker run -d --name=grafana --network prom-network -p 3000:3000 grafana/grafana-enterprise

Go to localhost:3000 in the browser. Login with default credentials, admin as both user and password.

And change default password.

From the menu, go to Data sources in the Connections section or go directly to http://localhost:3000/connections/datasources.

Click on Add data source, click on Prometheus, type http://prometheus:9090 as the Prometheus server URL, and click on Save & test.

Now go to Dashboards from the menu or go directly to http://localhost:3000/dashboards.

Click on Create Dashboard, click on Import dashboard, type 14057, click on Load, and click on Import.

If everything went well, you'll see the following screen.

Conclusion

After all the previous steps, you're running a MySQL server that it is being monitored with Prometheus and Grafana. Through this blog post you learn how to run a MySQL server, Prometheus and Grafana on your local environment using Docker.