How to fix mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@'localhost' error?
Tags: fix, mysql, authentication
Published: 2021-11-30 04:00:19

Most of the time, this error comes because the “root” account of MySQL is set only to allow Unix Socket based authentication. So, enter the following commands using a terminal to change its authentication plugin either to Caching SHA-2 or Native authentication.

Login to MySQL CLI.

```
sudo mysql
```

First, we need to make sure that the root account uses the `auth_socket` plugin, for that run the following command.

```
SELECT host,user,authentication_string,plugin FROM mysql.user;
```

It should display something similar to this. Look at the plugin used by the root account, it should be `auth_socket`.

```
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | debian-sys-maint | $A$005$:\ddQ82h3oaB1+gf0FF.Uq5Gkaj3cVHbEWusZIIpJEBigM17KwLcFDcv6Z2zJ20 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | phpmyadmin       | $A$005$7.sK5b2|eI*r%2pnFO7wtVj57sqt54wR8FA3gsj6LEvvahn64BgPSD6Y5IyjzX8 | caching_sha2_password |
| localhost | root             |                                                                        | auth_socket           |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
```

Then run the following command to change the authentication plugin to Caching SHA-2 authentication.

```
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '';
```

Alternatively, you can use Native authentication as well, but use either.

```
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
```

Run this command to reload and apply changes.

```
FLUSH PRIVILEGES;
```

Again confirm whether changes have been successfully applied or not by running this command.

```
SELECT host,user,authentication_string,plugin FROM mysql.user;
```

You should be able to see something similar to this,

```
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | debian-sys-maint | $A$005$:\ddQ82h3oaB1+gf0FF.Uq5Gkaj3cVHbEWusZIIpJEBigM17KwLcFDcv6Z2zJ20 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | phpmyadmin       | $A$005$7.sK5b2|eI*r%2pnFO7wtVj57sqt54wR8FA3gsj6LEvvahn64BgPSD6Y5IyjzX8 | caching_sha2_password |
| localhost | root             |                                                                        | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
```

Now you can exit from MySQL CLI.

```
exit
```

Now you should be able to log in to the MySQL server with the ‘root’ account, the password will be blank. If you need to set a password, add it like this.

```
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'super-secret-password';
```