MySQL删除root@localhost后权限的恢复

1.学习过程中给root授权了远程登陆

mysql> grant all privileges on *.* to root@'%' identified by 'Password@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.查看用户状态是这样的

mysql> select user,host from mysql.user;
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| json      | %            |
| json3     | %            |
| json4     | %            |
| root      | %            |
| work      | 192.168.43.0 |
| json2     | localhost    |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
8 rows in set (0.00 sec)

我以为 %就包含了   root@localhost  结果我就把 这个用户删除了，然后我在本地给新创建的用户授权就报错了

mysql> grant select on *.* to json3@'%';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

3.我开始恢复这个root@localhost

首先查看root@'%'这个用户有没有grant权限

mysql> select * from mysql.user where user='root' and host='%'\G

查看有没有grant这个权限，没有给赋权

mysql>  update mysql.user set Grant_priv='Y',Super_priv='Y' where user='root' and host='%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

flush privileges;

4.给root@localhost赋权，从其他主机登陆

grant all on *.*  to root@"localhost" identified by "密码"；

查看，验证可以授权了

update mysql.user set Grant_priv='Y',Super_priv='Y' where user='root' and host='localhost';

mysql> select user,host from mysql.user;
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| json      | %            |
| json3     | %            |
| json4     | %            |
| root      | %            |
| work      | 192.168.43.0 |
| json2     | localhost    |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
8 rows in set (0.00 sec)

mysql> grant select on *.* to json3@'%';
Query OK, 0 rows affected (0.03 sec)