MySQL 如何允许远程连接到mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14779104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to allow remote connection to mysql
提问by Leo
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?
我怎样才能做到这一点？
回答by mjuarez
That is allowed by default on MySQL.
这在 MySQL 上是默认允许的。
What is disabled by default is remote rootaccess. If you want to enable that, run this SQL command locally:
默认禁用的是远程root访问。如果要启用它，请在本地运行此 SQL 命令：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
And then find the following line and comment it outin your my.cnffile, which usually lives on /etc/mysql/my.cnfon Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).
然后找到以下行并在您的文件中将其注释掉my.cnf，该文件通常存在/etc/mysql/my.cnf于 Unix/OSX 系统上。在某些情况下，文件的位置是 /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.
回答by Matthew Lock
After doing all of above I still couldn't login as rootremotely, but Telnetting to port 3306confirmed that MySQLwas accepting connections.
完成上述所有操作后，我仍然无法root远程登录，但 Telnet 到端口3306确认MySQL正在接受连接。
I started looking at the users in MySQL and noticed there were multiple root userswith different passwords.
我开始查看 MySQL 中的用户，并注意到有多个 root 用户使用不同的密码。
select user, host, password from mysql.user;
So in MySQLI set all the passwords for rootagain 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;
回答by Mayur Patel
Just a note from my experience, you can find configuration file under this path /etc/mysql/mysql.conf.d/mysqld.cnf.
根据我的经验，您可以在此路径下找到配置文件/etc/mysql/mysql.conf.d/mysqld.cnf。
(I struggled for some time to find this path)
（我挣扎了一段时间才找到这条路）
回答by Kushal
In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.
就我而言，我试图连接到 Cent OS 上的远程 mysql 服务器。在经历了很多解决方案（授予所有权限、删除 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. In that case following should be done:
- 不建议在生产环境中使用此方法，但如果您的 iptables 配置不正确，添加规则可能仍然无法解决问题。在这种情况下，应执行以下操作：
service iptables stop
Hope this helps.
希望这可以帮助。
回答by Hiren Parghi
Please follow the below mentioned steps inorder to set the wildcard remote access for MySQL User.
请按照下面提到的步骤为 MySQL 用户设置通配符远程访问。
(1) Open cmd.
(2) navigate to path C:\Program Files\MySQL\MySQL Server 5.X\bin and run this command.
mysql -u root -p
(3) Enter the root password.
(4) Execute the following command to provide the permission.
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
USERNAME: Username you wish to connect to MySQL server.
IP: Public IP address from where you wish to allow access to MySQL server.
PASSWORD: Password of the username used.
IP can be replaced with % to allow user to connect from any IP address.
(5) Flush the previleges by following command and exit.
FLUSH PRIVILEGES;
exit;or \q
(1) 打开 cmd。
(2) 导航到路径 C:\Program Files\MySQL\MySQL Server 5.X\bin 并运行此命令。
mysql -u 根 -p
(3) 输入root密码。
(4) 执行以下命令提供权限。
将 *.* 上的所有权限授予由“密码”标识的“用户名”@“IP”；
USERNAME：您希望连接到 MySQL 服务器的用户名。
IP：您希望允许访问 MySQL 服务器的公共 IP 地址。
PASSWORD：使用的用户名的密码。
IP 可以替换为 % 以允许用户从任何 IP 地址进行连接。
(5) 按照命令刷新权限并退出。
同花顺特权；
出口; 或\q
回答by Timmmm
If you installed MySQL from brewit really does only listen on the local interface by default. To fix that you need to edit /usr/local/etc/my.cnfand change the bind-addressfrom 127.0.0.1to *.
如果您从中安装了 MySQL，brew则默认情况下它确实仅在本地接口上侦听。要解决此问题，您需要编辑/usr/local/etc/my.cnf并将bind-addressfrom更改127.0.0.1为*.
Then run brew services restart mysql.
然后运行brew services restart mysql。
回答by tadman
If your MySQL server process is listening on 127.0.0.1 or ::1 only then you will not be able to connect remotely. If you have a bind-addresssetting in /etc/my.cnfthis might be the source of the problem.
如果您的 MySQL 服务器进程仅在 127.0.0.1 或 ::1 上侦听，那么您将无法远程连接。如果您bind-address在/etc/my.cnf此设置可能是问题的根源。
You will also have to add privileges for a non-localhostuser as well.
您还必须为非localhost用户添加权限。
回答by albus_severus
All process for remote login. Remote login is off by default.You need to open it manually for all ip..to give access all ip
远程登录的所有过程。默认情况下远程登录是关闭的。您需要手动打开所有ip..才能访问所有ip
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
Specific Ip
特定IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'your_desire_ip' IDENTIFIED BY 'password';
then
然后
flush privileges;
You can check your User Host & Password
您可以检查您的用户主机和密码
SELECT host,user,authentication_string FROM mysql.user;
Now your duty is to change this
现在你的职责是改变这一点
bind-address = 127.0.0.1
You can find this on
你可以找到这个
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
if you not find this on there then try this
如果你在那里没有找到这个，那么试试这个
sudo nano /etc/mysql/my.cnf
comment in this
在此评论
#bind-address = 127.0.0.1
Then restart Mysql
然后重启Mysql
sudo service mysql restart
Now enjoy remote login
现在享受远程登录
回答by Dan
Just F.Y.I I pulled my hair out with this problem for hours.. finally I call my hosting provider and found that in my case using a cloud server that in the control panel for 1and1 they have a secondary firewall that you have to clone and add port 3306. Once added I got straight in..
仅供参考，我花了几个小时解决这个问题。最后我打电话给我的托管服务提供商，发现在我的情况下使用云服务器，在 1and1 的控制面板中，他们有一个辅助防火墙，您必须克隆并添加端口3306.一加我就直接进去了..
回答by Zelkovar
For whom it needs it, check firewall port 3306 is open too, if your firewall service is running.
对于需要它的人，如果您的防火墙服务正在运行，请检查防火墙端口 3306 是否也已打开。
