Can't Connect Grafana To MySQL

Grafana MySQL
1

I hope someone is able to help because I’m just about ready to give up.

What I’m Trying to Achieve

I’m trying to create a MySQL datasource in Grafana and connect it to a MySQL database.

Method

  • I have Grafana 10.3 installed on a Debian 11 server (178.x.x.x)
  • I have MySQL installed on a different server running Ubuntu 20.04 (213.x.x.x)
  • I’m trying to configure the connection from the Grafana web UI using the MySQL datasource plugin
  • I’m using basic password authentication

Below is my config.

image
image1920×1762 135 KB

Problem

The connection fails with the following error:

image
image759×175 7.27 KB

Troubleshooting Steps

  • I’ve checked the grafana log and it says the the MySQL server refused the connection
  • I’ve ensured that ports 3000 and 3306 are open in the firewall
  • I’ve created a grafana user on the database with SELECT permissions only
  • I’ve set the binding address to 0.0.0.0 in the mysql.conf file

Please Note

  • This isn’t a firewall issue as the MySQL server accepts incoming connections from WorkBench, Skyvia, and DBeaver without any problems at all

I’d be extremely grateful for any help you’re able to offer as this issue is driving me to insanity. Is there something blatantly obvious that I’m missing?

Thanks in advance.

2

Hi @riley_qcs and welcome to the Grafana forum. I feel your pain, as often when I first set up a new datasource, it’s usually something stupid like a missing colon or an extra space or whatever that prevents it from successfully connecting.

In your case, I do not have access to a working MySQL remote database (maybe this weekend?) but for now, did you go thru this article? (probably did, but figured I would ask)

1 Like
3

Hi @grant2,

Thanks for your reply.

Yes, I followed that article step-by-step. The only thing I did differently was to use a different username for the database user, which is an optional step anyway, so it shouldn’t make a difference.

4

This is my go to article for remote access

  1. Must restart mysql service when changing cong file

  2. You might need to specify the user as a remote user from a specific ip address, the grafana ip address and grant it all the db perms

CREATE USER 'sammy'@'grafana_server_ip' IDENTIFIED BY 'password';

Do you have phpadmin type of tool that will visually show you the perns of your grafana user. It might be configured as local and not from the grafana host ip

1 Like
5

Hey @yosiasz, thanks for your reply.

Yes, I’m using DBeaver which is working quite well.

I’ve created a user, specifying the IP address of my Grafana server as shown below:

image
image656×557 21.5 KB

Even after this change, I’m still getting the same connection error.

6

You don’t need the ip address when creating th3 connection in grafana

7

Did you do this bit in mysql

1 Like
8

Yes, I did that.

1 Like
9

I’ve tried it with and without. It doesn’t connect either way.

1 Like
10

Using a visual tool such mysql workbench or other tool, can you visually show us using a screen grab the permissions you setup for this user on that zoho database?

11

I’m using DBeaver to create users and set privileges. The only privilege I’ve set for the grafana user is SELECT on the zoho_invoice table.

image
image1341×898 46.9 KB

1 Like
12

It seems all looks fine. Not sure what the issue is :man_shrugging:t5:

And can you show us how the user was setup in that screen minus ip

Also you did restart the mysql service for the bind address change?

Are they both in same ip network?

Is tls enabled on mysql

13

Hi,

Did you restart the mysql service after changing the bind address?

sudo systemctl restart mysql # for ubuntu
sudo systemctl restart mysqld.service # for debian

Did you create a new user or grant permissions to an existing user to connect from the remote IP?

GRANT ALL PRIVILEGES ON yourDB.* TO user1@'133.155.44.103' IDENTIFIED BY 'password1';

or

update db set Host='133.155.44.103' where Db='yourDB';
update user set Host='133.155.44.103' where user='user1';

Did you flush your privileges after granting?

FLUSH PRIVILEGES;
1 Like
14

Yes. I’ve restarted the service and rebooted the server multiple times.

I created a new user.

Yes, multiple times.

1 Like
15

Yes. I’ve restrated the MySQL service and rebooted the server multiple times.

No, they’re on different networks.

  • Grafana server (178.x.x.x)
  • MySQL server (213.x.x.x)
16

Can you ping ip of mysql from grafana?

1 Like
17

Did you GRANT the privileges to the DB user to be able to connect to Grafana?

Also, don’t forget to run FLUSH PRIVILEGES at the last, it always bugged me multiple times in the past

Also make sure of the firewall rules to allow connections

18

Yes, the ping is successful.

19

Yes, I did that.

20

Could you please provide us with the backend logs (the default location in Linux is /var/log/grafana/grafana.log) so that we can have more information about the error message?