无法远程连接到 Windows 上运行的 MySQL (TCP/IP) [英] Cannot connect remotely to MySQL (TCP/IP) running on Windows

问题描述

我正在尝试连接到安装在装有 Windows 操作系统的机器上的 MySQL 服务器.连接是远程键入的，但不在本地网络 (LAN) 上.我家有一台电脑(也有窗户)，另一台在大学里.三天来，我一直在阅读 MySQL 网站和其他来源上的文档，例如其他 SO 问题，但没有成功.在我的所有设置下方.也想知道有没有其他人遇到同样的问题.无需提及安全问题(SSH、令牌、管道等)，我使用免费软件来做这件事只是为了表明缺乏安全性或不良实践，只是为了教育目的.

• 路由器上所有端口都打开，并且配置正确，防火墙关闭；
• 两台计算机(客户端/提供商)的防火墙都关闭了，所有的门都打开了；
• 两台电脑都是Win7 Ultimate 64x；
• MySQL 5.5.37 Community Server 的服务正在服务器计算机上运行；
• 客户端尝试通过 HeidiSQL 8.3 (TCP/IP) (64x) 连接；
• SQL GRANT ALL PRIVILEGES ON * TO 'USERNAME' @ 'IP' IDENTIFIED BY 'PASSWORD';.FLUSH PRIVILEGES;，已经测试过了.
• 文件my.ini"已检查，Win版本中的bind-address"没有此项.
• 变量skip_external_locking"被设置为OFF"；
• FTP、HTTP 等服务正常运行.
• 测试了有关此问题的任何提示.
• 错过了什么重要的事情?
• 错误:

ERROR 2003 (HY000): 无法连接到 MySQL 服务器 123.123.123.123 (10061)

<块引用>

mysql>从 mysql.user 中选择主机、用户、密码；+-----------+------+-------------------------------------------+|主机|用户 |密码 |+-----------+------+-------------------------------------------+|% |根|*blablablablabla ||127.0.0.1 |根|*balblablalbalbalb ||::1 |根|||本地主机 |||+-----------+------+-------------------------------------------+4 行(0.00 秒)

解决方案

我终于明白了！所以我做了什么:

• 完全卸载MySQL；
• 我安装了最新版本的 MySQL 5.6；
• 我完成了上述问题中的所有步骤；
• 我完成了所有端口测试，一切正常；
• 我尝试以 root 用户身份连接到主机%"，但没有成功；
• 我创建了一个拥有 root 和主机%"所有权限的新用户，然后我就可以连接了.

MySQL 5 什么时候发布的，所有的门都打开了，但仍然没有连接，甚至在防火墙中为端口和 mysql 程序设置了例外.但是当我安装 MySQL 6 时，它会自动创建 Windows 防火墙的例外，如下所示:

等于 MySQL5.这不是答案.事实上，Windows 防止这种不便的最好方法始终是使用最新版本的 MySQL.因此，如果有人执行了我在问题中列出的所有步骤但没有奏效，则完全卸载 MySQL 并安装最新版本可能会解决问题，只需小心您的数据库即可.

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)

解决方案

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.

这篇关于无法远程连接到 Windows 上运行的 MySQL (TCP/IP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！