远程连接 Mysql Ubuntu
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15663001/
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
Remote Connections Mysql Ubuntu
提问by Cristian Eduardo Lehuede Lyon
For some reason, I've been unable to connect remotely to my MySQL server. I've tried everything and I'm still getting errors.
出于某种原因，我一直无法远程连接到我的 MySQL 服务器。我已经尝试了一切，但仍然出现错误。
root@server1:/home/administrator# mysql -u monty -p -h www.ganganadores.cl
Enter password:
ERROR 1045 (28000): Access denied for user 'monty'@'server1.ganganadores.cl' (using password: YES)
Now, I've tried running
现在，我试过跑步
GRANT ALL ON *.* to monty@localhost IDENTIFIED BY 'XXXXX';
GRANT ALL ON *.* to monty@'%' IDENTIFIED BY 'XXXXXX';`
and still nothing! What I'm doing wrong?
还是什么都没有！我做错了什么？
EDIT: my.cnfhas commented out the bind ip .
编辑：my.cnf已注释掉绑定 ip。
回答by apesa
To expose MySQL to anything other than localhost you will have to have the following line
要将 MySQL 公开给 localhost 以外的任何内容，您必须具有以下行
For mysql version 5.6 and below
对于 mysql 5.6 及以下版本
uncommented in /etc/mysql/my.cnfand assigned to your computers IP address and not loopback
取消注释/etc/mysql/my.cnf并分配给您的计算机 IP 地址而不是环回
For mysql version 5.7 and above
对于 mysql 5.7 及以上版本
uncommented in /etc/mysql/mysql.conf.d/mysqld.cnfand assigned to your computers IP address and not loopback
取消注释/etc/mysql/mysql.conf.d/mysqld.cnf并分配给您的计算机 IP 地址而不是环回
#Replace xxx with your IP Address
bind-address = xxx.xxx.xxx.xxx
Or add a
bind-address = 0.0.0.0if you don't want to specify the IP
或者
bind-address = 0.0.0.0如果不想指定 IP 则添加
Then stop and restart MySQL with the new my.cnf entry. Once running go to the terminal and enter the following command.
然后停止并使用新的 my.cnf 条目重新启动 MySQL。运行后转到终端并输入以下命令。
lsof -i -P | grep :3306
That should come back something like this with your actual IP in the xxx's
这应该会与您在 xxx 中的实际 IP 返回类似的内容
mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)
If the above statement returns correctly you will then be able to accept remote users. However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and '%' as in.
如果上述语句正确返回，则您将能够接受远程用户。但是，要使远程用户使用正确的权限连接，您需要在 localhost 和 '%' 中都创建该用户。
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
then,
然后，
GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
and finally,
最后，
FLUSH PRIVILEGES;
EXIT;
If you don't have the same user created as above, when you logon locally you may inherit base localhost privileges and have access issues. If you want to restrict the access myuser has then you would need to read up on the GRANT statement syntax HEREIf you get through all this and still have issues post some additional error output and the my.cnf appropriate lines.
如果您没有像上面那样创建相同的用户，当您在本地登录时，您可能会继承基本的 localhost 权限并遇到访问问题。如果您想限制 myuser 的访问权限，那么您需要在此处阅读 GRANT 语句语法。
NOTE: If lsof does not return or is not found you can install it HEREbased on your Linux distribution. You do not need lsof to make things work, but it is extremely handy when things are not working as expected.
注意：如果 lsof 没有返回或未找到，您可以根据您的 Linux 发行版在此处安装它。您不需要 lsof 来使事情正常工作，但是当事情没有按预期工作时它非常方便。
回答by Jonathan L
Add few points on top of apesa's excellent post:
在 apesa 的优秀帖子之上补充几点：
1) You can use command below to check the ip address mysql server is listening
1）您可以使用下面的命令来检查mysql服务器正在侦听的IP地址
netstat -nlt | grep 3306
sample result:
tcp 0 0 xxx.xxx.xxx.xxx:3306 0.0.0.0:* LISTEN
2) Use FLUSH PRIVILEGESto force grant tables to be loaded if for some reason the changes not take effective immediately
2)FLUSH PRIVILEGES如果由于某种原因更改没有立即生效，则用于强制加载授权表
GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'user'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
user == the user u use to connect to mysql ex.root
passwd == the password u use to connect to mysql with
用户 == 你用来连接到 mysql 的用户 ex.root
passwd == 你用来连接到 mysql 的密码
3) If netfilter firewall is enabled (sudo ufw enable) on mysql server machine, do the following to open port 3306 for remote access:
3）如果sudo ufw enable在mysql服务器机器上启用了netfilter防火墙（），请执行以下操作以打开端口3306以进行远程访问：
sudo ufw allow 3306
check status using
检查状态使用
sudo ufw status
4) Once a remote connection is established, it can be verified in either client or server machine using commands
4) 一旦建立了远程连接，就可以使用命令在客户端或服务器机器上进行验证
netstat -an | grep 3306
netstat -an | grep -i established
回答by Vinh Lee
MySQL only listens to localhost, if we want to enable the remote access to it, then we need to made some changes in my.cnf file:
MySQL 只监听 localhost，如果我们想要远程访问它，那么我们需要在 my.cnf 文件中做一些更改：
sudo nano /etc/mysql/my.cnf
We need to comment out the bind-address and skip-external-locking lines:
我们需要注释掉 bind-address 和 skip-external-locking 行：
#bind-address = 127.0.0.1
# skip-external-locking
After making these changes, we need to restart the mysql service:
进行这些更改后，我们需要重新启动 mysql 服务：
sudo service mysql restart
回答by marw
If testing on Windows, don't forget to open port 3306.
如果在 Windows 上测试，不要忘记打开端口 3306。
