复制中错误处理

传统复制错误跳过:

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 ;

1
<span style= "font-size:12px;font-family:'宋体', SimSun;" >例：<br>slave:<br>root@localhost [testdb]> create  table  t2(c1  int ,c2  varchar (20));<br>master:<br>root@localhost [testdb]> create  table  t2(c1  int ,c2  varchar (20));<br>root@localhost [testdb]>show slave status\G<br>......<br> Last_Error: Error  'Table ' t2 ' already exists'  on  query.  Default  database :  'testdb' . Query:  'create table t2(c1 int,c2 varchar(20))' <br>.......<br>解决方法：<br>slave:<br># drop 操作不记录从库的binlog，这一步的作用是防止在以后主从切换的时候，把主库的t2表干掉<br>root@localhost [testdb]> set  sql_log_bin=0;  <br>root@localhost [testdb]> drop  table  t2;<br>root@localhost [testdb]> set  sql_log_bin=1;<br>root@localhost [testdb]>start slave sql_thread;</span>


（2）insert主键冲突的错误error1062

解决方法：直接删除从库冲突主键

1
<span style= "font-size:12px;font-family:'宋体', SimSun;" >例：<br>slave：<br>root@localhost [testdb]> set  sql_log_bin=0;<br>root@localhost [testdb]> insert  into  t1  values (2, 'bbb' );<br>root@localhost [testdb]> set  sql_log_bin=1;<br>master:<br>root@localhost [testdb]> insert  into  t1  values (2, 'bbbbbb' );<br>slave :<br>root@localhost [testdb]>show slave status\G<br>Last_Errno: 1062<br>                   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<br>slave :<br>root@localhost [testdb]> set  sql_log_bin=0;<br>root@localhost [testdb]> delete  from  t1  where  c1=2;<br>root@localhost [testdb]> set  sql_log_bin=1;<br>root@localhost [testdb]>start slave sql_thread;</span>


（3）update找不到记录error1032

唯一的方法：伪造符合条件的数据

1
<span style= "font-size:12px;font-family:'宋体', SimSun;" >例：<br>master：<br>root@localhost [testdb]> set  sql_log_bin=0;<br>root@localhost [testdb]> insert  into  t1  values (1, 'aaa' );<br>root@localhost [testdb]> set  sql_log_bin=1;<br>root@localhost [testdb]> update  t1  set  c2= 'aaaaaa'  where  c1=1;<br>slave：<br>root@localhost [testdb]>show slave status\G<br>......<br> 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<br>                 Skip_Counter: 0<br>          Exec_Master_Log_Pos: 2283<br>master：<br>[root@Darren1 logs]# mysqlbinlog  --base64-output=decode-rows --verbose --start-position=2283 --stop-position=2529 mysql-bin.000029<br>......<br>### UPDATE `testdb`.`t1`<br>### WHERE<br>###   @1=1<br>###   @2='aaa'<br>### SET<br>###   @1=1<br>###   @2='aaaaaa'<br>slave:<br>root@localhost [testdb]>set sql_log_bin=0;<br>root@localhost [testdb]>insert into t1 values(1,'aaa');<br>root@localhost [testdb]>set sql_log_bin=1;<br>root@localhost [testdb]>start slave sql_thread;</span>


（4）delete找不到错误 error1032

方法一：伪造符合条件的数据

1
<span style= "font-size:12px;font-family:'宋体', SimSun;" >例：<br>master：<br>root@localhost [testdb]> set  sql_log_bin=0;<br>root@localhost [testdb]> insert  into  t1  values (1, 'aaa' );<br>root@localhost [testdb]> set  sql_log_bin=1;<br>root@localhost [testdb]> delete  from  t1  where  c1=1;<br>slave：<br>root@localhost [testdb]>show slave status\G<br>......<br>            Slave_IO_Running: Yes<br>            Slave_SQL_Running:  No <br>          Exec_Master_Log_Pos: 905    --从库已经成功执行主库到的postion点<br>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 --从库执行结束点<br>maser：<br>[root@Darren1 logs]# mysqlbinlog --base64-output=decode-rows --verbose --start-position=905 --stop-position=1138 mysql-bin.000029<br>......<br>### DELETE FROM `testdb`.`t1`<br>### WHERE<br>###   @1=1<br>###   @2='aaa'<br>slave：<br>root@localhost [testdb]>set sql_log_bin=0;<br>root@localhost [testdb]>insert into t1  values(1,'aaa');<br>root@localhost [testdb]>set sql_log_bin=1;<br>root@localhost [testdb]>start slave sql_thread;<br>方法二：从库跳过没有成功删除掉的行记录对应的GTID<br>master：<br>root@localhost [testdb]>set sql_log_bin=0;<br>root@localhost [testdb]>insert into t1 values(1,'aaa');<br>root@localhost [testdb]>insert into t1 values(2,'bbb');<br>root@localhost [testdb]>set sql_log_bin=1;<br>root@localhost [testdb]>delete from t1 where c1 =1;<br>root@localhost [testdb]>delete from t1 where c1 =2;<br>root@localhost [testdb]>insert into t1 values(3,'ccc');<br>slave：<br>root@localhost [testdb]>show slave status\G<br>......<br>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<br>           Retrieved_Gtid_Set: f0e27aec-b275-11e6-9c17-000c29565380:1-14   --从库结束的GTID点<br>            Executed_Gtid_Set: ab6320bc-d158-11e6-88f8-000c29c1b8a9:1,<br>            f0e27aec-b275-11e6-9c17-000c29565380:10-11  --从库成功执行过的GTID<br>slave：<br>root@localhost [testdb]>stop slave;<br>root@localhost [testdb]>set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380:12';<br>root@localhost [testdb]>begin;commit;<br>root@localhost [testdb]>set gtid_next='f0e27aec-b275-11e6-9c17-000c29565380:13';<br>root@localhost [testdb]>begin;commit;<br>root@localhost [testdb]>set gtid_next='automatic';<br>root@localhost [testdb]>start slave;</span>