大家好，又见面了，我是你们的朋友全栈君。
mysql主从误重复创建用户报错1396处理
问题： 在mysql主从或者mysql分布式架构某些时候主从中断，经分析发现重复创建用户导致。
场景一、 如在日常的维护中，在主从架构或者分布式的架构中，要创建某些维护用户。比如创建运维用户，这个应该主库创建用户即可，同时主从库针对此用户设置免密登录。但是管理员同学不小心在主库从库同时执行了创建用户语句。
场景二、 如上线前中在分布式架构一主多从多分片部署运维用户，创建用户在主节点上执行，但是另外一部分同学不知道，正好做了同城切换测试，结果也出现了恰巧出现了重建用户情况。
如何处理： 此种情况是特殊情况，下面以传统主从模拟此报错和处理步骤。
初始化主从： 主库 mysql> show slave hosts ±———–±————-±—–±———–±————————————- | Server_id | Host | Port | Master_id | Slave_UUID | ±———–±————-±—–±———–±————————————- | 2624197123 | xx.x.xxx.xx6 | 3819 | 2624198899 | 4677ee4c-1404-11ea-80dd-00505699b577 | ±———–±————-±—–±———–±————————————-
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 global variables like ‘%gtid%’; ±———————————±—————————————— | Variable_name | Value | ±———————————±—————————————— | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 845133e9-1404-11ea-a37b-005056991dcd:1-4 | | gtid_executed_compression_period | 1000 |
然后从库报错： 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
从库查询gtid mysql> show global variables like ‘%gtid%’; ±———————————±——————————————————————————— | Variable_name | Value | ±———————————±——————————————————————————— | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 4677ee4c-1404-11ea-80dd-00505699b577:1, 845133e9-1404-11ea-a37b-005056991dcd:1-3 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 845133e9-1404-11ea-a37b-005056991dcd:1-3 | | session_track_gtids | OFF | ±———————————±——————————————————————————— 8 rows in set (0.00 sec)
查主库的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 global variables like ‘%gtid%’; ±———————————±—————————————– | Variable_name | Value | ±———————————±—————————————– | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 845133e9-1404-11ea-a37b-005056991dcd:1-4 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 845133e9-1404-11ea-a37b-005056991dcd:1-4 | | session_track_gtids | OFF | ±———————————±—————————————– 8 rows in set (0.00 sec)
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
mysql> show global variables like ‘%gtid%’; ±———————————±—————————————— | Variable_name | Value | ±———————————±—————————————— | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 845133e9-1404-11ea-a37b-005056991dcd:1-4 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | 845133e9-1404-11ea-a37b-005056991dcd:6#89 | | gtid_purged | | | session_track_gtids | OFF | ±———————————±—————————————— 8 rows in set (0.01 sec)
总结： 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;
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/169957.html原文链接：https://javaforall.cn
