设为首页 加入收藏

TOP

主从延迟复制--数据恢复测试!-努力是一种习惯-51CTO博客(一)
2019-05-23 15:03:51 】 浏览:86
Tags:主从 延迟 复制 数据恢复 测试 努力 习惯 -51CTO 博客

写在前面:

????设想一下,你的线上环境使用了主从复制架构,如果不小心执行了,如:drop database db1、drop table tb1,或者说delete,update不加where条件的更新,当问题发生的时候,你是不是希望还有补救的机会?希望Slave主机不要重复Master主机的执行情况?可不可以将这个有害的SQL跳过后,继续进行复制?答案是:可以的。主从延迟复制就是实现这个功能的


环境准备:

????搭建好主从架构(笔者采用的传统的复制方式)

????设置好主从延迟变量(如:CHANGE MASTER TO master_delay=180)

????创建好测试表(在下面详细说明)


如果执行了drop database db1或drop table tb1有害SQL:(drop database和drop table恢复方式相同,只是影响范围不同而已)

测试表:
CREATE TABLE `edusoho_e`.`t1` (
? `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
? `xname` varchar(20) NOT NULL DEFAULT '',
? `address` char(20) NOT NULL DEFAULT '',
? `sex` tinyint(1) NOT NULL DEFAULT '1',
? `hobby` varchar(30) NOT NULL DEFAULT '',
? `age` tinyint(2) DEFAULT '18',

>? PRIMARY KEY (`id`),
? KEY `idx_name` (`xname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `bbs`.`myhash_0` (
? `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
? `c1` int(10) NOT NULL DEFAULT '0',
? `c2` int(10) unsigned DEFAULT NULL,
? `c5` int(10) unsigned NOT NULL DEFAULT '0',
? `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
? `c4` varchar(200) NOT NULL DEFAULT '',
? PRIMARY KEY (`id`),
? KEY `idx_c1` (`c1`),
? KEY `idx_c2` (`c2`)
) ENGINE=InnoDB? DEFAULT CHARSET=utf8

Master主机在正常的变更数据:

INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('lzb', '石家庄', 'MySQL');
INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('Python', '北京', '游戏');
INSERT INTO `bbs`.`myhash_0`(c1,c2,c5,c3,c4) VALUES(2,3,4,NOW(),5);
UPDATE `bbs`.`myhash_0` SET id=2 WHERE id=5;


上面的正常数据变更还没有执行完,此时Master上突然间执行了某个有害SQL:

DROP DATABASE `bbs`;


发现问题后,马上停止Slave复制:

mysql> stop slave;


而此时Master主机上其他库是正常的:

INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('PHP', '深圳', '学习');


分析:

drop语句发生的时候,drop语句之前的数据可能还没完全同步至Slave主机(这很有可能,尤其是你的数据量大的情况下),所以,需要分析Master主机的binlog,找到drop语句发生的position,使Slave主机同步至drop语句之前,然后跳过drop语句,使Slave继续同步Master的其他数据


分析Master的binlog:

mysqlbinlog -v --base64-output=decode mysql-bin.000001 | grep -i -C 10 --color 'drop'
###?? @3=2
###?? @4=3
###?? @5=1556612931
###?? @6=''
# at 1053
#190430 16:28:51 server id 2? end_log_pos 1084 CRC32 0x7644c8d2???? Xid = 2068
COMMIT/*!*/;
# at 1084
#190430 16:28:51 server id 2? end_log_pos 1180 CRC32 0x8fd4727e???? Query?? thread_id=11??? exec_time=0 error_code=0
SET TIMESTAMP=1556612931/*!*/;
DROP DATABASE `bbs`
/*!*/;
# at 1180
#190430 16:28:52 server id 2? end_log_pos 1262 CRC32 0xcfe6ddb1???? Query?? thread_id=11??? exec_time=0 error_code=0
SET TIMESTAMP=1556612932/*!*/;
BEGIN
/*!*/;
# at 1262
#190430 16:28:52 server id 2? end_log_pos 1323 CRC32 0x539e7626???? Table_map: `edusoho_e`.`t1` mapped to number 312
# at 1323
#190430 16:28:52 server id 2? end_log_pos 1383 CRC32 0xd286a3c0???? Write_rows: table id 312 flags: STMT_END_F


查看详细的binlog信息:

mysql> show binlog events in 'mysql-bin.000001' from 1053 limit 10;


跳过有害SQL,继续进行复制:

1、查看当前执行到的positon

mysql> show slave status\G;
Exec_Master_Log_Pos: 120


2、暂时将同步延迟关闭,使Slave立马同步Master的数据

mysql> change master to master_delay=0;


3、同步数据至drop语句发生之前

mysql> start slave until master_log_file='mysql-bin.000001',master_log_pos=1084 user='repliter' password='123456';


4、再次查看执行到的position

mysql> show slave status\G;

Exec_Master_Log_Pos: 1084 (drop语句之前的数据已经同步过来了,去Slave相应的数据表验证下,但是drop语句之后的数据还没有同步过来)


现在跳过有害SQL之后,继续Master的数据复制:

mysql> stop sl
编程开发网

首页 上一页 1 2 3 4 5 下一页 尾页 1/5/5
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇sql开发规范-梁十八的博客-51CTO.. 下一篇分区间统计-梁十八的博客-51CTO博..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(217) }