log ver: 4 | | mysql-bin.000001 |? 120 | Query?????? |???????? 2 |???????? 202 | BEGIN???????????????????????????????? | | mysql-bin.000001 |? 202 | Table_map?? |???????? 2 |???????? 263 | table_id: 281 (edusoho_e.t1)????????? | | mysql-bin.000001 |? 263 | Write_rows? |???????? 2 |???????? 328 | table_id: 281 flags: STMT_END_F?????? | | mysql-bin.000001 |? 328 | Xid???????? |???????? 2 |???????? 359 | COMMIT /* xid=587 */????????????????? | | mysql-bin.000001 |? 359 | Query?????? |???????? 2 |???????? 441 | BEGIN???????????????????????????????? | | mysql-bin.000001 |? 441 | Table_map?? |???????? 2 |???????? 502 | table_id: 281 (edusoho_e.t1)????????? | | mysql-bin.000001 |? 502 | Update_rows |???????? 2 |???????? 591 | table_id: 281 flags: STMT_END_F?????? | | mysql-bin.000001 |? 591 | Xid???????? |???????? 2 |???????? 622 | COMMIT /* xid=596 */????????????????? | | mysql-bin.000001 |? 622 | Query?????? |???????? 2 |???????? 704 | BEGIN???????????????????????????????? | | mysql-bin.000001 |? 704 | Table_map?? |???????? 2 |???????? 765 | table_id: 281 (edusoho_e.t1)????????? | | mysql-bin.000001 |? 765 | Delete_rows |???????? 2 |???????? 913 | table_id: 281 flags: STMT_END_F?????? | | mysql-bin.000001 |? 913 | Xid???????? |???????? 2 |???????? 944 | COMMIT /* xid=605 */????????????????? | | mysql-bin.000001 |? 944 | Query?????? |???????? 2 |??????? 1026 | BEGIN???????????????????????????????? | | mysql-bin.000001 | 1026 | Table_map?? |???????? 2 |??????? 1087 | table_id: 281 (edusoho_e.t1)????????? | | mysql-bin.000001 | 1087 | Write_rows? |???????? 2 |??????? 1150 | table_id: 281 flags: STMT_END_F?????? | | mysql-bin.000001 | 1150 | Xid???????? |???????? 2 |??????? 1181 | COMMIT /* xid=614 */????????????????? | | mysql-bin.000001 | 1181 | Query?????? |???????? 2 |??????? 1263 | BEGIN???????????????????????????????? | | mysql-bin.000001 | 1263 | Table_map?? |???????? 2 |??????? 1324 | table_id: 281 (edusoho_e.t1)????????? | | mysql-bin.000001 | 1324 | Update_rows |???????? 2 |??????? 1416 | table_id: 281 flags: STMT_END_F?????? | | mysql-bin.000001 | 1416 | Xid???????? |???????? 2 |??????? 1447 | COMMIT /* xid=623 */????????????????? | +------------------+------+-------------+-----------+-------------+---------------------------------------+
跳过有害SQL,继续进行复制:
1、暂时将同步延迟关闭,使Slave立马同步Master的数据
mysql> change master to master_delay=0;
2、同步数据至drop语句发生之前
mysql> start slave until master_log_file='mysql-bin.000001',master_log_pos=622 user='repliter' password='123456';
3、再次查看执行到的position
mysql> show slave status\G;
Exec_Master_Log_Pos: 622? (delete全表语句之前的数据已经同步过来了,去Slave相应的数据表验证下,但是delete全表语句之后的数据还没有同步过来)
现在跳过有害SQL之后,继续Master的数据复制:
mysql> stop slave;
mysql> change master to master_log_pos=1026 [master_delay=180];(可加可不加)
mysql> start slave user='repliter' password='123456';
mysql> show slave status\G; Exec_Master_Log_Pos: 1447
去验证delete全表语句之后的数据过去了没 就这样有害SQL被跳过了,保留了一份Slave还未被删除的数据备份,之后是做主从切换,还是把数据导回到Master就根据你自己的情况了
笔者这里演示下,将Slave的同名数据库导回到Master的过程(如果数据量很大的话,建议做主从切换,因为导回的成本也许比切换的成本大的多)
如果你的数据表数据量较小,可以在上述until语句执行完之后,将Master的数据表加上全局写锁,然后将Slave主机上的数据同步过去,因为数据表小,对业务影响也不会太大
将Master主机上的数据表加上写锁:(如果你知道你的数据表pk值不会被插入,而是依靠自增生成,那么你可能需要先将表清空,导入旧数据后,再导入新数据,这样才能保证数据的一致性)
LOCK TABLE `edusoho_e`.`t1` WRITE;
然后再Slave主机上把until语句之前的数据导出来:
INSERT INTO `t1` VALUES (1,'lzb','石家庄',1,'MySQL' |