Can't connect to remote MySQL server but works with localhost

I can't connect to my remote MySQL server

UNABLE TO CONNECT TO ANY OF THE SPECIFIED MYSQL HOSTS

I did some tests and it works with localhost on the server.

My user has the privilege to connect from any host

enter image description here

and this is the network settings of the server

enter image description here

and I bind the address to 0.0.0.0

I'm trying to connect with visual studio with the MySQL connector, and I also tried with a connection string in C# (both works in local)

enter image description here

Also, the windows firewall allow MySQL default port for TCP and I forward 3306 to the server ip on the router(I don't know if that was necessary)

If I do telnet localhost 3306 on my server it works but if I do telnet 216.x.x.x 3306 from a remote computer it doesn't work. I rebooted the router and I forward the port to the server. is port forwarding is the only thing to do on the router?

Any ideas why it doesn't work?

Thank you

Marc

Posted 2013-04-15T01:45:25.973

Reputation: 213

Hey Mac, have you flushed the privileges? You can get it done with the command below or even restarting the mysql server.

mysql> FLUSH PRIVILEGES; – None – 2013-04-15T02:15:23.333

Also, check with privileges your user has with the following command

SHOW GRANTS FOR usager;

You should be able to see something like that : GRANT ALL PRIVILEGES ON . TO 'usager'@'%' IDENTIFIED BY PASSWORD '*' WITH GRANT OPTION – None – 2013-04-15T02:18:59.440

Yes, it's what I got for this user and I have flushed the privileges. Still no luck :( – Marc – 2013-04-15T02:22:37.100

Make the bind-address "Unchecked" and restart the server. This line should actually be commented out in your my.cnf – None – 2013-04-15T02:26:05.090

Sorry, it doesn't work – Marc – 2013-04-15T02:31:19.250

Can you telnet it on port 3306? – None – 2013-04-15T02:54:51.473

oh no. It says Could not open connection to the host on port 3306. – Marc – 2013-04-15T03:01:38.300

Primarily make very sure that your port forwarding is active and that it is working correctly. Problems with this are so common it is ridiculous — the router, Windows firewall, perhaps extra local firewall must all play ball. Sometimes restarts are needed at the router, etc. My tip is to absolutely positively check that the port forwarding rules work before looking at other parts. – Daniel Andersson – 2013-04-15T09:09:07.950

I don't understand what is wrong. If I do telnet localhost 3306 on my server it works but if I do telnet 206.x.x.x from a remote computer it doesn't work. I rebooted the router and I forward the port to the server – Marc – 2013-04-15T14:09:35.080

@DanielAndersson you were right, can you post your answer so I can accept please – Marc – 2013-04-15T15:10:02.253

Alright, glad it worked! – Daniel Andersson – 2013-04-15T16:35:39.023

Answers

Primarily make very sure that your port forwarding is active and that it is working correctly.

Problems with this are so common it is ridiculous — the router, Windows firewall, perhaps an extra local firewall must all play ball. Sometimes restarts are needed at the router, etc.

My tip is to absolutely positively check that the port forwarding rules work before looking at other parts.

Daniel Andersson

Posted 2013-04-15T01:45:25.973

Reputation: 20 465

Set the bind address to your external IP (216...*) The mysql server needs this to know on which IP it should listen. After this make a restart and try to connect to your server.

If not try to add your IP to iptables (firewall)

iptables -A INPUT -p tcp -s 10.10.10.10 --sport 1024:65535 -d 216.*.*.* --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 216.*.*.* --sport 3306 -d 10.10.10.10 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

change the 216...* to your external server ip and 10.10.10.10 to your ip where you want to connect from. Or you say 0/0 instead of 10.10.10.10 to allow all ips.

syss

Posted 2013-04-15T01:45:25.973

Reputation: 273

When I bind the address to my external IP, the server can't restart because of an unexpected error – Marc – 2013-04-15T14:44:05.617

it would have been very useful if you mentioned that you serve this mysql from home. this makes a lot of difference, because most servers you rent get their external ip address directly bound to the network card. In your case you have a router inbetween which needs a little bit more configuration. the error that you can't bind mysql to your external ip is, that the server does not have this ip. it has something like 192.168. due to the port forwarding your router tunnels incoming traffic on port 3306 to your server. – syss – 2013-04-15T19:38:38.543

So should I set the bind address to 192.168.x.x? – Marc – 2013-04-16T19:06:20.070

I got it.

After hours I noticed there's a checkbox to enable the port forwarding. That's why it wasn't working... Once it's checked, it works!

enter image description here

Marc

Posted 2013-04-15T01:45:25.973

Reputation: 213