首页 > 数据库 > 其他综合 >

主从延迟复制--数据恢复测试!-努力是一种习惯-51CTO博客

2019-05-07

写在前面:????设想一下,你的线上环境使用了主从复制架构,如果不小心执行了,如:drop database db1、drop table tb1,或者说delete,update不加where条件的更新,当问题发生的时候,你是不是希望还有补救

写在前面:

????设想一下,你的线上环境使用了主从复制架构,如果不小心执行了,如: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 slave;

mysql> change master to master_log_pos=1262 [master_delay=180];(可加可不加)

mysql> start slave user='repliter' password='123456';

mysql> show slave status\G;
Exec_Master_Log_Pos: 1414?


去验证drop语句之后的数据过去了没
就这样有害SQL被跳过了,保留了一份Slave还未被删除的数据备份,之后是做主从切换,还是把数据导回到Master就根据你自己的情况了


笔者这里演示下,将Slave的同名数据库导回到Master的过程(如果数据量很大的话,建议做主从切换,因为导回的成本也许比切换的成本大的多,自行评估,个人建议)

1、首先,将Slave的库导成SQL文件,这里为bbs_new.sql(一定要有包含创建库的语句,要是忘记了,你就自己创建)

2、给导入SQL文件更改权限
chown mysql.mysql bbs_new.sql

3、mysql -uroot -p bbs -e "SET @@session.sql_log_bin=0;source /root/bbs_new.sql;"?? (一定要加sql_log_bin=0,不然你懂得)


至此,drop database语句,成功跳过!


如果执行了delete from table(不加where条件)或truncate table有害SQL:

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

INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`,age) VALUES ('Games', '海南', '就是玩',28);?? ?
UPDATE `edusoho_e`.`t1` SET xname='刘备' WHERE id=5;


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

DELETE FROM `edusoho_e`.`t1`;


因为是delete全表数据,表结构仍在,依据会有新数据产生和变更:

INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('曹操', '魏国', '三国');
UPDATE `edusoho_e`.`t1` SET age=40 WHERE xname='曹操';
DELETE FROM `edusoho_e`.`t1` WHERE xname='lzb';?? #删除一条不存在的数(不会产生日志)
UPDATE `edusoho_e`.`t1` SET xname='孙权' WHERE xname='Python';??? #更新一条不存在的数据(不会产生日志)


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

mysql> stop slave;


分析:

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


在Master主机上根据时间分析binlog(因为笔者使用的是ROW格式,所以,会有很多条的delete语句,在delete全表语句之前,极有可能有正常的delete语句,你将分不清哪个才是该跳过的有害SQL,所以,问题发生的时候,一定要尽可能的知道发生的时间,对binlog进行分析才能更加有效)


mysqlbinlog -v --base64-output=decode mysql-bin.000001 | grep -i -C 10 --color 'delete from'(笔者自己测试,生产环境一定要加时间筛选)

COMMIT/*!*/;
# at 622
#190505? 8:34:35 server id 2? end_log_pos 704 CRC32 0xd237cd1f? Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1557016475/*!*/;
BEGIN
/*!*/;
# at 704
#190505? 8:34:35 server id 2? end_log_pos 765 CRC32 0x9335b52a? Table_map: `edusoho_e`.`t1` mapped to number 281
# at 765
#190505? 8:34:35 server id 2? end_log_pos 913 CRC32 0xb6da4487? Delete_rows: table id 281 flags: STMT_END_F
### DELETE FROM `edusoho_e`.`t1`
### WHERE
###?? @1=1
###?? @2='lzb'
###?? @3='石家庄'
###?? @4=1
###?? @5='MySQL'
###?? @6=18
### DELETE FROM `edusoho_e`.`t1`
### WHERE
###?? @1=3
###?? @2='Python'
###?? @3='北京'
###?? @4=1
###?? @5='游戏'
###?? @6=18
### DELETE FROM `edusoho_e`.`t1`
### WHERE
###?? @1=5
###?? @2='刘备'
###?? @3='深圳'
###?? @4=1
###?? @5='学习'
###?? @6=18
### DELETE FROM `edusoho_e`.`t1`
### WHERE
###?? @1=7
###?? @2='Games'
###?? @3='海南'
###?? @4=1
###?? @5='就是玩'
###?? @6=28
# at 913
#190505? 8:34:35 server id 2? end_log_pos 944 CRC32 0x215741c7? Xid = 605
COMMIT/*!*/;


查看详细的binlog信息:

mysql> show binlog events in 'mysql-bin.000001';(线上的binlog很大,一定要加from做筛选)
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| Log_name???????? | Pos? | Event_type? | Server_id | End_log_pos | Info????????????????????????????????? |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |??? 4 | Format_desc |???????? 2 |???????? 120 | Server ver: 5.6.16-log, Binlog 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',18),(3,'Python','北京',1,'游戏',18),(5,'刘备','深圳',1,'学习',18),(7,'Games','海南',1,'就是玩',28);


切换到Master:

mysql> show master status\G;
*************************** 1. row ***************************
???????????? File: mysql-bin.000001
???????? Position: 1447


SET @@session.sql_log_bin=0;? (一定要做,具体原因应该都知道)


把数据导回去(如果是SQL文件,则执行source导入)

INSERT INTO `t1` VALUES (1,'lzb','石家庄',1,'MySQL',18),(3,'Python','北京',1,'游戏',18),(5,'刘备','深圳',1,'学习',18),(7,'Games','海南',1,'就是玩',28);


mysql> show master status\G;
*************************** 1. row ***************************
???????????? File: mysql-bin.000001
???????? Position: 1447


释放锁:

UNLOCK TABLES;

至此,delete 全表语句,成功跳过!


如果执行了update table(不加限制条件)有害SQL:

测试表:
CREATE TABLE `orders` (
? `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
? `xname` varchar(10) NOT NULL DEFAULT '' COMMENT '用户名称',
? `chongzhi` int(11) NOT NULL DEFAULT '0' COMMENT '充值金额',
? `amount` int(11) NOT NULL DEFAULT '0' COMMENT '剩余金额',
? PRIMARY KEY (`id`)
) ENGINE=InnoDB? DEFAULT CHARSET=utf8 COMMENT='用户订单表'


