How to Fix mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@'localhost' Error
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';