gtid多源复制1062故障处理
create database testdb214;
use testdb214;
CREATE TABLE `info_area` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
`rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID',
`pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID',
`level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_rel_id` (`rel_id`),
KEY `idx_pid` (`rel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='地区表';
INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10000', '北京', '10000', '10000', '1');
INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '三环以内', '100001000110001', '10001', '3');
INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10002', '三环到四环之间', '100001000110002', '10002', '2');
INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10003', '三环到四环之间', '100001000110003', '10003', '3');
INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10004', '三环到四环之间', '100001000110004', '10004', '4');
################################################# 1062 故障处理 #########################################
# 模拟1062错误
在从库中插入数据
set sql_log_bin=0;
insert into `testdb214`.`info_area` values (10001,'三环以内','100001000110001',10001,3);
set sql_log_bin=1;
mysql> show replica status for channel 'testdb214'\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 10.100.19.214
Source_User: mysqlsync
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-binlog.000001
Read_Source_Log_Pos: 5844
Relay_Log_File: relay-log-testdb214.000003
Relay_Log_Pos: 467
Relay_Source_Log_File: mysql-binlog.000001 # 非常重要
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: test.%
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b68143a-645e-11eb-b60f-fa163ea9cdf0:12' at master log mysql-binlog.000001, end_log_pos 5813. 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_Source_Log_Pos: 5389 # 非常重要
Relay_Log_Space: 6589
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b68143a-645e-11eb-b60f-fa163ea9cdf0:12' at master log mysql-binlog.000001, end_log_pos 5813. 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:
Source_Server_Id: 10402
Source_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 210203 14:37:52
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-12
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: testdb214
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
# 解决办法：(从上述信息，可以看出，错误提示已经不再像以前那么明显了，所以正确可能的办法，还是解析日志，确认引起错误的原因)
mysqlbinlog -vvv --base64-output=decode-rows --start-position=5389 mysql-binlog.000001 >01.binlog
# at 5389
#210203 14:37:52 server id 10402 end_log_pos 5468 CRC32 0x52564c87 GTID last_committed=11 sequence_number=12 rbr_only=yes original_committed_timestamp=1612334272398566 immediate_commit_timestamp=1612334272398566 transaction_length=455
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1612334272398566 (2021-02-03 14:37:52.398566 CST)
# immediate_commit_timestamp=1612334272398566 (2021-02-03 14:37:52.398566 CST)
/*!80001 SET @@session.original_commit_timestamp=1612334272398566*//*!*/;
/*!80014 SET @@session.original_server_version=80023*//*!*/;
/*!80014 SET @@session.immediate_server_version=80023*//*!*/;
SET @@SESSION.GTID_NEXT= '7b68143a-645e-11eb-b60f-fa163ea9cdf0:12'/*!*/;
# at 5468
#210203 14:37:52 server id 10402 end_log_pos 5548 CRC32 0x127f7b30 Query thread_id=76 exec_time=0 error_code=0
SET TIMESTAMP=1612334272/*!*/;
SET @@session.pseudo_thread_id=76/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113664/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 5548
#210203 14:37:52 server id 10402 end_log_pos 5663 CRC32 0xd831c640 Rows_query
# insert into `testdb214`.`info_area` values (10001,'三环以内','100001000110001',10001,3)
# at 5663
#210203 14:37:52 server id 10402 end_log_pos 5736 CRC32 0xbd3f6636 Table_map: `testdb214`.`info_area` mapped to number 311
# at 5736
#210203 14:37:52 server id 10402 end_log_pos 5813 CRC32 0x11bf5e98 Write_rows: table id 311 flags: STMT_END_F
### INSERT INTO `testdb214`.`info_area`
### SET
### @1=10001 /* INT meta=0 nullable=0 is_null=0 */
### @2='三环以内' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### @3='100001000110001' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */
### @4=10001 /* INT meta=0 nullable=0 is_null=0 */
### @5=3 /* INT meta=0 nullable=0 is_null=0 */
# at 5813
#210203 14:37:52 server id 10402 end_log_pos 5844 CRC32 0x92e0ec8d Xid = 615
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
在从库中删除掉这个重复的数据即可
mysql> select * from info_area where id=10001;
+-------+--------------+-----------------+-------+-------+
| id | name | rel_id | pid | level |
+-------+--------------+-----------------+-------+-------+
| 10001 | 三环以内 | 100001000110001 | 10001 | 3 |
+-------+--------------+-----------------+-------+-------+
1 row in set (0.00 sec)
set sql_log_bin=0;
delete from info_area where id=10001;
set sql_log_bin=1;
start replica sql_thread for channel 'testdb214';
mysql> show replica status for channel 'testdb214'\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 10.100.19.214
Source_User: mysqlsync
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-binlog.000001
Read_Source_Log_Pos: 5844
Relay_Log_File: relay-log-testdb214.000003
Relay_Log_Pos: 922
Relay_Source_Log_File: mysql-binlog.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: test.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 5844
Relay_Log_Space: 6589
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 10402
Source_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-12
Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: testdb214
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
