Mysql主从同步1396错误解决办法

错误信息

Last_Errno: 1396
Last_Error: Error ‘Operation CREATE USER failed for ‘backup‘@‘%‘‘ on query. Default database: ‘‘. Query: ‘CREATE USER ‘repl‘@‘%‘ IDENTIFIED WITH ‘mysql_native_password‘ AS ‘*369D23DSF3ASDF34245DF32435DDFECR23456778‘‘

解决办法

# 查看同步错误的语句位置
mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1396;
# 会有类似这样的输出
| | 0 | NULL | OFF | 5c8e4590-35a6-1990-ab92-589753512acd:1 | 1396 | Error ‘Operation CREATE USER failed for 
‘backup‘@‘%‘‘ on query. Default database: ‘‘. Query: ‘CREATE USER ‘backup‘@‘%‘ IDENTIFIED WITH ‘mysql_native_password‘ 
AS ‘*369D23DSF3ASDF34245DF32435DDFECR23456778‘‘ | 2024-05-05 13:33:07 |

记住这个值：

5c8e4590-35a6-1990-ab92-589753512acd:1

操作步骤

mysql> stop slave;
​
Query OK, 0 rows affected (0.00 sec)
​
mysql> set @@session.gtid_next=‘5c8e4590-35a6-1990-ab92-589753512acd:1‘;
​
Query OK, 0 rows affected (0.00 sec)
​
mysql> begin;
​
Query OK, 0 rows affected (0.00 sec)
​
mysql> commit;
​
Query OK, 0 rows affected (0.00 sec)
​
mysql> set @@session.gtid_next=automatic;
​
Query OK, 0 rows affected (0.00 sec)
​
mysql> start slave;
mysql> show slave status\G;

如果不出意外的话就不会出意外了