How to Export Data From MySQL to Prometheus in 7 Steps

July 26, 2023

MySQL/MariaDB is a widely used relational database management system (RDBMS) that is designed for storing, retrieving, and managing structured data. It is an excellent choice for data-intensive applications that require a robust and scalable database.

On the other hand, Prometheus is a monitoring system that is designed to collect and store time-series data. It is optimized for monitoring and alerting and is particularly suited for systems with a high degree of dynamic service discovery and instrumentation.

While MySQL/MariaDB is designed for data storage and retrieval, Prometheus is designed for monitoring and analysis of time-series data. They serve different purposes, and their features and capabilities are optimized accordingly. However, both tools can complement each other in data-intensive applications that require both data storage and monitoring capabilities.

Table of Contents

Migrate Data from MySQL to Prometheus – Step By Step

Prometheus MySQL Exporter is a Prometheus exporter for MySQL server metrics. It makes various MySQL server metrics available for scraping so you can monitor and alert on them. Here’s a comprehensive guide on how to use it.

Step 1: Install MySQL Exporter

The first step is to download and install the MySQL Exporter. Visit the MySQL Exporter repository (https://github.com/prometheus/mysqld_exporter) and download the latest release.

Alternatively, you can use wget to download it directly:

wget https://github.com/prometheus/mysqld_exporter/releases/download/v*/mysqld_exporter-*.linux-amd64.tar.gz

Then, unpack the tarball using tar:

tar xvfz mysqld_exporter-*.linux-amd64.tar.gz

Step 2: Create MySQL User for Exporter

Create a new MySQL user for the exporter. This user is used by the exporter to connect to your MySQL server and get the metrics.

mysql -uroot -p -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 2;"
mysql -uroot -p -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"
mysql -uroot -p -e "FLUSH PRIVILEGES;"

Replace ‘password’ with a strong password.

Step 3: Configure MySQL Exporter

You need to tell the MySQL exporter which MySQL instance it should connect to. This is done through a .my.cnf configuration file:

cat <<'EOF' >> ~/.my.cnf
[client]
user=exporter
password=password
EOF

Again, replace ‘password’ with the password you set earlier.

Step 4: Run the MySQL Exporter

Run the exporter:

./mysqld_exporter

This will start the exporter, and it will start to scrape metrics from your MySQL server.

Step 5: Configure Prometheus to Scrape the MySQL Exporter

Finally, you need to tell Prometheus to scrape the MySQL exporter. Add the following to your prometheus.yml file:

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['<mysql_exporter_host>:9104']

Replace <mysql_exporter_host> with the host name or IP address where the MySQL Exporter is running.

Step 6: Restart Prometheus

To apply the changes, restart Prometheus:

systemctl restart prometheus

Once you’ve done this, Prometheus will start to scrape metrics from the MySQL exporter every 60 seconds (or whatever interval you’ve set). You can view these metrics through the Prometheus web interface.

Step 7: Validate the Setup

To validate the setup, you can go to your Prometheus dashboard, typically accessible at http://<prometheus_server>:9090, then go to the “Graph” tab and try to display some MySQL metrics like mysql_global_variables_innodb_buffer_pool_bytes_total, you should see some data if everything is correctly set.

After you have imported the data into Prometheus, you should verify that it is present in Prometheus. You can verify the data by querying Prometheus using PromQL. The query should return the data you imported.

That’s it! You have successfully migrated data from MySQL to Prometheus. You can now use Prometheus to monitor and analyze your data.