You are using mariadb as an anonymous user

Question

All the privileges have been denied and all my databases seem to have been deleted. I get the error mentioned in the subject when I try to change the password. Initially there was no password set and this behaviour started after executing the following command

update mysql.user set password=password('newpass') where user='root';

I enter mysql using:

mysql -u root

Every command I try to execute gives me access denied error. I tried surfing on google but did not get a solution to solve the issue.

Answer 1

You must set the plugin of the user too, and flush privileges everytime

> update mysql.user set password=password('newpass') where user='root';
> flush privileges;
> update mysql.user set plugin='mysql_native_password' where user='root';
> flush privileges;

Then check you are not using an anonymous user when the database instance starts: in your /etc/my.cnf remove/comment any line which looks like this

skip-grant-tables #comment with #

And restart the database

service <db> restart