mysql主从误重复创建用户报错1396处理[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元售后保障童叟无欺

mysql主从误重复创建用户报错1396处理

问题：
在mysql主从或者mysql分布式架构某些时候主从中断，经分析发现重复创建用户导致。

场景一、
如在日常的维护中，在主从架构或者分布式的架构中，要创建某些维护用户。比如创建运维用户，这个应该主库创建用户即可，同时主从库针对此用户设置免密登录。但是管理员同学不小心在主库从库同时执行了创建用户语句。

场景二、
如上线前中在分布式架构一主多从多分片部署运维用户，创建用户在主节点上执行，但是另外一部分同学不知道，正好做了同城切换测试，结果也出现了恰巧出现了重建用户情况。

如何处理：
此种情况是特殊情况，下面以传统主从模拟此报错和处理步骤。

mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:

从库

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: testdb11
Master_User: repusr
Master_Port: 3819
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 568
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2624198899
Master_UUID: 845133e9-1404-11ea-a37b-005056991dcd
Master_Info_File: /mysqldata/mysql/data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

主库
create table mysql(id int);
create table mysq2(id int);
create table mysq3(id int);

mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 672
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-3
1 row in set (0.00 sec)

从库

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: testdb1
Master_User: repusr
Master_Port: 3819
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 672
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 885
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 672
Relay_Log_Space: 1086
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2624198899
Master_UUID: 845133e9-1404-11ea-a37b-005056991dcd
Master_Info_File: /mysqldata/mysql/data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-3
Executed_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

主库同时创建相同用户：
从库创建会话：
mysql> create user testing@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 365
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 4677ee4c-1404-11ea-80dd-00505699b577:1,
845133e9-1404-11ea-a37b-005056991dcd:1-3

主库创建会话：
mysql> create user testing@’%’;
Query OK, 0 rows affected (0.00 sec)

然后从库报错：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: testdb1
Master_User: repusr
Master_Port: 3819
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 883
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 885
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction ‘845133e9-1404-11ea-a37b-005056991dcd:4’ at master log mysql-bin.000001, end_log_pos 883. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 672
Relay_Log_Space: 1297
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: 1396
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction ‘845133e9-1404-11ea-a37b-005056991dcd:4’ at master log mysql-bin.000001, end_log_pos 883. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2624198899
Master_UUID: 845133e9-1404-11ea-a37b-005056991dcd
Master_Info_File: /mysqldata/mysql/data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 191225 20:15:51
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-4
Executed_Gtid_Set: 4677ee4c-1404-11ea-80dd-00505699b577:1,
845133e9-1404-11ea-a37b-005056991dcd:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

查主库的845133e9-1404-11ea-a37b-005056991dcd:1-4事务：
[mysql@testdb1:/mysqllog]>mysqlbinlog -vv mysql-bin.000001
SET @@SESSION.GTID_NEXT= ‘845133e9-1404-11ea-a37b-005056991dcd:4’/!/;
CREATE USER ‘testing’@’%’ IDENTIFIED WITH ‘mysql_native_password’
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;
#End of log file

查询从库4677ee4c-1404-11ea-80dd-00505699b577:1事务
[mysql@testdb2:/mysqllog]>mysqlbinlog -vv mysql-bin.000001
SET @@SESSION.GTID_NEXT= ‘4677ee4c-1404-11ea-80dd-00505699b577:1’/!/;
CREATE USER ‘testing’@’%’ IDENTIFIED WITH ‘mysql_native_password’
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;
End of log file

确认是从库已经创建test@’%‘用户，要回放主库创建test@’%’创建用户动作就会失败。

如何处理：
从库
mysql> stop slave;
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> set global gtid_purged=‘845133e9-1404-11ea-a37b-005056991dcd:1-4’;
Query OK, 0 rows affected (0.00 sec)

mysql>start slave;

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: testdb1
Master_User: repusr
Master_Port: 3819
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 883
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 883
Relay_Log_Space: 1597
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2624198899
Master_UUID: 845133e9-1404-11ea-a37b-005056991dcd
Master_Info_File: /mysqldata/mysql/data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-4
Executed_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

从库
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-4
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

主库
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 883
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 845133e9-1404-11ea-a37b-005056991dcd:1-4
1 row in set (0.00 sec)

ERROR:
No query specified

总结：
1、确认从库报错1396
Last_Errno: 1396
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction ‘845133e9-1404-11ea-a37b-005056991dcd:4’ at master log mysql-bin.000001, end_log_pos 883. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
2、通过mysqlbinlog 查询相关的主库和从库gitd事务，确认是重复创建用户导致。
3、从库处理步骤：
mysql> stop slave;
mysql> reset master;
mysql> set global gtid_purged=‘845133e9-1404-11ea-a37b-005056991dcd:1-4’;
mysql>start slave;

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/169957.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权正版激活】： 官方授权正版激活支持Jetbrains家族下所有IDE 使用个人JB账号...