gtid 1032同步异常处理
[root@dba_test_002 ~]# cat 3.sql # 在从库中执行 set sql_log_bin=0; insert into fudao_student_lable(uid,tid) values(1,11); insert into fudao_student_lable(uid,tid) values(2,22); insert into fudao_student_lable(uid,tid) values(3,33); insert into fudao_student_lable(uid,tid) values(4,44); set sql_log_bin=1; mysql> select * from fudao_student_lable; +----+-----+-----+ | id | uid | tid | +----+-----+-----+ | 1 | 1 | 11 | | 2 | 2 | 22 | | 3 | 3 | 33 | | 4 | 4 | 44 | +----+-----+-----+ 4 rows in set (0.00 sec) # 在主库中执行 insert into fudao_student_lable(uid,tid) values(1,11); insert into fudao_student_lable(uid,tid) values(2,22); insert into fudao_student_lable(uid,tid) values(3,33); insert into fudao_student_lable(uid,tid) values(4,44); mysql> select * from fudao_student_lable; +----+-----+-----+ | id | uid | tid | +----+-----+-----+ | 1 | 1 | 11 | | 2 | 2 | 22 | | 3 | 3 | 33 | | 4 | 4 | 44 | +----+-----+-----+ 4 rows in set (0.00 sec) # 在从库中查看 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.50.60 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 3272 Relay_Log_File: relay-bin.000051 Relay_Log_Pos: 2430 Relay_Master_Log_File: mysql-bin.000021 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: 1062 Last_Error: Could not execute Write_rows event on table test.fudao_student_lable; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000021, end_log_pos 2452 Skip_Counter: 0 Exec_Master_Log_Pos: 2220 Relay_Log_Space: 90228961 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: 1062 Last_SQL_Error: Could not execute Write_rows event on table test.fudao_student_lable; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000021, end_log_pos 2452 Replicate_Ignore_Server_Ids: Master_Server_Id: 1574144089 Master_UUID: 0501f340-0a94-11ea-ad2b-5254007dcbb3 Master_Info_File: mysql.slave_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: 191204 17:20:16 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:10-5440875 Executed_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:1-5440871, 137347eb-0a94-11ea-ad2b-525400dd43f8:1-531071 Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> # 分析主库中的日志 #191204 17:20:16 server id 1574144089 end_log_pos 2268 CRC32 0xe50d7ecf GTID [commit=yes] SET @@SESSION.GTID_NEXT= '0501f340-0a94-11ea-ad2b-5254007dcbb3:5440872'/*!*/; # at 2268 #191204 17:20:16 server id 1574144089 end_log_pos 2340 CRC32 0xa0a551a6 Query thread_id=3294 exec_time=0 error_code=0 SET TIMESTAMP=1575451216/*!*/; BEGIN /*!*/; # at 2340 #191204 17:20:16 server id 1574144089 end_log_pos 2404 CRC32 0x01555dc8 Table_map: `test`.`fudao_student_lable` mapped to num ber 70 # at 2404 #191204 17:20:16 server id 1574144089 end_log_pos 2452 CRC32 0x6cc26db4 Write_rows: table id 70 flags: STMT_END_F ### INSERT INTO `test`.`fudao_student_lable` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=0 is_null=0 */ ### @3=11 /* INT meta=0 nullable=0 is_null=0 */ # at 2452 SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440872'; BEGIN;COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE; show slave statusG; SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440873'; BEGIN;COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE; show slave statusG; SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440874'; BEGIN;COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE; show slave statusG; SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440875'; BEGIN;COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE; show slave statusG; #在从库中执行 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.50.60 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 3272 Relay_Log_File: relay-bin.000051 Relay_Log_Pos: 3482 Relay_Master_Log_File: mysql-bin.000021 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: 3272 Relay_Log_Space: 90228961 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: 1574144089 Master_UUID: 0501f340-0a94-11ea-ad2b-5254007dcbb3 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:10-5440875 Executed_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:1-5440875, 137347eb-0a94-11ea-ad2b-525400dd43f8:1-531071 Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> [root@dba_test_002 ~]#