After updating Ubuntu 16.04 with new updates, my java app could not connect to mysql anymore.
I tried to connect manually via terminal, and with Mysql Workbench, same result :

Access denied for user 'root'@'localhost' (using password: NO)

Usually I log in with user root and no password.
After some searching, I found that that's something to do with plugin auth

I followed the instruction on this thread, the second answer

sudo mysql -u root
use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;

But I didn't check my mysql version beforehand, which is 5.7.17.
I know, I tried to do quick and I did stupid.
I Should have done :

[mysql] update user set plugin='mysql_native_password' where User='root';

So now, I can't even launch any mysql prompt to correct my error.
I can't find if user "mysql" can connect via prompt ...
I can't remember if I have set other users: how can I verify this ?

Any idea somebody, please ?

  • Welcome on superuser. I fixed some link. Please edit your post if the link to the second answer is not correct (you can take the link from question or answer from the share text below the post). Remember to add a "double space" to have a newline... – Hastur Feb 15 '17 at 17:50

after searching a bit more, I stumbled upon this, which did the trick ;

sudo service mysql stop
sudo /usr/bin/mysqld_safe --skip-grant-tables &
mysql -h localhost

From there , I could connect to mysql. Then :

update user set plugin='mysql_native_password' where user = 'root';

After restarting the service I could connect with root user.

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.