Cannot Connect to MySQL on Linux VM

[jellek]

Hi there,

I have a Virtual Machine running a LAMP server with a bridged connection to the host computer (Win10).

After installing Mmysql-connector-net-6.6.5.msi I still cannot connect to my database; utilities. 
I can access the database through phpMyAdmin, 192.168.0.13 on host computer.
If I check in phpMyAdmin, the port is 3306, I also tried Ubuntu which is listed as the Hostname in phpMyAdmin.

In Power BI i get the following error:
"Details: "MySQL: Unable to connect to any of the specified MySQL hosts.""

Why can I not connect to my Database? what am I doing wrong? Is this a firewall issue?

I'm still new to virtual machines and LAMP, so if you could point me in the right direction then that would be great.

Thanks in advance.
Jelle

[dlash]

Does this work? The mysql credentals page does not even support a port number? 

Are you sure you can connect to a remote mysql DB running off a linux server? It doesn't look supported. 

[Antarian]

5 years later and I can connect with any tool to MySQL from inside WSL2 and from the Windows host. But PowerBI Desktop has no proper documentation, no settings, not anything that can help you to connect to DB or check what is not working. It will just not connect to MySQL DB.
You can paste whatever connection string there, it will not tell you anything useful just:
Details: "MySQL: Unable to connect to any of the specified MySQL hosts."

[Antarian]

Edit: After finally discovering how to save and read the debug logs, issue was unability to read/reach DSN. I have tried altering host file to no avail. I have tried to put there "https://www.google.com" and even that was not recognized as domain. Then I tried to remove https:// from "https://localhost" to just "localhost" and it finally found the domain.

[v-qiuyu-msft · Accepted solution]

Hi @jellek,

In your scenario, I would suggest you check if you can use MySQL workbench to the database successfully. Make sure that MySQL workbench is installed  on the same machine with Power BI Desktop. If the connection fails, please follow the instructions in this blog to troubleshoot this issue.

If you are able to connect to database using MySQL workbench, please use same connection parameters in Power BI Desktop, and also add port number with server name to connect to MySQL.

Also ensure that you install MySQL Connector/Net 6.6.5 for Microsoft Windows on your computer. Select the driver that matches your Power BI Desktop installation (32-bit or 64-bit).

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-msft v-qiuyu-msft,

I have MySQL DB installed in a Linux Server. I am successfully able to connect to the linux server MySQL DB from MySQL Workbench (connection method = standard tcp/ip over ssh) in my local machine. Gave Server host name, user, pwd, mysql db host, port, user and pwd.

However I am not able to do the same from Power BI in my local machine. I see only the following (as in screen shot) taken as inputs.

I referred this link - https://towardsdatascience.com/connecting-to-aws-remote-mysql-server-via-an-ssh-tunnel-6669a84cee91 and it seems to say Power BI SSH tunnel is not possible directly and they suggest to use putty. However I also read that with Putty there is a danger frequent connection drops impacting Power BI reports.

Can you please help resolve this and let me know if there are any other settings I should consider/change in both my linux server and local windows machine to establish a successful connection?

[psdhillon1]

Hey @Anonymous I am also in the same boat today where you were a couple of months back. I am working on connecting MySQL database over ssh. I have got bastion SSH IP address, user name, password besides MySQL hostname, user name, password & pem/ppk file. 
I have been able to connect through MySQL workbench, but when I try using powerbi, I am also getting same error, ""Details: "MySQL: Unable to connect to any of the specified MySQL hosts.""

BAsed on your experience, can you help me with some approaches which u used to solve the problem. Thanks in advance

[jellek]

Thanks a lot for your help Qiuyun Yu, you pointed me in the right direction.

There were a few things I had to do on both my host and in guest MySQL database:

1) I opened the correct port number on my windows host.

2) GRANT ALL ON *.* to homedbuser@localhost IDENTIFIED BY 'password'; for my user in MySQL

3) in mysqld.cnf file i changed the bind-address = 0.0.0.0

After all these changes I tested the connection and it worked.

Time to visulize my data.