1

I'm trying to connect to MySQL Server installed on a machine with Windows OS. The connection is remote to type, but not on a local network (LAN). A pc (also with windows) in my house and the other in college. For three days I have been reading the documentation on the MySQL website and other sources, such as other SO questions but got no success. Below all my settings. Also wanted to know if anyone else is having the same problem. No need to mention the issue of security (SSH, token, pipe, etc), I do this using free software just to show the lack of security or bad pratice, just to educational purposes.

  • All ports are open on the router, and it is configured correctly, firewall off;
  • Firewall turned off on both computers (client / provider) and all the doors open;
  • The two computers have Win7 Ultimate 64x;
  • The service of MySQL 5.5.37 Community Server is running on the server computer;
  • The client tries to connect by HeidiSQL 8.3 (TCP/IP) (64x);
  • The SQL GRANT ALL PRIVILEGES ON * TO 'USERNAME' @ 'IP' IDENTIFIED BY 'PASSWORD';. FLUSH PRIVILEGES;, has already been tested.
  • The file "my.ini" was checked the "bind-address" in the Win version does not have this entry.
  • The variable "skip_external_locking" was set to "OFF";
  • Services such as FTP and HTTP function normally.
  • Any tips on this question were tested.
  • Misses something important?
  • The error:

ERROR 2003 (HY000): Can't connect to MySQL server on 123.123.123.123 (10061)

mysql> select host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| %         | root | *blablablablabla                          |
| 127.0.0.1 | root | *balblablalbalbalb                        |
| ::1       | root |                                           |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
Community
  • 1
  • 1
Artur_Indio
  • 736
  • 18
  • 35
  • 1
    Of course it's possible. Otherwise a MySQL installation on Windows would be about as useful as one shoe. – user2864740 Aug 29 '14 at 00:35
  • Is the PC with the database the one at your house or the one at your college? It's possible that the college would block port 3306. If it is on the PC at your house, make sure the correct local IP is specified for port 3306 (in the port forwarding settings). When you type in your home's external IP, at the college, this is the only way for the router to know what device it should go to via that port. – Brian DeMilia Aug 29 '14 at 00:40
  • The database is in my house. in my home router ports have been targeted. I even left in DMZ for testing, but still did not work. Do you think the university is blocking connection to port 3036? Remembering that my home pc is the server with the database and the pc at the university is the customer with Heidi. – Artur_Indio Aug 29 '14 at 00:54
  • Great hehehe @user2864740 it's true. – Artur_Indio Aug 29 '14 at 00:59
  • What I do not understand is that the lan works normally. – Artur_Indio Aug 29 '14 at 01:00
  • @Artur_Indio That narrows down the problem a good bit: what's between the College and the LAN? Use a port scanner (or even telnet) from the remote location to get a better idea of why the connection is refused - there is a difference between not being able to establish the TCP link (network issue/firewall) and then being refused by the MySQL server (security configuration). – user2864740 Aug 29 '14 at 01:06
  • Ok, I will do that and test connection with other pc, my father pc for example. Thinking may well be that the university does not allow this connection in its general server. – Artur_Indio Aug 29 '14 at 01:18
  • If the database is at your house I doubt it has anything to do w/ the college. Have you forwarded port 3306 to the internal IP (likely 192.168.1.xxx) with the database on it? – Brian DeMilia Aug 29 '14 at 02:10
  • Yes I did, http, ftp, works well. I tried open and forward all ports too. I find this [question](http://stackoverflow.com/questions/11905895/mysql-remote-connection-not-as-usual?rq=1) (but in linux) and now I will unistall and install again mysql. – Artur_Indio Aug 29 '14 at 02:21

1 Answers1

0

I finally got it! So what I did:

  • Fully Uninstall MySQL;
  • I installed the latest version of MySQL 5.6;
  • I did all the steps in the question above;
  • I did all the tests of ports, and everything working;
  • I tried to connect as root to the host '%', it did not work;
  • I created a new User with all privileges of the root and host '%', then I could connect.

When did MySQL 5, all the doors were open and still not connected, even putting exceptions in the firewall for the ports and for the mysql program. But when I installed MySQL 6 it automatically created the exception to the windows firewall as below:

image

Were equal to MySQL5.This was not an answer. In fact Windows the best way to prevent such inconvenience is always using the latest version of MySQL. So if someone did all the steps I listed in the question and did not work, completely uninstall MySQL and install the latest version may solve the problem, just be careful with your database.

Artur_Indio
  • 736
  • 18
  • 35