MySQL Error: : 'Access denied for user 'root'@'localhost'

Question

Consider:

./mysqladmin -u root -p** '_redacted_'   

Output (including typing the password):

Enter password:

mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'localhost' (using password: YES)'

How can I fix this?

Answer 1

All solutions I found were much more complex than necessary and none worked for me. Here is the solution that solved my problem. There isn't any need to restart mysqld or start it with special privileges.

sudo mysql  -- for MySQL ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';  -- for MariaDB ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root'); 

With a single query we are changing the auth_plugin to mysql_native_password and setting the root password to root (feel free to change it in the query).

Now you should be able to log in with root. More information can be found in MySQL documentation or MariaDB documentation.

(Exit the MySQL console with Ctrl + D or by typing exit.)

Answer 2

1. Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
2. Add skip-grant-tables under [mysqld]
3. Restart MySQL
4. You should be able to log in to MySQL now using the below command mysql -u root -p
5. Run mysql> flush privileges;
6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
7. Go back to /etc/my.cnf and remove/comment skip-grant-tables
8. Restart MySQL
9. Now you will be able to login with the new password mysql -u root -p