Access denied for user 'root'@'localhost' (using password: yes) - No permission?

Question

I keep getting this error.

I am using mySQL Workbench and I found that root's schema permissions are empty. There are no privileges at all.

I'm having issues on various platforms I'm using my server on, and this is a problem that pops up out of the blue.

root@127.0.0.1 Apparently there is a lot of access, but I log in like this, but it is only assigned to localhost - localhost has no permissions.

I did some things like FLUSH HOSTS, FLUSH PRIVILEGES etc. But no success from that method or from the internet.

How can I restore root access? I find this frustrating because when I look around people expect you to "have access" but I don't have access so I can't get into the command line or anything and GRANT do it myself anything.

Run SHOW GRANTS FOR root This is what I get in return:

Error code: 1141. No such authorization is defined for user 'root' Host "%"

Answer 1

If you encounter the same problem in MySql 5.7.:

Access denied for user 'root'@'localhost'

This is because MySql 5.7 allows socket connections by default, which means you only need to use sudo mysql to connect. If you run sql:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Then you will see it:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connections using root and password, update the values ​​in the table using the command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you will see it has changed:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

That's it. You can run this procedure after running and completing the sudo mysql_secure_installation command.

For mariadb, use

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');

set password. For more information please visit https://mariadb.com/kb/en/set-password/

Answer 2

UsageInstructions for resetting the root password - But instead of resetting the root password, we will force insert a record into the mysql.user table

In initialization files, use this instead of

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;