MySQL 8 Access denied for user 'root'@'localhost'

John D K -

Quick solution for MySQL 8 (and MySQL 5.7) error:

MySQL 8 Access denied for user 'root'@'localhost'

or

Access denied for user 'root'@'localhost' (using password: YES)

or

Access denied for user 'root'@'localhost' (using password: NO)

There is a security change since MySQL 5.7 which is causing the errors above. Even if you were able to login in the past and had access to the local MySQL database this problem might happen in future - if your current password is weak, not set or some system changes. You can find more information on this link: MySQL 8 set or reset user password or the resources below. In the article you can find several solutions about the problem and some general tips at the end.

In this article we will do only one which is the one I'm using most.

Once you lost access to the MySQL database or simply you never have it in order to access to this data base you need to do:

Solution for Access denied for user 'root'@'localhost'

  1. Open terminal on the machine where data base is - CTRL + ALT + T [*]
  2. login to MySQL database with - sudo mysql -u root [**]
  3. You will be asked for your OS root password
  4. Once you are connected to MySQL terminal you will see something like: mysql>
  5. Now you can reset your password with new one or remove the root password( not recommended:
  • no password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
  • password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
  1. Final step is restart of MySQL by: sudo systemctl stop mysql

Other similar solutions for Access denied for user 'root'@'localhost'

Another possible solution depending on the context might be:

  • open terminal
  • sudo -i and root password
  • mysql
  • and next steps of previous

or

use this for Debian, Ubuntu, Linux Mint when you lock your account or lost access to it:

  • in terminal type
sudo mysql --defaults-file=/etc/mysql/debian.cnf
  • once connected change the password

[*] For remote connections with ssh check the article above
[**] If you reset the password next time you need to use: sudo mysql -u root -p and the new password

General tips for Access denied for user 'root'@'localhost'

Some tips related to MySQL error:

Access denied for user 'root'@'localhost' (using password: YES)

  • Have backup of your database and working restore procedure
  • Use strong password and program which remembers the password like HeidiSQL
  • Be sure for the database address - I have friend of mine who mistaken dev and prod and reset password for the wrong database
  • for production databases use high level of security - sudo mysql_secure_installation or something similar
  • MySQL useful commands for check of users, version and security levels by(might differ depending on version and installation):
    • SELECT user,authentication_string,plugin,host FROM mysql.user;
    • mysql> SHOW VARIABLES LIKE 'version%';
    • SHOW VARIABLES LIKE 'validate%';
    • SHOW VARIABLES LIKE 'validate_password_policy';
    • SHOW STATUS LIKE 'validate%';

Reference