您好,登錄后才能下訂單哦!
復(fù)制中錯(cuò)誤處理
傳統(tǒng)復(fù)制錯(cuò)誤跳過:
stop slave sql_thread ;
set global slq_slave_skip_counter=1;
start slave sql_thread ;
GTID復(fù)制錯(cuò)誤跳過:
stop slave sql_thread ;
set gtid_next='uuid:N';
begin;commit;
set gtid_next='automatic';
start slave sql_thread ;
注意:
若是binlog+pos復(fù)制,使用:
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';
主從復(fù)制錯(cuò)誤分類及處理方式
(1)主庫create table ,從庫已經(jīng)存在,以主庫為準(zhǔn)處理方法:
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,這一步的作用是防止在以后主從切換的時(shí)候,把主庫的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主鍵沖突的錯(cuò)誤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
唯一的方法:偽造符合條件的數(shù)據(jù)
例:
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找不到錯(cuò)誤 error1032
方法一:偽造符合條件的數(shù)據(jù)
例:
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 --從庫已經(jīng)成功執(zhí)行主庫到的postion點(diǎn)
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 --從庫執(zhí)行結(jié)束點(diǎn)
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;
方法二:從庫跳過沒有成功刪除掉的行記錄對(duì)應(yīng)的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 --從庫結(jié)束的GTID點(diǎn)
Executed_Gtid_Set: ab6320bc-d158-11e6-88f8-000c29c1b8a9:1,
f0e27aec-b275-11e6-9c17-000c29565380:10-11 --從庫成功執(zhí)行過的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;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。