如何允许远程连接到MySQL

本文翻译自：How to allow remote connection to mysql

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source. 我已经在本地计算机上安装了MySQL Community Edition 5.5，并且希望允许远程连接，以便可以从外部源进行连接。

How can I do that? 我怎样才能做到这一点？

---

#1楼

参考：https://stackoom.com/question/100ie/如何允许远程连接到MySQL

---

#2楼

If your MySQL server process is listening on 127.0.0.1 or ::1 only then you will not be able to connect remotely. 如果您的MySQL服务器进程仅在127.0.0.1或:: 1上侦听，则您将无法远程连接。 If you have a bind-address setting in /etc/my.cnf this might be the source of the problem. 如果在/etc/my.cnf具有bind-address设置，则可能是问题的根源。

You will also have to add privileges for a non- localhost user as well. 您还必须为非localhost用户添加特权。

---

#3楼

That is allowed by default on MySQL. MySQL默认情况下允许这样做。

What is disabled by default is remote root access. 默认情况下禁用的是远程root访问。 If you want to enable that, run this SQL command locally: 如果要启用它，请在本地运行此SQL命令：

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. 然后找到下面一行，在您的评论出来 my.cnf文件，通常生活在/etc/mysql/my.cnf在Unix / OSX系统。 In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf). 在某些情况下，文件的位置是/etc/mysql/mysql.conf.d/mysqld.cnf。

If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\\Program Files\\MySQL\\MySQL Server 5.5\\ and the filename will be my.ini . 如果是Windows系统，则可以在MySQL安装目录中找到它，通常类似于C:\\Program Files\\MySQL\\MySQL Server 5.5\\ ，文件名将为my.ini 。

Change line 换线

 bind-address = 127.0.0.1

to 至

 #bind-address = 127.0.0.1

And restart the MySQL server ( Unix/OSX , and Windows ) for the changes to take effect. 并重新启动MySQL服务器（ Unix / OSX和Windows ），以使更改生效。

---

#4楼

In my case I was trying to connect to a remote mysql server on cent OS. 就我而言，我试图连接到cent OS上的远程mysql服务器。 After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved. 经过许多解决方案（授予所有特权，删除ip绑定，启用网络连接）之后，问题仍然没有得到解决。

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections. 事实证明，在研究各种解决方案时，我遇到了iptables，这使我意识到mysql端口3306不接受连接。

Here is a small note on how I checked and resolved this issue. 这是有关我如何检查和解决此问题的小注释。

• Checking if port is accepting connections: 检查端口是否接受连接：

telnet (mysql server ip) [portNo]

• Adding ip table rule to allow connections on the port: 添加ip表规则以允许端口上的连接：

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

• Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. 不建议在生产环境中使用此功能，但是如果您的iptables配置不正确，则添加规则可能仍无法解决问题。 In that case following should be done: 在这种情况下，应执行以下操作：

service iptables stop

Hope this helps. 希望这可以帮助。

---

#5楼

After doing all of above I still couldn't login as root remotely, but Telnetting to port 3306 confirmed that MySQL was accepting connections. 完成上述所有操作后，我仍然无法以root身份远程登录，但是Telnet到端口3306确认MySQL正在接受连接。

I started looking at the users in MySQL and noticed there were multiple root users with different passwords. 我开始查看MySQL中的用户，发现有多个root用户使用不同的密码。

select user, host, password from mysql.user;

So in MySQL I set all the passwords for root again and I could finally log in remotely as root . 因此，在MySQL我再次为root设置了所有密码，最终我可以以root身份远程登录。

use mysql;
update user set password=PASSWORD('NEWPASSWORD') where User='root';
flush privileges;

---

#6楼

如果防火墙服务正在运行，请向谁需要它，请检查防火墙端口3306是否也已打开。