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 status\G;
*************************** 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 status\G;
SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440873';
BEGIN;COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
show slave status\G;
SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440874';
BEGIN;COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
show slave status\G;
SET GTID_NEXT='0501f340-0a94-11ea-ad2b-5254007dcbb3:5440875';
BEGIN;COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
show slave status\G;
#在从库中执行
mysql> show slave status\G;
*************************** 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 ~]#
