远程连接 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?

EDIT: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

应该会返回类似这样的内容，其中包含您在 xxx 中的实际 IP

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)

如果上述语句正确返回，您将能够接受远程用户.但是，要使远程用户使用正确的权限连接，您需要在 localhost 和 '%' 中都创建该用户.

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 语句语法 这里 如果你完成了所有这些但仍然有问题，请发布一些额外的错误输出和 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 在此处安装它分配.您不需要 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.

更新:如果即使在 my.cnf 中添加/更改 bind-address 也不起作用，那么去最初声明的地方更改它:

UPDATE: If even after adding/changing the bind-address in my.cnf did not work, then go and change it in the place it was originally declared:

/etc/mysql/mariadb.conf.d/50-server.cnf

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