復制中常見1062和1032錯誤處理方法

復制中錯誤處理

傳統復制錯誤跳過:

stop slave sql_thread ;

set global slq_slave_skip_counter=1;

start slave sql_thread ;

GTID復制錯誤跳過:

stop slave sql_thread ;

set gtid_next='uuid:N';

begin;commit;

set gtid_next='automatic';

start slave sql_thread ;

注意：

若是binlog+pos復制，使用：

set global sql_salve_skip_counter=1;

代替下面步驟：

root@localhost [testdb]>set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380:13';

root@localhost [testdb]>begin;commit;

root@localhost [testdb]>set gtid_next='automatic';

主從復制錯誤分類及處理方式

（1）主庫create table ,從庫已經存在，以主庫為準處理方法：

slave：

set sql_log_bin=0;

drop table t1；

set sql_log_bin=1;

start slave sql_thread ;

例：
slave:
root@localhost [testdb]>create table t2(c1 int,c2 varchar(20));
master:
root@localhost [testdb]>create table t2(c1 int,c2 varchar(20));
root@localhost [testdb]>show slave status\G
......
 Last_Error: Error 'Table 't2' already exists' on query. Default database: 'testdb'. Query: 'create table t2(c1 int,c2 varchar(20))'
.......
解決方法：
slave:
#drop操作不記錄從庫的binlog，這一步的作用是防止在以后主從切換的時候，把主庫的t2表干掉
root@localhost [testdb]>set sql_log_bin=0;  
root@localhost [testdb]>drop table t2;
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>start slave sql_thread;

（2）insert主鍵沖突的錯誤error1062

解決方法：直接刪除從庫沖突主鍵

例：
slave：
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1 values(2,'bbb');
root@localhost [testdb]>set sql_log_bin=1;
master:
root@localhost [testdb]>insert into t1 values(2,'bbbbbb');
slave :
root@localhost [testdb]>show slave status\G
Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table testdb.t1; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000029, end_log_pos 2796
slave :
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>delete from t1 where c1=2;
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>start slave sql_thread;

（3）update找不到記錄error1032

唯一的方法：偽造符合條件的數據

例：
master：
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1 values(1,'aaa');
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>update t1 set c2='aaaaaa' where c1=1;
slave：
root@localhost [testdb]>show slave status\G
......
 Last_Error: Could not execute Update_rows event on table testdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000029, end_log_pos 2529
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2283
master：
[root@Darren1 logs]# mysqlbinlog --base64-output=decode-rows --verbose --start-position=2283 --stop-position=2529 mysql-bin.000029
......
### UPDATE `testdb`.`t1`
### WHERE
###   @1=1
###   @2='aaa'
### SET
###   @1=1
###   @2='aaaaaa'
slave:
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1 values(1,'aaa');
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>start slave sql_thread;

（4）delete找不到錯誤 error1032

方法一：偽造符合條件的數據

例：
master：
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1 values(1,'aaa');
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>delete from t1 where c1=1;
slave：
root@localhost [testdb]>show slave status\G
......
            Slave_IO_Running: Yes
            Slave_SQL_Running: No
          Exec_Master_Log_Pos: 905   --從庫已經成功執行主庫到的postion點
Last_SQL_Error: Could not execute Delete_rows event on table testdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000029, end_log_pos 1138 --從庫執行結束點
maser：
[root@Darren1 logs]# mysqlbinlog --base64-output=decode-rows --verbose --start-position=905 --stop-position=1138 mysql-bin.000029
......
### DELETE FROM `testdb`.`t1`
### WHERE
###   @1=1
###   @2='aaa'
slave：
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1  values(1,'aaa');
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>start slave sql_thread;
方法二：從庫跳過沒有成功刪除掉的行記錄對應的GTID
master：
root@localhost [testdb]>set sql_log_bin=0;
root@localhost [testdb]>insert into t1 values(1,'aaa');
root@localhost [testdb]>insert into t1 values(2,'bbb');
root@localhost [testdb]>set sql_log_bin=1;
root@localhost [testdb]>delete from t1 where c1 =1;
root@localhost [testdb]>delete from t1 where c1 =2;
root@localhost [testdb]>insert into t1 values(3,'ccc');
slave：
root@localhost [testdb]>show slave status\G
......
Last_SQL_Error: Could not execute Delete_rows event on table testdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000029, end_log_pos 1402
           Retrieved_Gtid_Set: f0e27aec-b275-11e6-9c17-000c29565380:1-14   --從庫結束的GTID點
            Executed_Gtid_Set: ab6320bc-d158-11e6-88f8-000c29c1b8a9:1,
            f0e27aec-b275-11e6-9c17-000c29565380:10-11  --從庫成功執行過的GTID
slave：
root@localhost [testdb]>stop slave;
root@localhost [testdb]>set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380:12';
root@localhost [testdb]>begin;commit;
root@localhost [testdb]>set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380:13';
root@localhost [testdb]>begin;commit;
root@localhost [testdb]>set gtid_next='automatic';
root@localhost [testdb]>start slave;