Can't Connect Grafana To MySQL

riley_qcs · February 14, 2024, 5:43am

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.

Problem

The connection fails with the following error:

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.

grant2 · February 14, 2024, 11:02am

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)

riley_qcs · February 14, 2024, 11:17am

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.

yosiasz · February 14, 2024, 1:05pm

This is my go to article for remote access

Must restart mysql service when changing cong file
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

riley_qcs · February 14, 2024, 1:37pm

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:

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

yosiasz · February 14, 2024, 1:40pm

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

yosiasz · February 14, 2024, 1:41pm

Did you do this bit in mysql

riley_qcs · February 14, 2024, 1:49pm

Yes, I did that.

riley_qcs · February 14, 2024, 1:50pm

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

yosiasz · February 14, 2024, 1:59pm

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?

riley_qcs · February 14, 2024, 2:08pm

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.

yosiasz · February 14, 2024, 3:01pm

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

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

sowdenraymond · February 15, 2024, 6:59am

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;

riley_qcs · February 15, 2024, 8:45am

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

I created a new user.

Yes, multiple times.

riley_qcs · February 16, 2024, 6:13am

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)

yosiasz · February 16, 2024, 1:33pm

Can you ping ip of mysql from grafana?

usman.ahmad · February 16, 2024, 1:38pm

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

riley_qcs · February 16, 2024, 9:03pm

Yes, the ping is successful.

riley_qcs · February 16, 2024, 9:04pm

Yes, I did that.

usman.ahmad · February 16, 2024, 9:28pm

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?