How to solve My SQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Question

I know there are already similar questions and I tried each of the solutions but somehow nothing seems to help. Maybe anyone has an idea what is going on. I am very new to the programming world and don't understand yet what's behind all the commands, but tried my best to solve it myself.

I installed MySQL via homebrew on a mac. As long as the password was not set up and I could just press enter when asked for password all was fine. Then I changed the password via this command mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';

After doing this, I could not log in any more and always get the ERROR 1045 (28000). I reinstalled SQL and also downloaded another version via Then I installed SQL via the MySQL community server, but this also did not change anything.

But by installing via MySQL community I could go to the system preferences and reset the password (which also did not work via the terminal) - but again no luck.

I would appreciate any ideas. Thanks.

Answer 1

The issue is likely due to socket authentication being enabled for the root user by default when no password is set, during the upgrade to ubuntu 16.04.

The solution is to revert back to native password authentication. You can do this by logging in to MySQL using socket authentication by doing:

sudo mysql -u root

Once logged in:

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

which will revert back to the native (old default) password authentication.

Now use password as the password whenever required by MySQL.