MySQL远程访问设置终极方法

可以看到数据库中有如上这些用户。那么，如果有'root'@'localhost'登录时，怎么匹配呢？

根据MySQL官方文档MySQL :: MySQL 5.6 Reference Manual :: 6.2.4 Access Control, Stage 1: Connection Verification里是如下面这样说的：

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.

When a client attempts to connect, the server looks through the rows in sorted order.

The server uses the first row that matches the client host name and user name.

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern ‘%' means “any host” and is least specific. The empty string ” also means “any host” but sorts after ‘%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific).

请注意这句话：“The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific.”

那么，当从本地连接到数据库时，匿名用户将覆盖其他如'[any_username]'@'%'之类的用户。具体匹配过程如下：

在'root'@'localhost'可以匹配'root'@'%'，''也可以匹配root，那么'root'@'localhost'也可以匹配''@'localhost'。根据上面的文档显示，主机（host）有比用户名称user更高的匹配优先级，则localhost比%有比更高的匹配优先级。所以，''@'localhost'比'root'@'%'匹配优先级更高，'root'@'localhost'就匹配到了''@'localhost'。根据我们上面的查询结果来看，''@'localhost'的密码为空，与登录时提供的密码123456不匹配。所以，就登录失败了。

说到这里，想必大家已经想到了解决方案：很简单，直接把匿名用户删掉就可以了。

但是，现在的问题时，在本地根本登录不上数据库，更别扯删除数据了。

怎么办？D瓜哥想到了三个解决办法，方法如下：

①、比较扯淡的解决方法，在另外一台电脑或者虚拟机上，远程连接这个数据库

这是我想到的一个比较扯淡的解决方法。既然我们已经允许了远程连接，那么我们就可以在另外一台电脑或者另外一个虚拟机上，远程连接到这个数据库，然后把删除匿名用户。步骤如下：

1.在远程电脑上连接到该数据库。命令如下：

mysql -h192.168.1.119 -uroot -p123456

2.然后，切换到mysql数据库。命令如下：

 5/9   首页 上一页 3 4 5 6 7 8 下一页 尾页