Cannot login to MySQL, access denied

Question

After a crash, I re-installed Ubuntu on my dev machine over the previous installation, and after I re-installed all the necessary packages (including mysql-server and mysql-client) I went on restoring the dev database. Unfortunately, I am unable to connect.

This works :

$ sudo su
# mysql -u root

this does not

$ mysql -u root -p

• I have tried purging mysql-* and reinstalling everything.
• I have tried reconfiguring (i.e. sudo dpkg-reconfigure mysql-server-5.7)
• I have tried manually reseting the password using UPDATE queries.
• I have tried everything here (ecept that the table user does not have a password column, but an authentication_string one)

Please, can someone help me get this thing working?

---

** Edit **

mysql> SELECT user, host, authentication_string, password_expired, account_locked FROM user WHERE user = 'root';
+------------------+-----------+-------------------------------------------+------------------+----------------+
| user             | host      | authentication_string | password_expired | account_locked |
+------------------+-----------+-----------------------+------------------+----------------+
| root             | localhost | *<password hash>      | N                | N              |
| root             | %         | *<password hash>      | N                | N              |
+------------------+-----------+-----------------------+------------------+----------------+
5 rows in set (0.00 sec)

Both root users have the same password, set using PASSWORD('password'), and all privileges have been flushed, then the service was restarted.

Contrary to what the documentation says, running sudo dpkg-reconfigure mysql-server-5.7 does not prompt me to specify a new password for root. The command terminates successfully, but I am never asked to specify a password.

Answer 1

I found my answer, here.

Basically, the plugin value for the user root@'localhost' was not mysql_native_password.

UPDATE user SET plugin = 'mysql_native_password' WHERE user = 'root';
FLUSH PRIVILEGES;

worked, and I was finally able to log in without super user privileges.

Answer 2

MySQL permissions were probably not restored. The fact that you can log into the server without any permissions suggests that you need to set the root password, and add any other users that need to be used with their respective permissions.