MySQL主从异常处理Errno: 1292-REPLICATE_WILD_IGNORE_TABLE的使用

• 一、Slave错误信息

+ View Code?

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

• 二、查询表结构

+ View Code?

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

+ View Code?

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;

+ View Code?

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