How to connect Grafana to a remote MySQL database

Grafana needs data to be useful. Jack Wallen walks you through the process of using a MySQL database as a data visualization source in Grafana.

Image: Grafana

Grafana is one of the most widely used interactive data visualization tools on the market. It is open-source, powerful, highly configurable, and free to use. I walked you through the process of installing Grafana on Ubuntu Server 20.04, and this time I’m going to help you connect this newly installed platform to a MySQL database, so you can view these data.

Grafana allows you to connect to many data sources (Google Sheets, Amazon Timestream, Elasticsearch and many databases). Because MySQL is such a popular database, I thought this would be a great data source to use as an illustration. It will also refresh your memory on MySQL configuration for remote connections.

SEE: 40+ open source and Linux terms you need to know (TechRepublic Premium)

What you will need

For this to work you will need a running instance of Grafana, a running instance of MySQL, and a user with sudo privileges. I’m going to assume that your instance of MySQL is on a remote Linux server.

How to configure MySQL for a remote connection

The first thing we need to do is configure the MySQL server to allow remote connections. In addition to this, we will create a specific user who has specific permissions for the database that we will display on Grafana.

First, connect to your MySQL server and open the MySQL configuration file with:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In this file, find the line:

bind-address = 127.0.0.1

Change that to:

bind-address = 0.0.0.0

Save and close the file. Restart the MySQL service with:

sudo systemctl restart mysql

Next, we need to create a new user and give it the appropriate permissions. Log in to the MySQL console with:

sudo mysql -u root -p

Make sure you know which database you are going to use. You can list them with:

show databases;

Create the new user with:

CREATE USER 'grafanareader' IDENTIFIED BY 'PWORD';

Where PWORD is a strong/unique password for the new user.

Now we can grant this new user the SELECT permission for the database with:

GRANT SELECT ON DB.* TO 'grafanareader';

Where DB is the name of the database to be read by Grafana.

Dump privileges and exit the MySQL console with:

flush privileges;
exit

Your database server is now ready.

How to connect Grafana to MySQL

Log in to your Grafana instance and click the gear icon in the left sidebar. In the resulting pop-up window, click Data Sources. In the next window, scroll down and select MySQL from the list. You will then be presented with the necessary configuration options for a MySQL data connection (Figure A).

Figure A

The MySQL Grafana configuration editor.

Here is what you need to fill out:

  • Host—the IP address or domain name of the MySQL hosting server and the port used for the database server (default is 3306).
  • Database—the database to use as the source.
  • User—grafanareader
  • Password—PWORD used to create grafanareader in MySQL console.

You may also need to enable Bypass TLS verification.

Configure these options and click Save and Test. You should eventually see Database connection OK (Figure B).

Figure B

Our MySQL connection is ready to go.

Congratulations, you now have Grafana connected to a remote MySQL server. In our next article in the series, we’ll create a new dashboard to display some of the source data.

Subscribe to TechRepublic How to make technology work on YouTube for all the latest tech tips for professionals from Jack Wallen.

Maria H. Underwood