MySQL 中 GRANT 操作会引起复制中断吗？

一个案例讲清楚 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 操作不是一个原子性操作，可以来验证一下。

实验验证

总结

1. GRANT 操作并不是一个原子性操作，不管执行成功与否，都会触发一个隐式重载授权表的行为。
2. 在生产环境中需要规范用户创建及授权的操作，不推荐使用 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