远程连接Mysql Ubuntu [英] Remote Connections Mysql Ubuntu

问题描述

由于某种原因，我无法远程连接到我的MySQL服务器.我已经尝试了一切，但仍然遇到错误.

For some reason, I've been unable to connect remotely to my MySQL server. I've tried everything and I'm still getting errors.

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)

现在，我已经尝试运行

 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?

编辑:my.cnf已注释掉绑定ip.

EDIT: my.cnf has commented out the bind ip .

推荐答案

要将MySQL暴露给localhost以外的任何内容，您将必须具有以下行

To expose MySQL to anything other than localhost you will have to have the following line

对于mysql 5.6和更低版本

在/etc/mysql/my.cnf中未注释，并已分配给您的计算机IP地址，并且没有环回

uncommented in /etc/mysql/my.cnf and assigned to your computers IP address and not loopback

对于MySQL 5.7及更高版本

在/etc/mysql/mysql.conf.d/mysqld.cnf中未注释，并已分配给您的计算机IP地址，并且没有环回

uncommented in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers IP address and not loopback

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

或添加一个 bind-address = 0.0.0.0如果您不想指定IP

Or add a bind-address = 0.0.0.0 if you don't want to specify the IP

然后停止并使用新的my.cnf条目重新启动MySQL.运行后，请转到终端并输入以下命令.

Then stop and restart MySQL with the new my.cnf entry. Once running go to the terminal and enter the following command.

lsof -i -P | grep :3306

应该返回这样的内容，并且您的实际IP位于xxx中

That should come back something like this with your actual IP in the xxx's

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.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

然后

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

最后

FLUSH PRIVILEGES; 
EXIT;

如果您没有创建与上述相同的用户，则在本地登录时，您可能会继承基本的localhost特权，并且会出现访问问题.如果要限制myuser的访问权限，则需要阅读GRANT语句的语法 HERE 如果您解决了所有这些问题，但仍然遇到问题，请发布一些其他错误输出以及my.cnf相应的行.

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 HERE If you get through all this and still have issues post some additional error output and the my.cnf appropriate lines.

注意:如果lsof不返回或找不到，您可以根据Linux发行版 HERE 进行安装.您不需要lsof即可使事情正常运行，但是当事情无法按预期进行时，这非常方便.

NOTE: If lsof does not return or is not found you can install it HERE based 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.

这篇关于远程连接Mysql Ubuntu的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！