1
 MySqlConnection con = new MySqlConnection("Server = 'ip_adress'; Database = 'db'; Uid = 'root'; Pwd = 'test'; SslMode = none");

Hello! I am trying to connect to my mysql database using my ip instead of localhost. Using localhost everything is fine but it can't connect when i am using the ip. I followed most if not every thread on the subject but i still can not fix it. I am able to connect to phpmyadmin using the ipadress, i granted full privileges to root, set host to %, port forwarded 3306 and 80. I appreciate every bit of help. Thank you

A. Dravid
  • 43
  • 1
  • 4
  • The port forward should be `3306` and not `3606` – James Wong Jun 26 '18 at 13:01
  • mispelled. i made it to 3306 – A. Dravid Jun 26 '18 at 13:02
  • I assume you flushed the SQL privileges and restarted your router? – James Wong Jun 26 '18 at 13:20
  • i did that.. but i dont think its a problem with the users. it just can't connect to the ip, if the user would be the problem i guess i would get another error. – A. Dravid Jun 26 '18 at 13:22
  • is this C# or something? What exact error do you get? – ADyson Jun 26 '18 at 13:26
  • Your app really should not log in as root if you can help it, make an account specifically for it which has only the privileges it actually requires in order to do its work. Anyway, is mysql actually configured to listen for external connections on that IP (which is what it will seem to be when you use the IP, even if physically your client is on the same machine)? Silly question but have you verified the IP is correct? Does the machine have any kind of firewall in operation? – ADyson Jun 26 '18 at 13:27
  • Install PsPing and from a remote host try run a TCP ping by `psping 192.0.0.0:3306 -T` If it says packet lost it means there's a firewall blocking that port. You can get PsPing here https://learn.microsoft.com/en-us/sysinternals/downloads/psping (PS: nmap can do the same thing if you have it installed) – James Wong Jun 26 '18 at 13:30
  • https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql may also be a useful resource – ADyson Jun 26 '18 at 13:31
  • https://i.imgur.com/oag3mYb.png i created a new user i still get the same error.'unable to connect to any of the specified mysql ' – A. Dravid Jun 26 '18 at 13:32
  • it does listen to 3306, it doesnt return 100%packetloss. i think i configured it for external connections, as i said i followed most of the tutorials. the only thing i didn't do is to uncomment bind-adress:127.0.0.1 because there isn't any. i stopped the firewall, same error – A. Dravid Jun 26 '18 at 13:45
  • @A.Dravid Try editing it to `bind-address = 0.0.0.0` – James Wong Jun 26 '18 at 13:56
  • that actually did the trick.. i don't understand why but thank you guys very much <3 – A. Dravid Jun 26 '18 at 14:01
  • it's because it makes it bind to all interfaces, not just localhost – ADyson Jun 26 '18 at 14:27

1 Answers1

3

Since it worked out for you, am gonna post this as an answer :) What shed the light into your situation is that you mentioned the MySQL port binding in the config is set to

bind-address = 127.0.0.1

The above config meant MySQL only listens to incoming requests from localhost. To have it listening to all interfaces, change it to

bind-address = 0.0.0.0

There might be security risks associated with listening on all interfaces. Those risk can be mitigated by whitelisting specific IP addresses by either defining users from specific host or adding firewall rules at network level.

CREATE USER 'dbuser'@'192.0.0.100';

Reference:

Glad it worked out for you in the end, and enjoy your stay at SO. Cheers!

James Wong
  • 4,529
  • 4
  • 48
  • 65