MySQL Shell连接数据库报MySQL Error 1045 (28000)错误浅析

柏拉兔 · 发表于 2023-11-15 19:54:43

这里简单总结一下mysql shell访问数据库时报MySQL Error 1045 (28000): Access denied for user 'root'@'::1' (using password: YES)的原因以及如何解决这个问题
这里测试的环境为MySQL 8.0.35，我们先来看看报错案例：

$ mysqlsh -h localhost -P 7306 -u root -p
Please provide the password for 'root@localhost:7306': ***********
MySQL Shell 8.0.35
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:7306'
MySQL Error 1045 (28000): Access denied for user 'root'@'::1' (using password: YES)

复制代码

先用root账号连接数据(socket方式),检查用户信息，如下所示，root账号限定为localhost

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
13 rows in set (0.00 sec)
mysql>

复制代码

然后，检查变量skip_name_resolve,如下所示，skip_name_resolve为ON

mysql> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| skip_name_resolve | ON |
+-------------------+-------+
1 row in set (0.01 sec)
mysql>

复制代码

数据库参数skip_name_resolve设置为ON，它是禁止域名解析的，一般都推荐将这个参数设置为ON，因此服务器不会尝试解析连接客户端的名称或每次都在主机名缓存中查找它们(甚至localhost也会被解析/搜索)，根据官方文档的解释，它会限制@localhost的连接。
官方文档的详细解释：

Depending on the network configuration of your system and the Host values for your accounts, clients may need to connect using
an explicit --host option, such as --host=127.0.0.1 or --host=::1.
<p>An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run
with skip_name_resolve enabled. If you plan to do that, make sure an account exists that can accept a connection. For example,
to be able to connect as root using --host=127.0.0.1 or --host=::1, create these accounts:</p>
[code]CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password';
CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';

复制代码

[/code]
那么怎么解决这个问题呢？一共有下面几种方法。
方案1：skip_name_resolve设置为OFF

我们需要在参数文件my.cnf中将参数skip-name-resolve注释或者设置skip_name_resolve设置为OFF的.
注意事项，虽然官方文档中，参数skip-name-resolve是Boolean类型，但是如果你像下面这样设置是不会生效的，具体原因不是很清楚

skip-name-resolve=0
或
skip-name-resolve=FALSE

复制代码

正确的做法

方法1：
skip-name-resolve=OFF
方法2：
#skip-name-resolve 注释掉参数

复制代码

修改skip_name_resolve的值为OFF后，重启一下MySQL实例，然后我们验证一下测试结果。

mysql> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| skip_name_resolve | OFF |
+-------------------+-------+
1 row in set (0.01 sec)
mysql>

复制代码

$ mysqlsh -h localhost -P 7306 -u root -p
MySQL Shell 8.0.35
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:7306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:7306 ssl JS >

复制代码

这种方案需要修改参数，需要重启MySQL实例，所以一般来说，不建议使用。
方案2：新增账号

如下所示，我们新增下面账号

CREATE USER 'root'@'::1' IDENTIFIED BY '********';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1';
FLUSH PRIVILEGES;

复制代码

当然，如果报错为MySQL Error 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)的话，那么可以创建下面用户

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '**********';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1';
FLUSH PRIVILEGES;

复制代码

创建账号后，mysqlsh就可以连接，不会报上面错误了，如下所示：

$ mysqlsh -h localhost -P 7306 -u root -p
Please provide the password for 'root@localhost:7306': ***********
Save password for 'root@localhost:7306'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.0.35
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:7306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:7306 ssl JS >

复制代码

方案3：使用socket方式连接

mysql shell也可以使用socket连接，一般的方式如下：

mysqlsh -h localhost -u root -p -S /tmp/mysql.sock #根据实际情况填写具体的mysql.sock文件
或
\connect root@localhost?socket=(/tmp/mysql.sock)

复制代码

测试验证如下所示：

$ mysqlsh -h localhost -u root -p -S /tmp/mysql.sock
Please provide the password for 'root@/tmp%2Fmysql.sock': ***********
Save password for 'root@/tmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.0.35
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@/tmp%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 22
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost JS >

复制代码

扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/17833536.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！