gtid 1060 同步异常
gtid 1060 同步异常
1、报警信息如下
[Gome Cloud]
[falcon] PROBLEM P1
Endpoint:192.168.1.11
Hostgroup:DBA-MYSQL
Metric:Slave_SQL_Running/isSlave=1,port=3306,readOnly=0,type=mysql
Strategy:all(#3) 0==0
Note:MySQL复制SQL线程存活告警
Application:
Time:2022-02-23 15:13:00
2、具体从库同步异常信息
root@localhost: 15:20 [3306][(none)]>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.10 Master_User: slave Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000482 Read_Master_Log_Pos: 105203633 Relay_Log_File: mysql-relay.001815 Relay_Log_Pos: 84720712 Relay_Master_Log_File: mysql-bin.000482 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1060 Last_Error: Error 'Duplicate column name 'userCode'' on query. Default database: 'testdb'. Query: 'ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `updateTime`' Skip_Counter: 0 Exec_Master_Log_Pos: 84720542 Relay_Log_Space: 105204170 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: 1060 Last_SQL_Error: Error 'Duplicate column name 'userCode'' on query. Default database: 'testdb'. Query: 'ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `updateTime`' Replicate_Ignore_Server_Ids: Master_Server_Id: 10963306 Master_UUID: 896178f5-cb1b-11ea-949e-94292f719203 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: 220223 15:10:17 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566695524, 4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114-2079168115, 896178f5-cb1b-11ea-949e-94292f719203:1-102022 Executed_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566675454, 4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114, 896178f5-cb1b-11ea-949e-94292f719203:1-102022, 89757714-cb1b-11ea-949e-94292f719173:1-518 Auto_Position: 1 1 row in set (0.00 sec) ERROR: No query specified root@localhost: 15:20 [3306][(none)]>
3、查看表结构
root@localhost: 15:22 [7313][gomepush]>show create table test_tbl\G; *************************** 1. row *************************** Table: push_template Create Table: CREATE TABLE `push_template` ( `infoId` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(258) DEFAULT NULL, `templateDes` varchar(512) DEFAULT NULL, `chanel` varchar(32) DEFAULT NULL, `id` varchar(32) DEFAULT NULL, `title` varchar(258) CHARACTER SET utf8mb4 DEFAULT NULL, `content` varchar(1024) CHARACTER SET utf8mb4 DEFAULT NULL, `scheme` varchar(258) DEFAULT NULL, `showType` varchar(258) DEFAULT NULL, `pushImg` varchar(258) DEFAULT NULL, `centreImg` varchar(258) DEFAULT NULL, `expire` int(32) DEFAULT NULL, `userId` varchar(258) DEFAULT NULL, `state` varchar(32) DEFAULT NULL, `centreType` varchar(258) DEFAULT NULL, `createTime` datetime DEFAULT NULL, `updateTime` datetime DEFAULT NULL, `userCode` varchar(258) DEFAULT '' COMMENT '用户编号', PRIMARY KEY (`infoId`), UNIQUE KEY `id_unique` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 1 row in set (0.01 sec) ERROR: No query specified
已经存在该字段。
4、从库信息如下
Retrieved_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566707006, 4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114-2079168115, 896178f5-cb1b-11ea-949e-94292f719203:1-102022 Executed_Gtid_Set: 276909df-1105-11e6-81d8-6c92bf177322:495803940-566675454, 4f9ba386-1103-11e6-81cc-6c92bf176998:2079168114, 896178f5-cb1b-11ea-949e-94292f719203:1-102022, 89757714-cb1b-11ea-949e-94292f719173:1-518 Auto_Position: 1
从这里看，由于环境不干净，导致信息难以确认。所以极力推荐，从主库解析binlog日志来确定gtid的信息。
5、在主库中解析从库报错信息的日志
# 在主库中解析binlog日志 /app/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=84720542 /data/my3306/data/mysql-bin.000482 | more DELIMITER /*!*/; # at 84720542 #220223 15:10:16 server id 461597306 end_log_pos 84720590 CRC32 0x6166919b GTID [commit=yes] SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115'/*!*/; # at 84720590 #220223 15:10:16 server id 461597306 end_log_pos 84720792 CRC32 0xcc7102b3 Query thread_id=992913 exec_time=1 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1645600216/*!*/; SET @@session.pseudo_thread_id=992913/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autoco mmit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; ALTER TABLE `test_tbl` ADD COLUMN `userCode` varchar(258) NULL DEFAULT '' COMMENT '用户编号' AFTER `upda teTime` /*!*/;
重点关注： SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115'/*!*/;
6、解决办法
STOP SLAVE; SET @@SESSION.GTID_NEXT= '4f9ba386-1103-11e6-81cc-6c92bf176998:2079168115'; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC; START SLAVE;
