MySQL主从异常处理Errno: 1292-REPLICATE_WILD_IGNORE_TABLE的使用
- 一、Slave错误信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.172.64.161 Master_User: dbsync Master_Port: 27415 Connect_Retry: 60 Master_Log_File: mysql_bin.014571 Read_Master_Log_Pos: 22657121 Relay_Log_File: relay-bin.043708 Relay_Log_Pos: 137275030 Relay_Master_Log_File: mysql_bin.014569 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: 1292 Last_Error: Error 'Truncated incorrect INTEGER value: ' 2023-01-01 '' on query. Default database: 'MDM' . Query: 'insert into CALENDAR( PERIOD_ID, PERIOD_TYPE_ID, PERIOD_NAME, CALENDAR_DATE , CALENDAR_YEAR, HALF_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, YEAR_WEEK, YEAR_DAY, MONTH_DAY, WEEK, PUBLIC_HOLIDAY, WORKING_DAY, WEEKEND, MONTH_END, QUARTER_END, YEAR_END, YOY, MOM, HALF_YEAR_END, CREATE_DATE, UPDATE_DATE )( select convert(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%Y%m%d' ),UNSIGNED) as PERIOD_ID, 1 as PERIOD_TYPE_ID, CONCAT(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%Y' ), '年' ,DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedis Skip_Counter: 0 Exec_Master_Log_Pos: 137274817 Relay_Log_Space: 2171129201 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: 1292 Last_SQL_Error: Error 'Truncated incorrect INTEGER value: ' 2023-01-01 '' on query. Default database: 'MDM' . Query: 'insert into CALENDAR( PERIOD_ID, PERIOD_TYPE_ID, PERIOD_NAME, CALENDAR_DATE , CALENDAR_YEAR, HALF_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, YEAR_WEEK, YEAR_DAY, MONTH_DAY, WEEK, PUBLIC_HOLIDAY, WORKING_DAY, WEEKEND, MONTH_END, QUARTER_END, YEAR_END, YOY, MOM, HALF_YEAR_END, CREATE_DATE, UPDATE_DATE )( select convert(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%Y%m%d' ),UNSIGNED) as PERIOD_ID, 1 as PERIOD_TYPE_ID, CONCAT(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%Y' ), '年' ,DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedis Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: ef92423d-488b-11ec-a1ae-525493542058 Master_Info_File: /u01/mysql/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: 220727 16:34:55 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified |
- 二、查询表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | mysql> show create table CALENDAR\G; *************************** 1. row *************************** Table: CALENDAR Create Table: CREATE TABLE `CALENDAR` ( `PERIOD_ID` int(11) NOT NULL, `PERIOD_TYPE_ID` int(11) DEFAULT NULL, `PERIOD_NAME` varchar(64) NOT NULL, `CALENDAR_DATE` date NOT NULL, `CALENDAR_YEAR` int(11) DEFAULT NULL, `HALF_YEAR` int(11) DEFAULT NULL, `CALENDAR_QUARTER` int(11) DEFAULT NULL, `CALENDAR_MONTH` int(11) DEFAULT NULL, `YEAR_WEEK` int(11) DEFAULT NULL, `QUARTER_WEEK` int(11) DEFAULT NULL, `MONTH_WEEK` int(11) DEFAULT NULL, `YEAR_DAY` int(11) DEFAULT NULL, `QUARTER_DAY` int(11) DEFAULT NULL, `MONTH_DAY` int(11) DEFAULT NULL, `WEEK` int(11) DEFAULT NULL, `QUARTER` int(11) DEFAULT NULL, `SUMMARY_DAYS` int(11) DEFAULT NULL, `SUMMARY_WORK_DAYS` int(11) DEFAULT NULL, `PUBLIC_HOLIDAY` varchar(1) DEFAULT NULL, `NATIONAL_HOLIDAY` varchar(1) DEFAULT NULL , `COMPANY_HOLIDAY` varchar(1) DEFAULT NULL, `WORKING_DAY` varchar(8) DEFAULT NULL, `WEEKEND` varchar(8) DEFAULT NULL, `MONTH_END` varchar(8) DEFAULT NULL, `QUARTER_END` varchar(8) DEFAULT NULL, `YEAR_END` varchar(8) DEFAULT NULL, `HALF_YEAR_END` varchar(8) DEFAULT NULL, `DESCRIPTION` varchar(512) DEFAULT NULL, `PERIOD_START_DATE` date DEFAULT NULL, `PERIOD_END_DATE` date DEFAULT NULL, `YOY` int(11) DEFAULT NULL, `MOM` int(11) DEFAULT NULL, `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `UPDATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `LOAD_TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `CALENDAR_DATE_21` date DEFAULT NULL, PRIMARY KEY (`PERIOD_ID`) USING BTREE, KEY `IDX_CALENDAR_DATE` (`CALENDAR_DATE`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec) ERROR: No query specified mysql> |
- 三、读取relay log日志,确定执行错误的SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | /home/mysql/base/bin/mysqlbinlog --no-defaults --database=MDM -- base64 -output=decode-rows -vv --start-position=137275030 relay-bin.043708 > /data/relay-bin .043708.txt # at 137275190 #220727 16:34:55 server id 1 end_log_pos 137279129 CRC32 0xcf5a44c9 Query thread_id=5622166 exec_time=0 error_code=0 use `MDM`/*!*/; SET TIMESTAMP=1658910895/*!*/; insert into CALENDAR(^M PERIOD_ID,^M PERIOD_TYPE_ID,^M PERIOD_NAME,^M CALENDAR_DATE ,^M CALENDAR_YEAR,^M HALF_YEAR,^M CALENDAR_QUARTER,^M WORKING_DAY,^M WEEKEND,^M MONTH_END,^M YOY,^M MOM,^M HALF_YEAR_END,^M CREATE_DATE,^M )(^M select ^M convert(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%Y%m%d' ),UNSIGNED) as PERIOD_ID,^M 1 as PERIOD_TYPE_ID,^M NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) as CALENDAR_DATE,^M YEAR( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) as CALENDAR_YEAR,^M ( case when DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ), '%m' )<= '06' then 1 else 2 end) as HALF_YEAR,^M QUARTER( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) as CALENDAR_QUARTER,^M MONTH( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) as CALENDAR_MONTH,^M convert(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) , '%u' ),UNSIGNED) as YEAR_WEEK,^M convert(DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) , '%j' ),UNSIGNED) as YEAR_DAY,^M DAY( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) as MONTH_DAY,^M WEEK( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) ) as WEEK,^M NAME_CONST( 'is_holiday' ,_utf8 'Y' COLLATE 'utf8_general_ci' ) as PUBLIC_HOLIDAY,^M ^M ( case NAME_CONST( 'is_holiday' ,_utf8 'Y' COLLATE 'utf8_general_ci' ) when 'Y' then 'N' when 'N' then 'Y' end) as WORKING_DAY,^M NAME_CONST( 'is_weekend' ,_utf8 'N' COLLATE 'utf8_general_ci' ) as WEEKEND,^M ^M ( case when MONTH( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ))<MONTH(date_sub( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ),interval -1 day)) then 'Y' else 'N' end) as MONTH_END,^M ( case when YEAR( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ))*100+QUARTER( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) <YEAR(date_sub( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ),interval -1 day))*100+QUARTER( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' )) then 'Y' else 'N' end) as QUARTER_END,^M ( case when YEAR( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ))<YEAR(date_sub( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ),interval -1 day)) then 'Y' else 'N' end) as YEAR_END,^M ^M CONVERT(date_sub( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ),interval 12 MONTH),UNSIGNED),^M CONVERT(date_sub( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ),interval 1 DAY),UNSIGNED),^M ( case when DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) , '%m%d' ) = '0630' or DATE_FORMAT( NAME_CONST( 'date_now' ,_latin1 '2024-01-01' COLLATE 'latin1_swedish_ci' ) , '%m%d' ) = '1231' then 'Y' else 'N' end) as HALF_YEAR_END,^M NOW() as CREATE_DATE,^M NOW() as UPDATE_DATE^M ^M ) /*!*/; 执行记录共计2559条 insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( insert into CALENDAR( |
[mysql@p0-tkhibdp-bdp-jtdsj01 data]$ cat relay-bin.043708.txt |grep "insert into CALENDAR"|wc -l
2559
- 四、该表无新增数据,处理过程,采用REPLICATE_WILD_IGNORE_TABLE恢复同步,择机mysqldump在从库导入该表数据
mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =('MDM.CALENDAR');
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.172.64.161 Master_User: dbsync Master_Port: 27415 Connect_Retry: 60 Master_Log_File: mysql_bin.014572 Read_Master_Log_Pos: 191330675 Relay_Log_File: relay-bin.043708 Relay_Log_Pos: 176749349 Relay_Master_Log_File: mysql_bin.014569 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: MDM.CALENDAR Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 176749136 Relay_Log_Space: 3414083380 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: 5121 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: 1 Master_UUID: ef92423d-488b-11ec-a1ae-525493542058 Master_Info_File: /u01/mysql/data/master .info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: update 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: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南