使用mysql的root账户授权却报错是什么鬼？Access denied for user ‘root’@’localhost’

背景：mysql版本为5.7.25，在创建账户并授权时出现如下错误：

mysql> grant all on xxx.* to xxx@'192.168.%' identified by 'xxx';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xxxx'
mysql> 

照理说，root用户应该有任何权限，那么为什么出现这个错误呢？ 查看当前用户为root@localhost，顺便查看了一下各个root账号的权限。如下所示：

mysql> select current_user() from dual;
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec

mysql> select host,user from user where user='root';
+-----------+----------+
| host      | user     |
+-----------+----------+
| %         | root     |
| 127.0.0.1 | root     |
| ::1       | root     |
| localhost | root     |
+-----------+----------+
7 rows in set (0.00 sec)

mysql>  show grants for root@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'            |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for root@'127.0.0.1';
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for root@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

如上所示，root@localhost账号没有WITH GRANT OPTION选项，关于WITH GRANT OPTION选项，如果想让授权的用户，也可以将这些权限授予给其他用户，需要选项 “WITH GRANT OPTION“ 。也就是说有这个选项就可以将权限传递给第三方。这也是上面root@localhost用户给其它用后授权报错的原因，如果以 root@127.0.0.1登录（此账号拥有WITHGRANT OPTION选项），创建用户并授权就不会有这个错误，如下所示：

# mysql -host 127.0.0.1 -u root -p
Enter password: 

mysql>  grant all on xxx.* to xxx@'192.168.%' identified by 'test1249';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

那么如何给没有授权账号的root账户给WITH GRANT OPTION选项呢？开始骚操作：

sudo systemctl stop mysql.service
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql -u root 
# 此时不需要输入密码
mysql> use mysql

mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
# 重点是下面这一句
mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL ON *.* TO 'root'@'localhost';
mysql> GRANT ALL ON *.* TO 'root'@'127.0.0.1';
mysql> FLUSH PRIVILEGES;

操作完毕，重启下mysql完事：

sudo systemctl start mysql.service