一个案例讲清楚 GRANT 操作的生效过程。
作者：杨彩琳，爱可生华东交付部 DBA，主要负责 MySQL 日常问题处理及 DMP 产品支持。爱好跳舞，追剧。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 1100 字，预计阅读需要 3 分钟。
问题背景
客户反馈，某业务测试环境的数据库主从同步断开。
登录到从库，执行 show slave status\G，发现 sql 线程没有工作了，具体报错为：
LAST_ERROR_MUMBER: 1410
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'c593bdc6-cd10-11ec-ac44-0050568a0cc2:2003275' at master log mysql-bin.00187, end_log_post 142 'You are not allowed to create a user with GRANT' on query. Default database: 'mysql'. Query: 'GRANT ALL PRIVILIEGES ON *.* TO 'p-dms-all'@100.104.%''
从提示可以看出是 GRANT 操作失败导致 sql 线程断开了。
经过与其他运维同事的沟通，了解到客户执行了创建用户并授权的操作。由于数据库中本身有一个未使用的用户，所以选择直接对 mysql.user 表的用户数据做 UPDATE 操作实现授权，从 MySQL 操作日志记录也可以看到如下操作：
![]()
尝试执行 start slave，从库的 sql 线程就已经正常工作了。GRANT 的操作也已经正常回放了。
也许你好奇这个过程中到底发生了什么，下面通过复现验证并解释该现象。
本地复现
现有一套 MySQL 8.0 的主从，数据库中已存在只读用户 test@'10.186.%'。
mysql> show grants for test@'10.186.%';
+------------------------------------------+
| Grants for test@10.186.% |
+------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`10.186.%` |
+------------------------------------------+
1 row in set (0.00 sec)
主库更改 test@'10.186.%' 用户的 host 并进行授权操作。
mysql> update mysql.user set host='%' where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all on *.* to test@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant all on *.* to test@'%';
Query OK, 0 rows affected (0.00 sec)
可以看到第一次 GRANT 操作失败了，再执行第二次可以成功。此时查看从库的复制状态，从库的 sql 线程已断开，稳定复现该问题。
mysql> show slave status\G
*************************** 1. row ***************************
Last_Errno: 1410
Last_Error: Coordinator stopped because there were error(s) in the worker(s).
The most recent failure being: Worker 1 failed executing transaction
'59c87cdc-9a47-11ee-b06e-02000aba394f:149931' at master log mysql-bin.000001,
end_log_pos 68067966. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
mysql> select * from performance_schema.replication_applier_status_by_worker limit 1\G
*************************** 1. row ***************************
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction
'59c87cdc-9a47-11ee-b06e-02000aba394f:149931' at master log mysql-bin.000001,
end_log_pos 68067966; Error 'You are not allowed to create a user with GRANT' on query.
Default database: ''. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'test'@'%''
![]()
- 如果使用账户管理语句更改授权表，服务器会注意到这些更改并立即将授权表加载到内存中。例如
GRANT,REVOKE,SET PASSWORD,RENAME USER 等操作。
- 如果使用
INSERT，UPDATE 或 DELETE 等语句直接修改授权表（不推荐），这些更改并不会加载到内存，除非告诉服务器重新加载授权表或者重启数据库。
flush-privileges 操作可以让服务器重新加载授权表。
官方文档的这段描述可以解释为什么在 UPDATE 操作之后，执行两次 GRANT 才能成功。
分析过程
UPDATE 操作之后并未将授权表的更改加载到内存，此时内存中并没有 test@'%' 用户，所以第一次 GRANT 操作失败了。
虽然返回执行失败了，但是第一次 GRANT 执行实际有将 UPDATE 的变更加载到内存（可以理解是隐式执行了 flush privileges，不过 flush privileges 并没有记录到 binlog 日志中），所以第二次 GRANT 执行成功，从库回放到 GRANT 时复制中断重新启动复制即可恢复也是这个逻辑。
GRANT 操作是不是原子性？
那么问题来了，从复现的现象来看，第一个 GRANT 操作虽然执行返回错误，但是实际上已进行了重载授权表的操作。所以，GRANT 操作失败后并没有完全回滚，看来 GRANT 操作不是一个原子性操作，可以来验证一下。
实验验证
![]()
总结
- GRANT 操作并不是一个原子性操作，不管执行成功与否，都会触发一个隐式重载授权表的行为。
- 在生产环境中需要规范用户创建及授权的操作，不推荐使用 DML 语句去直接变更
mysql.user 表，可能会引发其他的问题，若使用了 DML 语句进行变更，需要手工执行 flush privileges。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
✨ Github：https://github.com/actiontech/sqle
📚 文档：https://actiontech.github.io/sqle-docs/
💻 官网：https://opensource.actionsky.com/sqle/
👥 微信群：请添加小助手加入 ActionOpenSource
🔗 商业支持：https://www.actionsky.com/sqle
