Issue background
When accessing the database via MySQL Shell, I encounter MySQL Error 1045 (28000): Access denied for user ‘root’@’::1′ (using password: YES). Let’s take a closer look at what this issue entails.
The test environment here is MySQL 8.0.35. Let’s first take a look at an error case:
$ 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)
First, connect to the database using the root account (via socket) and check the user information. As shown below, the root account is restricted to 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>
Next, check the variable skip_name_resolve. As shown below, skip_name_resolve is set to ON.
mysql> show variables like ‘skip_name_resolve’;
+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | skip_name_resolve | ON | +-------------------+-------+ 1 row in set (0.01 sec) mysql>
The database parameter skip_name_resolve is set to ON, which disables domain name resolution. It is generally recommended to set this parameter to ON so that the server does not attempt to resolve client connection names or look them up in the hostname cache each time (even localhost would be resolved/searched). According to the official documentation, it limits connections to @localhost.
Detailed explanation from the official documentation:
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'; CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password'; CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';
How to fix the MySQL 1045 error: Access denied?
Here is 3 methods for you.
Solution 1: Set skip_name_resolve to OFF.
We need to comment out the skip-name-resolve parameter in the my.cnf file or set skip_name_resolve to OFF.
Note: Although the official documentation states that the skip-name-resolve parameter is Boolean, setting it as shown below will not work. The exact reason is unclear.
skip-name-resolve=0 or skip-name-resolve=FALSE
Correct method:
Method 1： skip-name-resolve=OFF Method 2：Comment out the parameter #skip-name-resolve
After changing the value of skip_name_resolve to OFF, restart the MySQL instance, and then we can verify the test results.
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 >
This solution requires changing parameters and restarting the MySQL instance, so it is generally not recommended.
Solution 2: Create a new account
As shown below, we create the following new account:
CREATE USER 'root'@'::1' IDENTIFIED BY '********'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1'; FLUSH PRIVILEGES;
Of course, if the error is MySQL Error 1045 (28000): Access denied for user ‘root’@’127.0.0.1’ (using password: YES), you can create the following user:
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '**********'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'; FLUSH PRIVILEGES;
After creating the account, MySQL Shell can connect without the above error, as shown below:
$ 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 >
Solution 3: Connect using the socket method
MySQL Shell can also connect using the socket method. The usual way is as follows:
mysqlsh -h localhost -u root -p -S /tmp/mysql.sock #Fill in the specific mysql.sock file according to your actual situation. or \connect root@localhost?socket=(/tmp/mysql.sock)
The test result is as follows:
$ 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 >
