MySql can not grant privileges to root

Question

I ran command grant all privileges on *.* to 'root'@'%' identified by 'root';

And it keep showing following error

11:43:51 grant all privileges on . to 'root'@'%' identified by 'root' Error Code: 1045. Access denied for user 'root'@'%' (using password: YES) 0.00045 sec

I also checked User table on Mysql it looks like below.

'%', 'root', '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', ?, ?, ?, '0', '0', '0', '0', 'mysql_native_password', '', 'N'
'localhost', 'root', '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', ?, ?, ?, '0', '0', '0', '0', '', '', 'N'

I do not know what is wrong with this? :<

How can I fix this without re-install MySQL. (Since, it worked well before what happened today)

Thanks for your answer :b

====================== Edit =================

Some of history, but I am not sure this will be useful or not.

Before I did this.

I import a dump. When I trying to access, some of procedures could not execute since, definer is different. So I did like this.

1. made a new schema and re-import the dump
2. create a user in mysql database and flush privileges;

And now I want to give privileges to new user. At first, I was trying with workbench(GUI of MySQL Client).

But when I trying to give privileges, it show error 1044, which I am facing now.

Now what can I do to fix this?

Answer 1

The error message Access denied for root@% suggests you are trying to login as root remotely and grant yourself these privileges. You will need to log in from localhost and perform this operation for it to be successful.

It should be pointed out that allowing unrestricted root access from remote hosts is very rarely a good idea.