Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 15 '13 at 8:31

This question came from our site for professional and enthusiast programmers.

    
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; –  medina Apr 15 '13 at 2:15
    
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 –  medina Apr 15 '13 at 2:18
    
Yes, it's what I got for this user and I have flushed the privileges. Still no luck :( –  Marc Apr 15 '13 at 2:22
    
Make the bind-address "Unchecked" and restart the server. This line should actually be commented out in your my.cnf –  medina Apr 15 '13 at 2:26
    
Sorry, it doesn't work –  Marc Apr 15 '13 at 2:31

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
When I bind the address to my external IP, the server can't restart because of an unexpected error –  Marc Apr 15 '13 at 14:44
    
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 Apr 15 '13 at 19:38
    
So should I set the bind address to 192.168.x.x? –  Marc Apr 16 '13 at 19:06

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.