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 source for data visualization in Grafana.

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

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

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

What you’ll need

To make this work, you’re going to need a running instance of Grafana, a running instance of MySQL and a user with sudo privileges. I am going to assume your instance of MySQL is on a remote Linux server.

How to configure MySQL for remote connection

The first thing we must do is configure the MySQL server to allow remote connections. On top of that, we’re going to create a specific user that has specific permissions for the database we’ll view on Grafana.

First, log into your MySQL server and open the MySQL configuration file with:

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

In that file, look for 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 correct permissions. Log in to the MySQL console with:

sudo mysql -u root -p

Make sure you know which database you’re going to be using. You can list them out 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 that 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.

Flush the privileges and exit from the MySQL console with:

flush privileges;
exit

Your database server is now ready.

How to connect Grafana to MySQL

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

Figure A

Here’s what you must fill out:

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

You might also have to enable Skip TLS Verify.

Configure those options and click Save & Test. You should eventually see Database Connection OK (Figure B).

Figure B

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

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the latest tech advice for business pros from Jack Wallen.