8

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.

anish samant
  • 183
  • 1
  • 4
  • 13

2 Answers2

21

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
Goufalite
  • 2,253
  • 3
  • 17
  • 29
  • Can't believe there is no mention of this in the MySQL docs. Or is this specific to MariaDB? – gosuto May 05 '19 at 19:16
  • 1
    life-saving advice sir!!! the "newpass" is your own password, but the plugin "mysql_native_password" is a hardcoded plugin which cannot be change. most important you must highlight all these are executed as non-root user id, running the command "mysql -u root -p". – Peter Teoh Feb 29 '20 at 05:48
  • 1
    you saved my day with the command 'update mysql.user set plugin='mysql_native_password' where user='root';' – dritan Mar 19 '20 at 11:46
  • Use [ALTER USER](https://mariadb.com/kb/en/alter-user/) and [SET PASSWORD](https://mariadb.com/kb/en/set-password/) rather than these underlying low level historical hacks especially as these [won't work in MariaDB-10.4+](https://stackoverflow.com/questions/64841185/error-1356-hy000-view-mysql-user-references-invalid-tables-or-columns-o/64841540#64841540) – danblack Jan 22 '23 at 23:40
-1
> select version();
+----------------------------------+
| version()                        |
+----------------------------------+
| 10.1.48-MariaDB-0ubuntu0.18.04.1 |
+----------------------------------+



# systemctl stop mariadb
# mysqld --skip-grant-tables  &
# mysql

>  flush privileges;
> update mysql.user  
       set authentication_string=password('789'),  
       plugin='mysql_native_password'  
       where user='root' and host='localhost';
>exit

# kill -15 $(ps aux | grep mysqld | grep -v grep | awk '{print $2}')
# systemctl start mariadb
# mysql -u root -p
Enter password: 789

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

If mariadb >=10.2 then use ALTER USER ( alter user announcement)

Alex
  • 837
  • 8
  • 9