作者：杨彩琳，爱可生华东交付部 DBA，主要负责 MySQL 日常问题处理及 DMP 产品支持。爱好跳舞，追剧。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 1100 字，预计阅读需要 3 分钟。
1问题背景
客户反馈，某业务测试环境的数据库主从复制断开。
登录到从库，执行 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 的操作也已经正常回放了。
也许你好奇这个过程中到底发生了什么，下面通过复现验证并解释该现象。
2本地复现
现有一套 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'@'%''
3官方说明[1]
- 如果使用账户管理语句更改授权表，服务器会注意到这些更改并立即将授权表加载到内存中。例如
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 操作不是一个原子性操作，可以来验证一下。
4实验验证
5总结
- GRANT 操作并不是一个原子性操作，不管执行成功与否，都会触发一个隐式重载授权表的行为。
- 在生产环境中需要规范用户创建及授权的操作，不推荐使用 DML 语句去直接变更
mysql.user
表，可能会引发其他的问题，若使用了 DML 语句进行变更，需要手工执行flush privileges
。
参考资料
[1]
官方说明: https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html
本文关键字：#MySQL# #GRANT# #事务#
故障分析 | 如何解决由触发器导致 MySQL 内存溢出？
故障分析 | 一个 Clickhouse 集群磁盘损坏恢复数据的案例
技术分享 | MySQL 隐式转换必知必会
技术分享 | MySQL VARCHAR 最佳长度评估实践
故障分析 | TCP 缓存超负荷导致的 MySQL 连接中断
故障分析 | 一则 MySQL 从节点 hung 死问题分析
新特性解读 | MySQL 8.0 支持对单个数据库设置只读
技术分享 | MySQL 授权表运维注意事项
✨ Github：https://github.com/actiontech/sqle
📚 文档：https://actiontech.github.io/sqle-docs/
💻 官网：https://opensource.actionsky.com/sqle/
👥 微信群：请添加小助手加入 ActionOpenSource
🔗 商业支持：https://www.actionsky.com/sqle
