Introduction

Monitoring MySQL or MariaDB using Prometheus and Grafana enables you to visualize database performance metrics in real-time. This guide will walk you through the steps to set up monitoring on a Windows VPS.

Prerequisites

• A Windows VPS with MySQL or MariaDB installed. Consider using a service like NetCloud24.
• Basic familiarity with the command line and Windows services.
• PowerShell or Command Prompt access to install necessary software.

Step 1: Install Prometheus

1. Download the latest version of Prometheus for Windows from the Prometheus downloads page.

curl -LO https://github.com/prometheus/prometheus/releases/latest/download/prometheus-*.zip

2. Extract the downloaded ZIP file to a directory (e.g., C:\prometheus).

3. Navigate to the extracted folder and open the prometheus.yml configuration file in a text editor. Add a job to scrape MySQL metrics:

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']  # Change the target if your MySQL is hosted elsewhere

Step 2: Install MySQL Exporter

MySQL Exporter is necessary to expose MySQL metrics to Prometheus:

1. Download the MySQL Exporter from the Prometheus MySQL Exporter GitHub page.

curl -LO https://github.com/prometheus/mysqld_exporter/releases/latest/download/mysqld_exporter-*.zip

2. Extract the contents to a folder (e.g., C:\mysqld_exporter).

3. Create a configuration file for the exporter. Create a text file named mysql.cnf with the following content:

[mysql]
user=
password=

Replace <your_user> and <your_password> with your database credentials.

Step 3: Run MySQL Exporter

1. Open a PowerShell or Command Prompt window. Navigate to the MySQL Exporter directory:

cd C:\mysqld_exporter

2. Run the exporter with:

mysqld_exporter.exe --config.my-cnf=mysql.cnf

You should see the output indicating that the exporter is running and listening for requests.

Step 4: Install Grafana

1. Download the latest version of Grafana from the Grafana download page.

2. Install Grafana by running the installer and following the prompts.

3. Once installed, start the Grafana service. You can do this from the Windows Services management console or by using PowerShell:

Start-Service grafana

Step 5: Configure Grafana

1. Open your web browser and access Grafana at http://localhost:3000.

2. Log in with the default credentials:

• Username: admin
• Password: admin

3. You will be prompted to change the password. Follow the prompts to create a new password.

4. Add Prometheus as a data source:

• Click on the gear icon (??) in the left sidebar and select Data Sources.
• Click on Add data source.
• Select Prometheus.
• In the URL field, enter http://localhost:9090.
• Click Save & Test to verify the connection.

Step 6: Create a Dashboard

1. In Grafana, click on the plus icon (?) in the sidebar and select Dashboard.

2. Click on Add new panel to create a new panel.

3. In the query editor, choose prometheus as the data source and use queries like:

mysql_global_status_connections

4. Configure your panel settings and save your dashboard.

Conclusion

You have successfully set up monitoring for MySQL or MariaDB using Prometheus and Grafana on your Windows VPS! You can now visualize your database performance metrics in Grafana.

Resources

• Prometheus Documentation
• Grafana Documentation
• MySQL Exporter GitHub Page