Introduction

Prometheus is a powerful monitoring system and time series database, and Grafana is a popular analytics and monitoring solution. This guide will show you how to set up monitoring for MySQL or MariaDB databases using these two tools.

Prerequisites

• A server with MySQL or MariaDB installed.
• Root access or a user with sudo privileges.
• Prometheus installed on your system.
• Grafana installed on your system.

Step 1: Install Dependencies

Before starting, you need to ensure that the necessary dependencies are installed. For monitoring MySQL/MariaDB, you will need the mysqld_exporter:

sudo apt install -y prometheus-mysqld-exporter

Step 2: Configure mysqld_exporter

Create a new user in MySQL/MariaDB for the exporter:

mysql -u root -p
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'your_password';
GRANT SELECT ON *.* TO 'exporter'@'localhost'; -- Grant necessary permissions
FLUSH PRIVILEGES;
EXIT;

Replace your_password with a secure password.

Step 3: Create mysqld_exporter Configuration

Create a configuration file for the mysqld_exporter:

sudo nano /etc/default/prometheus-mysqld-exporter

Add the following lines to configure the MySQL connection:

DATA_SOURCE_NAME="exporter:your_password@tcp(127.0.0.1:3306)/" # Ensure you replace 'your_password'

Save and exit the editor.

Step 4: Start mysqld_exporter

Enable and start the mysqld_exporter service:

sudo systemctl enable prometheus-mysqld-exporter
sudo systemctl start prometheus-mysqld-exporter

The exporter should now be running on port 9104.

Step 5: Configure Prometheus

Next, you need to configure Prometheus to scrape the MySQL/MariaDB metrics. Open the Prometheus configuration file:

sudo nano /etc/prometheus/prometheus.yml

Add the following job configuration under the scrape_configs section:

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

Save and exit the editor. Then, restart Prometheus:

sudo systemctl restart prometheus

Step 6: Configure Grafana

Open your Grafana dashboard in a web browser, typically at http://your_server_ip:3000. Log in using the default credentials (username: admin, password: admin). You will be prompted to change the password.

Add Prometheus as a data source:

• Click on “Configuration” (gear icon) > “Data Sources”.
• Click on “Add Data Source” and select “Prometheus”.
• Set the URL to http://localhost:9090 (or your Prometheus server address).
• Click “Save & Test” to ensure it is working.

Step 7: Create a Dashboard

Now you can create a Grafana dashboard to visualize the MySQL metrics:

• Click on the “+” sign on the left sidebar, then select “Dashboard”.
• Click on “Add a new panel”.
• Select a query such as mysql_global_status_threads_connected to visualize the number of connected threads.
• Customize the visualization as needed and click “Apply”.

Conclusion

You have successfully set up monitoring for MySQL or MariaDB using Prometheus and Grafana. You can now visualize and monitor your database performance in real-time.

Resources

• Prometheus Official Website
• Grafana Official Website
• mysqld_exporter GitHub Repository