MySQL 主从复制遇到 1590 报错
👉 本文共约1871个字，系统预计阅读时间或需8分钟。
1故障描述
DMP[1] 收到告警：从库的 SQL 线程停止工作，MySQL 版本为 5.7.32，登录到从库查看复制信息报错如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event …… Last_Errno: 1590 Last_Error: The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. Skip_Counter: 0 Exec_Master_Log_Pos: 12531 Relay_Log_Space: 69304 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1590 Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. …… |
从库错误日志信息如下：
1 2 3 | [ERROR] Slave SQL for channel '': The incident LOST_EVENTS occured on the master. Message: REVOKE/GRANT failed while granting/revoking privileges in databases. Error_code: 1590 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 12531. |
解析对应 Binlog 信息如下：
1 2 | # Incident: LOST_EVENTS RELOAD DATABASE; # Shall generate syntax error |
主库错误信息如下：
1 | [ERROR] REVOKE/GRANT failed while granting/revoking privileges in databases. An incident event has been written to the binary log which will stop the slaves. |
客户反馈执行了一些授权操作然后复制就出现报错，执行的语句如下：
1 2 3 4 | mysql> create user test@'%',app@'%' identified by 'Root@123'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> grant all on test.* to test@'%',app@'%'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements |
2故障分析
根据以上报错信息可知：在做权限变更时发生了错误，主库在 binlog 里写一条 INCIDENT_EVENT，备库在解析到 INCIDENT_EVENT 就直接报错。
那在什么情况下执行授权语句会导致主库在 binlog 写 INCIDENT_EVENT 呢？
当权限变更操作只处理了一部分并发生错误时，主库会在 binlog 里写一条 INCIDENT_EVENT。
那什么情况下会发生权限变更只处理一部分而有一部分没处理完呢？
下面举例说明两种相关场景。
MySQL 5.7 的问题
在 MySQL 5.7 里使用 GRANT 语句新建用户，其中有部分权限有问题。
使用 GRANT 创建 test 用户（MySQL 8.0 版本已经不支持使用 GRANT 创建用户）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> grant select,insert,file on test.* to test@'%' identified by 'Q1w2e3E$'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES mysql> select user,host from mysql.user where user='test' and host='%'; +------+--------+ | user | host | +------+--------+ | test | % | +------+--------+ 1 row in set (0.00 sec) mysql> show grants for test@'%'; +--------------------------------------------+ | Grants for test@% | +--------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' | +---------------------------------------------+ 1 row in set (0.00 sec) |
在创建用户时对 test 库授予 SELECT、INSERT、FILE 权限，因 FILE 权限不能授予某个数据库而导致语句执行失败。但最终结果是：test@'%' 创建成功，授权部分失败。从上面的测试可知，使用 GRANT 创建用户其实是分为两个步骤：创建用户和授权。权限有问题并不影响用户的创建，上述语句会导致主库在 binlog 写 INCIDENT_EVENT，从而导致主从复制报错。
GRANT 对两个用户同时授权
使用一条 GRANT 语句，同时给 test@'10.186.63.5' 与 test@'10.186.63.29' 用户授权，其中 test@'10.186.63.5' 用户存在，而 test@'10.186.63.29' 不存在。
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> create user test@'10.186.63.5' identified by '123'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on test.* to test@'10.186.63.5',test@'10.186.63.29'; ERROR 1133 (42000): Can't find any matching row in the user table mysql> show grants for test@'10.186.63.5'; +----------------------------------------------------------+ | Grants for test@10.186.63.5 | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'10.186.63.5' | | GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'10.186.63.5' | +----------------------------------------------------------+ 2 rows in set (0.00 sec) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