正常的Master数据变更:
INSERT INTO `edusoho_e`.`orders`(xname,chongzhi,amount) VALUES('郑千次',10000,100);
INSERT INTO `edusoho_e`.`orders`(xname,chongzhi,amount) VALUES('孙悟空',200,600);
INSERT INTO `edusoho_e`.`orders`(xname,chongzhi,amount) VALUES('柯南',666,888);
INSERT INTO `edusoho_e`.`orders`(xname,chongzhi,amount) VALUES('我',1,0);
UPDATE `edusoho_e`.`orders` SET chongzhi=chongzhi+1000 WHERE id=3;
UPDATE `edusoho_e`.`orders` SET amount=amount-200 WHERE id=5;
UPDATE `edusoho_e`.`orders` SET amount=amount-100;


此时,线上有一张用户订单表,执行了一条不加WHERE条件的UPDATE语句:

UPDATE `edusoho_e`.`orders` SET chongzhi=chongzhi+1000;

执行过后,用户很高兴,因为没充钱,白得了1000块;但是你的老板,绝对恨不得揍死你,为了不被揍,所以,你得赶紧恢复你的数据


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

mysql> stop slave;


分析:

发现问题之后,马上对Master加写锁,因为此时数据虽然存在,但是已经是错误的数据;然后确定有害SQL的position,然后跳过它,继续Master的复制


LOCK TABLE `edusoho_e`.`orders` WRITE;


在Master主机上根据时间分析binlog(因为笔者使用的是ROW格式,所以,会有很多条的update语句,如果在update不加限制条件语句之前,也有正常的update语句,你将分不清哪个才是该跳过的有害SQL,所以,问题发生的时候,一定要尽可能的知道发生的时间,对binlog进行分析才能更加有效)


