MySQL – Access denied for user ‘root’@’localhost’
In case you want to connect to your local MySQL Database by using the root user and you encounter one of the the following error messages
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
You can solve this issue as follows.
Open the main config file /etc/mysql/my.cnf and add the following option under the [mysqld] section
This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the –skip-grant-tables option, it also disables remote connections by enabling skip_networking.
A restart from MySQL is normally not necessary but you can do it nevertheless
$ sudo systemctl restart mysql
From now on you should be able to connect to MySQL without password as follows.
Connect to the MySQL server using the mysql client; no password is necessary because the server was started with –skip-grant-tables.
Inside the MySQL client tell the server to reload the grant tables so that account-management statements like below the Alter User work.
mysql> flush privileges;
Then change the ‘root’@’localhost’ account password. Replace the password with the password that you want to use
mysql< ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Finally we will go back to our main config file /etc/mysql/my.cnf and remove or comment
and restart MySQL
$ sudo systemctl restart MySQL
Resetting the Root Password: Generic Instructions
What is mysql_native_password?