关于 mysql5.7 中 创建一个用户 并为其 grant 权限 操作失败的问题

SQL:

-- 创建授权 canal 账号  具有 slave 权限
-- 查看密码策略状态
select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';
-- 查看密码策略
show variables like 'validate_password%';
-- 修改密码策略
set global validate_password_mixed_case_count=0;
set global validate_password_number_count=0;
set global validate_password_special_char_count=0;
set global validate_password_length=4;
set global validate_password_policy=low;
-- 创建用户
create user canal identified by 'canal';  
-- 赋予权限
grant select, replication slave, replication client on *.*  to 'canal'@'%' identified by 'canal';
-- 此时报错：Access denied for user 'root'@'%' (using password: YES)
-- 其他尝试
grant all privileges on *.*  to 'canal'@'%' identified by 'canal';
grant all privileges on *.*  to 'canal'@'%' ;
-- 查询 root 权限
show grants for  'root'@'%' ;
-- all privileges 但是仍然失败，bsbdqj，查询 mysql.user 表
select user,host,Grant_priv from mysql.user;
-- 发现非本地 root 无权授权 定位问题
-- 本地 root 登录失败，重置密码
update mysql.user set authentication_string = password('root') where user='root' and host = 'localhost';
-- 仍然登录失败，刷新权限
flush privileges;
-- 登录成功
select * from mysql.user where user='root' and host='localhost' \G;
-- hys！
show grants for  'canal'@'%';
+-----------------------------------+
| Grants for canal@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'canal'@'%' |
+-----------------------------------+

grant all privileges on *.*  to 'canal'@'%' with grant option;
select * from mysql.user where user='canal' \G;
revoke super on *.* from canal@'%'; 
select * from mysql.user where user='canal' \G;
revoke all on *.* from 'canal'@'%' ;
select * from mysql.user where user='canal' \G;
revoke grant option on *.* from canal@'%'; 
drop user 'canal'@'%';
create user canal identified by 'canal';  
grant all privileges on *.*  to 'canal'@'%';
select * from mysql.user where user='canal' \G;
revoke all on *.* from 'canal'@'%' ;
-- revoke all privileges on *.* from 'canal'@'%' ;

-- drop 会权限表的内容，而 delete 后需要执行 flush privileges，否则下次 create 创建用户会报错。
delete from user where user='canal' and host='localhost';
flush privileges;
grant select, replication slave, replication client on *.*  to 'canal'@'%' identified by 'canal';
show warnings;
select authentication_string from mysql.user where user='canal';
+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
update mysql.user set authentication_string = password('canal')  where user='canal';
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
alter user canal@'%' identified by 'canal'; 
set global validate_password_mixed_case_count=1;
set global validate_password_number_count=1;
set global validate_password_special_char_count=1;
set global validate_password_length=8;
set global validate_password_policy=medium