分析Master日志,找到执行的问题SQL发生的position:
mysqlbinlog -v --base64-output=decode mysql-bin.000001 | grep -i -C 10 --color 'update'
COMMIT/*!*/;
# at 3554
#190505 10:04:20 server id 2? end_log_pos 3636 CRC32 0xd95ad4e9?? Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1557021860/*!*/;
BEGIN
/*!*/;
# at 3636
#190505 10:04:20 server id 2? end_log_pos 3695 CRC32 0xa8208a81?? Table_map: `edusoho_e`.`orders` mapped to number 282
# at 3695
#190505 10:04:20 server id 2? end_log_pos 3897 CRC32 0xdb6fe2c1?? Update_rows: table id 282 flags: STMT_END_F
### UPDATE `edusoho_e`.`orders`
### WHERE
###?? @1=1
###?? @2='郑千次'
###?? @3=10000
###?? @4=100
### SET
###?? @1=1
###?? @2='郑千次'
###?? @3=11000
###?? @4=100
### UPDATE `edusoho_e`.`orders`
### WHERE
###?? @1=3
###?? @2='孙悟空'
###?? @3=1200
###?? @4=600
### SET
###?? @1=3
###?? @2='孙悟空'
###?? @3=2200
###?? @4=600
### UPDATE `edusoho_e`.`orders`
### WHERE
###?? @1=5
###?? @2='柯南'
###?? @3=666
###?? @4=688
### SET
###?? @1=5
###?? @2='柯南'
###?? @3=1666
###?? @4=688
### UPDATE `edusoho_e`.`orders`
### WHERE
###?? @1=7
###?? @2='我'
###?? @3=1
###?? @4=0
### SET
###?? @1=7
###?? @2='我'
###?? @3=1001
###?? @4=0

mysql> show binlog events in 'mysql-bin.000001' from 3554;
+------------------+------+-------------+-----------+-------------+----------------------------------+
| Log_name???????? | Pos? | Event_type? | Server_id | End_log_pos | Info???????????????????????????? |
+------------------+------+-------------+-----------+-------------+----------------------------------+
| mysql-bin.000001 | 3554 | Query?????? |???????? 2 |??????? 3636 | BEGIN??????????????????????????? |
| mysql-bin.000001 | 3636 | Table_map?? |???????? 2 |??????? 3695 | table_id: 282 (edusoho_e.orders) |
| mysql-bin.000001 | 3695 | Update_rows |???????? 2 |??????? 3897 | table_id: 282 flags: STMT_END_F? |
| mysql-bin.000001 | 3897 | Xid???????? |???????? 2 |??????? 3928 | COMMIT /* xid=893 */???????????? |
+------------------+------+-------------+-----------+-------------+----------------------------------+
4 rows in set (0.00 sec)


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

1、和问题发生人员沟通,确认update是怎样执行的

在Master上执行:

SET @@session.sql_log_bin=0;(一定要加,不然你懂得)
UPDATE `edusoho_e`.`orders` SET chongzhi=chongzhi-1000;
此时,Master和SLave的数据都恢复了一致,只要Slave跳过有害的UPDATE语句就可以了


2、跳过有害SQL,继续复制

mysql> change master to master_log_pos=3928 [master_delay=180];(可加可不加)


3、start slave user='repliter' password='123456';


4、释放表的写锁

UNLOCK TABLES;


至此,update全表语句,成功跳过!


题外:

本文是笔者根据自己的理解,设想线上可能发生的部分问题后,针对性的利用 master_delay 参数特性,进行数据恢复做的测试,并没有经过任何的实战检测。一方面,仅为广大同行做个参考;另一方面,记录笔者自己的心得和针对问题解决的思路做个总结,当问题真正发生的时候,有个方向可以进行参考,而不至于手忙脚乱,不知所措,所以,对其中有误之处和理解不到位的地方,望请下方留言指正,不胜感激!

相关文章
最新文章
热点推荐