使ç¨è¿Mysqlæ°æ®åºæåï¼ä¸å®å¬è¿è¯»åå离ï¼å¬çå¤çï¼ä¼°è®¡è³æµé½èµ·è§åäºãé£ä¹è¯»åå离æ¯æä¹å®ç°çå¢ï¼æ常è§çæ¹æ³å°±æ¯æ建Mysqlç主ä»å¤å¶ï¼ä¸»åºæä¾åæä½ï¼ä»åºæä¾è¯»æä½ï¼ä»èè¾¾å°åºç¨ç读åå离ã
对äºåå ¥åå¼åå²ï¼è¿ç»´å²çèæ°ä»¬ï¼ä¸å®è¦å¼æä»ä¹æ¯è¯»åå离ï¼è¯»åå离解å³ä»ä¹ä¸å¡é®é¢ï¼åªæå½»åºå¼æç½è¿äºä¹åï¼æå»ç¨è¯»åå离æ¶æã
主ä»å¤å¶æ¥å¸¸æ¥é
åºè¯å°±ä¸å¤è¯´äºï¼å¨è¿éå°±æ¥è¯´è¯´ï¼ä¸»ä»å¤å¶æ常è§ç2ç§é误
第ä¸ç§ï¼ä¸»é®å²çª(Error_code: 1062)
第äºç§ï¼è®°å½ä¸¢å¤±ï¼ä¾å¦update,deleteæä½ï¼å¨ä»åºæ¾ä¸å°å¯¹åºè®°å½(Error_code: 1032)
主ä»å¤å¶æ¥å¸¸æ¥éå¤çè¿ç¨
ä¸é¢æ¥è¯¦ç»æ¨¡æä¸ä¸è®°å½ä¸¢å¤±ï¼å¤çå ¨è¿ç¨æ£æ¥ä¸»ä»å¤å¶æ¯å¦æ£å¸¸
[[email protected]] 11:34:29 [testdb]>show slave statusG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000029 Read_Master_Log_Pos: 3683 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 2207 Relay_Master_Log_File: binlog.000029 Slave_IO_Running: Yes Slave_SQL_Running: Yes
å¯ä»¥çå°IO线ç¨åSQL线ç¨è¿è¡é½æ¯æ£å¸¸çã
å建æµè¯è¡¨åè®°å½
[[email protected]] 11:25:48 [testdb]>show create table test1G;*************************** 1. row *************************** Table: test1Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL, `name1` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `name2` char(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci1 row in set (0.07 sec)insert into test1 values(1,'test1','test1');insert into test1 values(2,'test2','test2');insert into test1 values(3,'test3','test3');
模æ主ä»å¤å¶ç±äºä»åºè®°å½ç¼ºå¤±ï¼å¯¼è´ä¸»ä»å¤å¶å¤±è´¥
第ä¸æ¥ï¼å¨ä»åºä¸å é¤id=2çè®°å½
[[email protected]] 11:26:41 [testdb]>delete from test1 where id=2;Query OK, 1 row affected (0.44 sec)[[email protected]] 11:26:52 [testdb]>select * from test1;+----+-------+-------+| id | name1 | name2 |+----+-------+-------+| 1 | test1 | test1 || 3 | test3 | test3 |+----+-------+-------+2 rows in set (0.00 sec)
第äºæ¥ï¼å¨ä¸»åºä¸å é¤id=2çè®°å½
[[email protected]] 11:27:11 [testdb]>delete from test1 where id=2;Query OK, 1 row affected (0.17 sec)[[email protected]] 11:27:51 [testdb]>select * from test1;+----+-------+-------+| id | name1 | name2 |+----+-------+-------+| 1 | test1 | test1 || 3 | test3 | test3 |+----+-------+-------+2 rows in set (0.00 sec)
å¨ä»åºä¸æ¥ç主ä»å¤å¶æ åµ
[[email protected]] 11:34:05 [testdb]>show slave statusG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000029 Read_Master_Log_Pos: 3683 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 1929 Relay_Master_Log_File: binlog.000029 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: 1032 Last_Error: Could not execute Delete_rows event on table testdb.test1; Can't find record in 'test1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000029, end_log_pos 3652 Skip_Counter: 0 Exec_Master_Log_Pos: 3405 Relay_Log_Space: 2414 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows event on table testdb.test1; Can't find record in 'test1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000029, end_log_pos 3652 Replicate_Ignore_Server_Ids: Master_Server_Id: 111213106 Master_UUID: 3ada166e-c4db-11ea-b21d-000c29cc2388 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: 200904 11:33:10 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3ada166e-c4db-11ea-b21d-000c29cc2388:84830-84835 Executed_Gtid_Set: 3ada166e-c4db-11ea-b21d-000c29cc2388:1-84834,3ada166e-c4db-11ea-b21d-000c29cc2389:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
æ¤æ¶ä¸»ä»çsql线ç¨å·²ç»æ¯åæ¢ç¶æï¼ä¸»ä»å¤å¶çæ°æ®å·²ç»ä¸åæ¥äºãå¤å¶å¼å§æ¥1032é误äºã
è¦è§£å³1032é误ï¼å¯ä»¥æ以ä¸3ç§æ¹æ¡
æ¥éä¸ç§è§£å³æ¹æ¡
æ¹æ¡ä¸ï¼æå·¥å°ç¼ºå¤±çä¸å¡è®°å½å¨ä¸»åºä¸å¯¼åºï¼å¹¶å¯¼å ¥å°ä»åºï¼ç¶åå¯å¨ä»åºçsql线ç¨å°±å¯ä»¥äºãæ ¢çï¼å¤§å®¶æ没æ注æå°ä¸ä¸ªé®é¢ï¼å°±æ¯å¨ä¸»åºä¸ï¼å°åºè¦å¯¼åºåªæ¡è®°å½ï¼æ¥éä¿¡æ¯é并没æï¼ä½æ¯ææ示ï¼he event's master log binlog.000029, end_log_pos 3652ï¼æ以è¿éè¦å°binlogæ¥å¿éçå 容解æå¤çï¼æ¾å°è¦æä½çè®°å½ï¼ä¼¼ä¹æäºéº»ç¦ãä¸ç¨æ ï¼è¿ææ¹æ¡äºï¼æ¹æ¡ä¸ã
æ¹æ¡äºï¼Mysqlæ°æ®åºæä¾ä¸ä¸ªåæ°slave_skip_errorsï¼è¿ä¸ªåæ°å¯ä»¥è·³è¿æå®é误代ç çsqlè¯å¥ï¼ä¾å¦ï¼slave_skip_errors=1032ï¼å¯æï¼è¿ä¸ªåæ°ä¸è½å¨çº¿ä¿®æ¹ï¼ä¿®æ¹çæéè¦éå¯å®ä¾ï¼æ¯ä¸æ¯ä¹å¤ªå好ã
[[email protected]] 11:28:57 [testdb]>set global slave_skip_errors=1032;ERROR 1238 (HY000): Variable 'slave_skip_errors' is a read only variable
æ¹æ¡ä¸ï¼ä½¿ç¨percona-toolkitså·¥å ·éä¸çpt-slave-restartå·¥å ·ï¼èªå¨è·³è¿ä¸»ä»åæ¥æå®çæ¥é代ç sqlè¯å¥ï¼æ¤æ¹æ³å¯¹mysqlæ°æ®ä¾µå ¥æ§å°ï¼ä¸å¿ éå¯Mysqlå®ä¾
[[email protected] ~]$ pt-slave-restart --user=root --password=root --socket=/data/mysql/run/3306/mysql.sock --error-numbers=1032# A software update is available:2020-09-04T11:32:07 S=/data/mysql/run/3306/mysql.sock,p=...,u=root mysql-relay-bin.000003 1651 1032
å½è·³è¿ä¸»ä»åæ¥æå®çæ¥é代ç sqlè¯å¥ï¼ä¸»ä»å¤å¶æ¢å¤ä¹åï¼é´é64ç§ï¼ä¼å次èªå¨æ£æµä¸»ä»å¤å¶æ¯å¦æ1032é误ã
å ¶å®ç±»ä¼¼çé误ï¼é½å¯ä»¥ç¨ä»¥ä¸ä¸ç§æ¹æ³æ¹æ¡å¤çï¼å»ºè®®å¤§å®¶ä½¿ç¨æ¹æ¡ä¸ã