0

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 ?

Community
  • 1
Manolo de la Vega
  • 101

1 Answers1

0

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.

Manolo de la Vega
  • 101