8

When I am trying to connect to a MySQL database from a remote computer I get a prompt saying: Connection Failed: [HY000] [MySQL][ODBC 5.1 Driver]Can't Connect to MySQL server on 'XXX.XXX.XX.XX' (10060)

I have created a user account in the MySQL Administrator and added a host to enable remote access, I have also made an exception for my Windows Firewall on port 3306 but the connection still fails.

What is the problem?

Thanks!

  • 1
    Not a programming related question, but anyway, check if the remote computer is able to connect to any other service running on the same computer you're running the MySQL server. I doubt it is however. Very likely to be a NAT problem. –  Apr 30 '10 at 12:51
  • @jweyrich: You were very right, the ISP have blocked all ports but 21, 80 and 3389. –  May 01 '10 at 09:12

8 Answers8

5

I suppose your mysql server is running on Windows...thus open a DOS window and type:

netstat -an

You should find a row like this:

TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

This tell you that mysql server is running and listening on TCP port 3306.

Another test you can do is to telnet (from another pc) to your mysql server:

telnet ip_mysql_server 3306

This should open a telnet session:

Connected to server_name.
Escape character is '^]'.
5.0.XX-community-nt-log
TallTed
  • 269
  • 2
  • 9
lg.
  • 4,579
  • 3
  • 20
  • 20
  • Thanks for this, I tried connection to the server from the remote computer using "telnet ip_mysql_server 3306" but the connection fails. –  Apr 30 '10 at 16:02
  • Maybe this seems a stupid question but...is running mysql server? Which is the result of netstat? – lg. May 03 '10 at 08:44
3

The error 10060 means that you are not allowed to access the web server remotely. Therefore

  1. Make sure that you are using the correct IP-address of the MySQL server.
  2. The rest of the parameters of your connection are the same.
  3. Use the % wildcard for the host part of the user account to allow connect from any host (see Adding User Accounts article).

Please, see Solution to Connecting remotely when you get Error 10060 post for details.

Alexander
  • 131
  • 4
2

First of all, it's a very bad idea to have a worldwide open port to your database server. Especially if it's on the MySQL default port. You're just inviting people to try to break in to your database.

Have you tried using a port scanning tool like nmap on your from your remote computer to scan the database server? It will tell you if the port is open with nmap -PN -p 3306 XXX.XXX.XX.XX?

amphetamachine
  • 832
  • 1
  • 8
  • 14
1

look at this article to connect MYSQL from remote server

But that's not a good idea. If you are going to access the database from another remote server, why don't you place a file inside your site with some security such htpasswd protection or some authentication module...

If you are going to get data of a mysql server to another server, be precise with the requirement...

If it's just for data synchronization, go for db server concepts...

TallTed
  • 269
  • 2
  • 9
Vijay
  • 151
  • 4
1

Check the mysql configuration: /etc/my.cnf

and comment out:

skip-networking

Now it will be listening to TCP/IP ports, however you have usually blocked direct access to MySQL via the firewall anyway, so it shouldn't be a problem. You can now SSH in over TCP/IP as "localhost" to the MySQL server.

e.g., Windows bat file, PuTTY tunnel:

@putty.exe -ssh -pw PassWord -L 3306:localhost:3306 odbcuser@remoteHost
EXIT

If you have XAMPP/MySQL running on your local machine, change the above port to 3307, ie:

3307:localhost:3306
TallTed
  • 269
  • 2
  • 9
user127379
  • 473
  • 4
  • 11
1

1) iptables access

sudo iptables -I INPUT -p tcp --dport 3306-j ACCEPT 
sudo service iptables save

2) my.conf

[mysqld]
port = 3306
bind-address = 0.0.0.0

3) mysql

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%';
FLUSH PRIVILEGES;

4) mysqld

sudo service mysqld restart
  • As @amphetamachine describes in the answer above, this is **far from best practices**. 1. & 2. opens MySQL to everyone. 3. is not related to the question at all - and even if it was, it gives access to every database instead of one, which is probably not wanted. – Esa Jokinen Mar 27 '15 at 13:46
0

The likely culprit that people often forget is:

mysql> GRANT ALL ON foo.* TO bar@'123.456.789.123' IDENTIFIED BY 'PASSWORD';

This step is needed to be able to connect from the computer 123.456.789.123 More information and a more complete guide can be found at Holicreature's link.

MatsT
  • 113
  • 3
0

I had exactly the same issue communicating between a MSSQL Server database on one (Win2008) server and a MySQL database on another.

I had already created an ODBC connection between one MSSQL Server and the MySQL server a couple of years ago; primarily to allow me to create a Linked Server object on the MSSQL Server.

When trying to create an ODBC connection from a different server I receive the same error:

Connection Failed: [HY000] [MySQL][ODBC 5.1 Driver]Can't Connect to MySQL server on 'XXX.XXX.XX.XX' (10060).

After following some of the suggestions in the answers found here, I decided to check the firewall rules on the (Win2008) server hosting MySQL.

I had an inbound rule set up that locked port 3306 down to be accessible only from a list of IP addresses. Once I added my latest server's IP to the list, I was able to connect.

Andrew Schulman
  • 8,561
  • 21
  • 31
  • 47
Armaitus
  • 1
  • 1