How to Set Up MySQL Monitoring on a Debian 11 VDS

Monitoring your MySQL database is essential for ensuring the stable and efficient operation of your application. Setting up monitoring allows you to quickly identify problems, prevent failures, and optimize performance. In this article, we will take a detailed look at how to set up MySQL monitoring on a Debian 11 VDS using popular tools and techniques.

In this article, we will cover the installation and configuration of Grafana, Prometheus, and MySQL Exporter for effective MySQL monitoring on your Debian 11 server. You will learn how to collect key metrics, visualize data, and set up alerts for timely response to potential issues.

  1. Installing and Configuring Prometheus
  2. Configuring MySQL Exporter
  3. Configuring Grafana
  4. Creating a Grafana Dashboard for MySQL

Installing and Configuring Prometheus

Prometheus is a powerful open-source monitoring and alerting system. It collects metrics from configured sources, stores them as time-series data, and provides an interface for queries and visualization. In this section, we will walk through the installation and basic configuration of Prometheus on Debian 11.

Saber Más

Step 1: Installing Prometheus

First, you need to download and install Prometheus. It’s best to download the latest stable version from the official Prometheus Downloads page. The process can be simplified by using wget and unpacking the archive:

wget https://github.com/prometheus/prometheus/releases/download/v2.48.0/prometheus-2.48.0.linux-amd64.tar.gz
tar xvfz prometheus-2.48.0.linux-amd64.tar.gz
cd prometheus-2.48.0.linux-amd64

Make sure to replace v2.48.0 with the latest available version. Create the necessary directories and move the Prometheus executables to /usr/local/bin:

sudo mkdir /etc/prometheus
sudo mkdir /var/lib/prometheus
sudo cp prometheus prometheus-2.48.0.linux-amd64/promtool /usr/local/bin/
sudo cp -r prometheus-2.48.0.linux-amd64/consoles prometheus-2.48.0.linux-amd64/console_libraries /etc/prometheus/
sudo cp prometheus-2.48.0.linux-amd64/prometheus.yml /etc/prometheus/
sudo chown -R prometheus:prometheus /etc/prometheus
sudo chown -R prometheus:prometheus /var/lib/prometheus

Step 2: Creating a Prometheus User and Group

For security purposes, it is recommended to run Prometheus under a separate user. Create the prometheus user and group:

sudo groupadd --system prometheus
sudo useradd -s /sbin/nologin --system -g prometheus prometheus

Step 3: Creating a systemd service file

To automatically start Prometheus after a server reboot, create the file /etc/systemd/system/prometheus.service with the following content:

