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

2023-11-15 12:01 由潇湘隐者发表于 #数据库

这里简单总结一下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. 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: CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password'; CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';

那么怎么解决这个问题呢？一共有下面几种方法。

方案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

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

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@/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 >