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.

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.

  • A password has been set for root, but I can still log in to mysql console without a password in super user mode (i.e. root). But if I specify the root user in "normal" mode, the password set for that user is ignored and I still get an access denied. – Yanick Rochon Apr 11 at 20:46

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.