Access denied for user 'root'@'localhost'

Question

I went through all similar or identical questions (this and this, for ex.) but still can't solve the problem.

I am on Xenial and have MySQL 5.7.12. I do not remember setting any password during installation (if it asked, I provided empty one).

I tried to go through password reset using --skip-grant-tables, as described here, it updated the database row, but I still cannot login.

Reconfiguring with sudo dpkg-reconfigure mysql-server-5.7 doesn't do anything, I get this:

Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.12, use --force if you still need to run mysql_upgrade
$ sudo dpkg-reconfigure --force mysql-server-5.7
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.12, use --force if you still need to run mysql_upgrade

Any ideas?

terminal, `mysql -u root -p` – Simoroshka Jul 11 '16 at 15:55 — Simoroshka, Jul 11 '16 at 15:55

score 5 · Answer 1 · answered Jul 11 '16 at 16:06

MySQL 5.7 generates a random root password by itself on installation.

This was a new change in MySQL 5.7.4, and described in the release notes: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html

The installation process creates only a single root account, 'root'@'localhost', automatically generates a random password for this account, and marks the password expired. The MySQL administrator must connect as root using the random password and use SET PASSWORD to select a new password. (The random password is found in the .mysql_secret file in the home directory of the effective user running the script.)

I never find the .mysql_secret file, but the random password is also output in the MySQL error log (e.g. /var/log/mysqld.log on Linux). The notice looks like this:

2016-07-11T15:59:54.922316Z 1 [Note] A temporary password is generated for root@localhost: f14_W1lN7FfP

score 2 · Answer 2 · answered Jan 06 '17 at 05:11

You could either reinstall everything, which i did. I had initially installed using macos package installer. That didn't get me anywhere.

So uninstalled everything, then upgraded brew, and installed via brew. Running the mysql shell proved a messy affair. Ultimately what worked was mysql -u root -p.

I was running it on localhost, so turns out root password wasn't required. Weirdly, mysql -u root -p <password> threw the "access denied" message.

score 1 · Accepted Answer · edited Apr 13 '17 at 12:22

Proper re-installing helped. Removed everything associated (this instruction, but it is important to specify the version of mysql, e.g. apt-get --yes purge mysql-server-5.7 mysql-client-5.7, and I also deleted mysql-common and all dependencies to be sure).

Then installed again (apt-get install mysql-server mysql-client) and when it asked for a password, this time, I gave it one. It works now.

Access denied for user 'root'@'localhost'

3 Answers3

Linked