\u00a0<\/p>\n<\/p>\n
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.<\/p>\n
sudo<\/code> privileges.<\/li>\n- Prometheus installed on your system.<\/li>\n
- Grafana installed on your system.<\/li>\n<\/ul>\n
Step 1: Install Dependencies<\/h2>\n
Before starting, you need to ensure that the necessary dependencies are installed. For monitoring MySQL\/MariaDB, you will need the mysqld_exporter<\/strong>:<\/p>\nsudo apt install -y prometheus-mysqld-exporter<\/code><\/pre>\nStep 2: Configure mysqld_exporter<\/h2>\n
Create a new user in MySQL\/MariaDB for the exporter:<\/p>\n
mysql -u root -p\r\nCREATE USER 'exporter'@'localhost' IDENTIFIED BY 'your_password';\r\nGRANT SELECT ON *.* TO 'exporter'@'localhost'; -- Grant necessary permissions\r\nFLUSH PRIVILEGES;\r\nEXIT;<\/code><\/pre>\nReplace your_password<\/code> with a secure password.<\/p>\nStep 3: Create mysqld_exporter Configuration<\/h2>\n
Create a configuration file for the mysqld_exporter:<\/p>\n
sudo nano \/etc\/default\/prometheus-mysqld-exporter<\/code><\/pre>\nAdd the following lines to configure the MySQL connection:<\/p>\n
DATA_SOURCE_NAME=\"exporter:your_password@tcp(127.0.0.1:3306)\/\" # Ensure you replace 'your_password'<\/code><\/pre>\nSave and exit the editor.<\/p>\n
Step 4: Start mysqld_exporter<\/h2>\n
Enable and start the mysqld_exporter service:<\/p>\n
sudo systemctl enable prometheus-mysqld-exporter\r\nsudo systemctl start prometheus-mysqld-exporter<\/code><\/pre>\nThe exporter should now be running on port 9104.<\/p>\n
Step 5: Configure Prometheus<\/h2>\n
Next, you need to configure Prometheus to scrape the MySQL\/MariaDB metrics. Open the Prometheus configuration file:<\/p>\n
sudo nano \/etc\/prometheus\/prometheus.yml<\/code><\/pre>\nAdd the following job configuration under the scrape_configs<\/code> section:<\/p>\n- job_name: 'mysql'\r\n static_configs:\r\n - targets: ['localhost:9104']<\/code><\/pre>\nSave and exit the editor. Then, restart Prometheus:<\/p>\n
sudo systemctl restart prometheus<\/code><\/pre>\nStep 6: Configure Grafana<\/h2>\n
Open your Grafana dashboard in a web browser, typically at http:\/\/your_server_ip:3000<\/code>. Log in using the default credentials (username: admin<\/code>, password: admin<\/code>). You will be prompted to change the password.<\/p>\nAdd Prometheus as a data source:<\/p>\n
\n- Click on “Configuration” (gear icon) > “Data Sources”.<\/li>\n
- Click on “Add Data Source” and select “Prometheus”.<\/li>\n
- Set the URL to
http:\/\/localhost:9090<\/code> (or your Prometheus server address).<\/li>\n- Click “Save & Test” to ensure it is working.<\/li>\n<\/ul>\n
Step 7: Create a Dashboard<\/h2>\n
Now you can create a Grafana dashboard to visualize the MySQL metrics:<\/p>\n
\n- Click on the “+” sign on the left sidebar, then select “Dashboard”.<\/li>\n
- Click on “Add a new panel”.<\/li>\n
- Select a query such as
mysql_global_status_threads_connected<\/code> to visualize the number of connected threads.<\/li>\n- Customize the visualization as needed and click “Apply”.<\/li>\n<\/ul>\n
Conclusion<\/h2>\n
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.<\/p>\n
Resources<\/h2>\n