Cannot connect remotely to MySQL (TCP/IP) running

2019-07-23 05:45发布

问题:

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)

回答1:

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:

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.