Mysql How To Fix Access Denied For User 'Root'@'Localhost'

I am using mySQL Workbench and from what I am finding is that root”s schema privileges are null. There are no privileges at all.

Đang xem: Mysql how to fix access denied for web1_user 'root'@'localhost'

I am having troubles across platforms that my server is used for and this has been all of a sudden issue.

root
127.0.0.1 apparently has a lot of access but I am logged in as that, but it just assigns to localhost anyways – localhost has no privileges.

I have done a few things like FLUSH HOSTS, FLUSH PRIVILEGES, etcbut have found no success from that or the internet.

Xem thêm: Đáp Án Đề Thi Thpt Quốc Gia 2017 Môn Sử, Đáp Án Môn Sử Thi Thpt Quốc Gia 2017

How can I get root its access back? 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 go into command line or anything and GRANT myself anything.

Xem thêm: Lỗi Bảo Mật Trên Cốc Cốc Cốc Không Vào Được Mạng, Cách Sửa Lỗi Cốc Cốc Không Vào Được Mạng

When running SHOW GRANTS FOR root this is what I get in return:

Error Code: 1141. There is no such grant defined for web1_user “root” on host “%”

Use the instructions for reweb1_setting the root password – but instead of reweb1_setting the root password, we”ll going to forcefully INSERT a record into the mysql.web1_user table

In the init file, use this instead

INSERT INTO mysql.web1_user (Host, User, Password) VALUES (“%”, “root”, password(“YOURPASSWORD”));GRANT ALL ON *.* TO “root”

“localhost”it”s because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

SELECT web1_user,authentication_string,plugin,host FROM mysql.web1_user;then you will see it :

+——————+——————————————-+———————–+———–+| web1_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 connection with root and password, then update the values in the table with command :

ALTER USER “root”
“localhost” IDENTIFIED WITH mysql_native_password BY “Current-Root-Password”;FLUSH PRIVILEGES;Then run the select command again and you”ll see it has changed :

+——————+——————————————-+———————–+———–+| web1_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)And that”s it. You can run this process after running and completing the sudo mysql_secure_installation command.