[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus \
    --config.file=/etc/prometheus/prometheus.yml \
    --storage.tsdb.path=/var/lib/prometheus/ \
    --web.console.templates=/etc/prometheus/consoles \
    --web.console.libraries=/etc/prometheus/console_libraries

[Install]
WantedBy=multi-user.target

Save the file and reload systemd:

sudo systemctl daemon-reload

Start and enable Prometheus for automatic startup on boot:

sudo systemctl start prometheus
sudo systemctl enable prometheus

Step 4: Checking Prometheus Operation

Make sure Prometheus is running successfully by checking its status:

sudo systemctl status prometheus

If everything is fine, you should see the active (running) status. Prometheus should now be accessible via the web interface at http://your_ip_address:9090.

Step 5: Configuring Prometheus.yml for MySQL Exporter

To collect MySQL metrics, you need to add a configuration for MySQL Exporter to the /etc/prometheus/prometheus.yml file. Open the file for editing and add the following block to the scrape_configs section:

scrape_configs:


  • job_name: 'prometheus'


    static_configs:


  • targets: ['localhost:9090']
  • job_name: 'mysql'


    static_configs:


  • targets: ['localhost:9104'] # Port on which MySQL Exporter is running

In this example, we added a mysql job that will poll the MySQL Exporter running on localhost:9104. After changing the configuration, you need to restart Prometheus:

sudo systemctl restart prometheus

Example Query in Prometheus

To check whether Prometheus is successfully collecting metrics, you can use the Prometheus web interface. Enter a query, such as up, to check whether Prometheus and MySQL Exporter are available.

Expert Tip: Regularly update Prometheus to the latest version to get new features and security fixes.

Configuring MySQL Exporter

MySQL Exporter is a tool that collects various metrics from the MySQL server and provides them in a format that Prometheus understands. In this section, we will look at installing, configuring, and running MySQL Exporter.

Step 1: Creating a Monitoring User in MySQL

To collect metrics, MySQL Exporter needs a user with specific privileges. Connect to the MySQL server with administrator privileges and create a user:

mysql -u root -p

Inside the MySQL client, run the following commands:

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'your_password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Replace your_password with a strong password. This user will be used by MySQL Exporter to collect metrics.

Step 2: Downloading MySQL Exporter

Go to the MySQL Exporter Releases page on GitHub and download the latest version that matches your architecture. For example:

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.16.0/mysqld_exporter-0.16.0.linux-amd64.tar.gz
tar xvfz mysqld_exporter-0.16.0.linux-amd64.tar.gz
cd mysqld_exporter-0.16.0.linux-amd64

Replace v0.16.0 with the current version.

Step 3: Configuring MySQL Exporter

MySQL Exporter requires specifying credentials for connecting to the MySQL server. This can be done via environment variables or a configuration file. For simplicity, let’s configure it via environment variables.

export DATA_SOURCE_NAME='exporter:your_password@tcp(localhost:3306)/'

Replace your_password with the password you specified when creating the user. localhost:3306 is the address of your MySQL server.

Step 4: Running MySQL Exporter

Run MySQL Exporter:

./mysqld_exporter

By default, MySQL Exporter will listen on port 9104. You can check that it is working by opening http://your_ip_address:9104/metrics in a browser. You should see a large list of metrics in Prometheus format.

Step 5: Creating a systemd service file for MySQL Exporter

As with Prometheus, let’s create a systemd service file to automatically start MySQL Exporter:

sudo nano /etc/systemd/system/mysqld_exporter.service

Add the following content:

[Unit]
Description=MySQL Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/path/to/mysqld_exporter/mysqld_exporter \
  --web.listen-address=:9104 \
  --collect.global_status \
  --collect.info_schema.innodb_metrics

Environment="DATA_SOURCE_NAME=exporter:your_password@tcp(localhost:3306)/"

[Install]
WantedBy=multi-user.target

Replace /path/to/mysqld_exporter/ with the actual path to the mysqld_exporter executable and your_password with your password. Make sure you specify the correct options, such as --collect.global_status and --collect.info_schema.innodb_metrics, to collect important InnoDB metrics.

Save the file and reload systemd:

sudo systemctl daemon-reload

Start and enable MySQL Exporter:

sudo systemctl start mysqld_exporter
sudo systemctl enable mysqld_exporter

Check the status:

sudo systemctl status mysqld_exporter

Prometheus should now be collecting metrics from MySQL Exporter.

Security Recommendations

Always use strong passwords for the monitoring user. Limit access to port 9104 using a firewall so that only Prometheus can connect to MySQL Exporter.

Configuring Grafana

Grafana is a popular platform for data visualization and dashboard creation. It allows you to create interactive graphs using data from various sources, including Prometheus. In this section, we will look at installing and configuring Grafana, as well as adding Prometheus as a data source.

Step 1: Installing Grafana

Add the Grafana repository to your Debian 11:

echo "deb https://apt.grafana.com stable main" | sudo tee /etc/apt/sources.list.d/grafana.list

Import the GPG key:

sudo apt-get update
sudo apt-get install -y apt-transport-https
sudo apt-get install -y software-properties-common wget
wget -q -O - https://apt.grafana.com/gpg.key | sudo apt-key add -

Install Grafana:

sudo apt-get update
sudo apt-get install grafana

Step 2: Starting and Enabling Grafana

Start Grafana:

sudo systemctl start grafana-server

Enable Grafana for automatic startup on boot:

sudo systemctl enable grafana-server

Check the status:

sudo systemctl status grafana-server

Grafana will be available by default on port 3000. Open http://your_ip_address:3000 in your browser.

Step 3: Adding Prometheus as a Data Source in Grafana

Log in to Grafana using the default credentials (admin/admin). You will be prompted to change the password. After logging in, go to «Configuration» -> «Data Sources».

Click «Add data source» and select «Prometheus».

In the «Name» field, enter a name for the data source, for example, «Prometheus». In the «URL» field, enter the address of your Prometheus server: http://your_ip_address:9090. Leave the other settings at their defaults.

Click «Save & Test». If everything is configured correctly, you will see the message «Data source is working».

Grafana can now retrieve data from Prometheus. You can create dashboards and visualize metrics collected by Prometheus and MySQL Exporter.

Configuring Authentication in Grafana

To improve security, it is recommended to configure authentication in Grafana. You can use Grafana’s built-in authentication system or integrate Grafana with other authentication systems such as LDAP or OAuth.

Example: Configuring Authentication via LDAP

Edit the /etc/grafana/grafana.ini file and find the [auth.ldap] section. Uncomment and configure the parameters that match your LDAP configuration. Example:

[auth.ldap]
enabled = true
config_file = /etc/grafana/ldap.toml

Create the /etc/grafana/ldap.toml file and add the LDAP configuration:

[[servers]]
host = "ldap.example.com"
port = 389
use_ssl = false
bind_dn = "cn=admin,dc=example,dc=com"
bind_password = "your_ldap_password"
search_base_dns = ["dc=example,dc=com"]

  [servers.attributes]
    name = "givenName"
    surname = "sn"
    member_of = "memberOf"
    email =  "mail"
    login_attribute = "uid"

Replace the values with your actual LDAP values. Restart Grafana after making changes.

Creating a Grafana Dashboard for MySQL

Once Grafana is configured and Prometheus is added as a data source, you can create a dashboard to visualize MySQL metrics. In this section, we will look at creating a basic dashboard with several useful graphs.

Step 1: Creating a New Dashboard

In Grafana, go to «Dashboards» and click «New» -> «New dashboard».

Step 2: Adding a Panel with a Graph

Click «Add new panel». Select the «Prometheus» data source. Now you need to enter a Prometheus query to display the metrics.

Example 1: CPU Usage

To display the CPU usage of the MySQL server, you can use the query:

rate(process_cpu_seconds_total{job="mysql"}[5m])

This query calculates the average CPU usage rate over the last 5 minutes. Set the panel title to «CPU Usage» and configure the graph display (for example, select the «Time series» graph type).

Example 2: Number of Active Connections

To display the number of active connections to the MySQL server, use the query:

mysql_global_status_threads_connected

Set the panel title to «Active Connections».

Example 3: QPS (Queries Per Second)

To display the number of queries per second:

rate(mysql_global_status_queries[5m])

Set the panel title to «QPS».

Example 4: InnoDB Buffer Pool Usage

To monitor InnoDB buffer pool usage:

(innodb_buffer_pool_pages_total / innodb_buffer_pool_pages_free) * 100

Set the panel title to «InnoDB Buffer Pool Usage (%)».

Step 3: Configuring Display and Saving the Dashboard

Configure the display of graphs (colors, units of measurement, axes) according to your needs. Save the dashboard, specifying a name, for example, «MySQL Monitoring».

You now have a Grafana dashboard that displays the main metrics of your MySQL server. You can add new panels, configure existing ones, and create alerts to be notified of problems.

Comparison of MySQL monitoring tools:

ToolAdvantagesDisadvantages
Prometheus + GrafanaFlexibility, scalability, powerful visualization capabilities.Requires configuration and understanding of how it works.
MySQL Enterprise MonitorSimple installation and configuration, ready-made dashboards.Paid.
phpMyAdminConvenient web interface for managing MySQL.Limited monitoring capabilities.

Expert quote: «Monitoring is not just about collecting data, but understanding what that data means and how it affects the performance of your application.» — John Doe, Database Administrator.