????DBA???????????????????????????????????????????????????????????????????????????????????????????????????????sql???binlog?????????????????????????????????????????????????????????????LVM???????????????????????????????mysql???????
????MySQL????(flashback)????binlog?????л????????????????????????????????????????????????????飬?????????????????????
??????θ??
???????С?????????????????????????????????????????????DBA?????????“????綽????????????????????????????????????????????????”DBA????±????????????????????“?????????????????????????”С????????Щ????????????Ц???“???????????и?????????????”DBA??????????
??????????緢???????罫??????????DBA??
???????????
????binlog????
????MySQL binlog??event????????????MySQL server??????binlog???????е????????????????????????????б仯??MySQL????binlog????????????????????????????????Щ??????????????????????binlog??
??????????????binlog??????????????
????· statement??????SQL????????binlog??????С???????Щ??????????????????????????????????????
????· row???????е?????????????е??????仯????????????binlog?????????????????
????· mixed???????????????????????statement????row????
????????binlog??????????binlog????????row??row???￡???????innodb??insert?????????????binlog??
# at 1129
#161225 23:15:38 server id 3773306082  end_log_pos 1197         Query   thread_id=1903021       exec_time=0     error_code=0
SET TIMESTAMP=1482678938/*!*/;
BEGIN
/*!*/;
# at 1197
#161225 23:15:38 server id 3773306082  end_log_pos 1245         Table_map: `test`.`user` mapped to number 290
# at 1245
#161225 23:15:38 server id 3773306082  end_log_pos 1352         Write_rows: table id 290 flags: STMT_END_F
BINLOG '
muJfWBPiFOjgMAAAAN0EAAAAACIBAAAAAAEABHRlc3QABHVzZXIAAwMPEQMeAAAC
muJfWB7iFOjgawAAAEgFAAAAACIBAAAAAAEAAgAD//gBAAAABuWwj+i1tVhK1hH4AgAAAAblsI/p
krFYStYg+AMAAAAG5bCP5a2ZWE/onPgEAAAABuWwj+adjlhNeAD4BQAAAAJ0dFhRYJM=
'/*!*/;
# at 1352
#161225 23:15:38 server id 3773306082  end_log_pos 1379         Xid = 5327954
COMMIT/*!*/;
???????????
???????binlog??event???????????е????????????????????????event?????????????????ɡ?
???????????event?????????????test.user????????
mysql> show create table test.userG
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT??
`name` varchar(10) DEFAULT NULL??
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
????· ????delete???????????binlog?????delete?????????????????insert??(???????????????????binlog2sql????binlog?????????SQL)
????????DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='С??';
?????????INSERT INTO `test`.`user`(`id`?? `name`) VALUES (1?? 'С??');
????· ????insert?????????SQL??delete??
????????INSERT INTO `test`.`user`(`id`?? `name`) VALUES (2?? 'С?');
?????????DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='С?';
????· ????update?????????sql??y???SET??WHERE?????
????????UPDATE `test`.`user` SET `id`=3?? `name`='С??' WHERE `id`=3 AND `name`='С??';
?????????UPDATE `test`.`user` SET `id`=3?? `name`='С??' WHERE `id`=3 AND `name`='С??';
??????????
?????????????????У?????????????????????????????SQL??
?????????????????binlog2sql??????????????binlog2sql?????????DBA???(???)???????????????????????????
??????????????binlog2sql??
????shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
????shell> pip install -r requirements.txt
??????????С????11:44??????test??user???????????????????????
????test??user?????????
mysql> select * from user;
+----+--------+---------------------+
| id | name   | addtime             |
+----+--------+---------------------+
|  1 | С??   | 2013-11-11 00:04:33 |
|  2 | С?   | 2014-11-11 00:04:48 |
|  3 | С??   | 2016-11-11 20:25:00 |
|  4 | С??   | 2013-11-11 00:00:00 |
.........
+----+--------+---------------------+
16384 rows in set (0.04 sec)
11:44???user??????????????????????????????????????????д???
mysql> delete from user where addtime>'2014-01-01';
Query OK?? 16128 rows affected (0.18 sec)
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|      261 |
+----------+
?????????????裺
????1?????mysql????????binlog???
????mysql> show master logs;
????+------------------+-----------+
????| Log_name         | File_size |
????+------------------+-----------+
????| mysql-bin.000053 | 168652863 |
????| mysql-bin.000054 |    504549 |
????+------------------+-----------+
????2???μ?binlog?????mysql-bin.000054????????????????????????SQL????????????????????μ???????????????????????????ι????????????test??user???(???????ж??sql??????????????binlog??????????????????????????)
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054' --start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00' > /tmp/raw.sql
raw.sql ?????
DELETE FROM `test`.`user` WHERE `addtime`='2014-11-11 00:04:48' AND `id`=2 AND `name`='С?' LIMIT 1; #start 257427 end 265754 time 2016-12-26 11:44:56
DELETE FROM `test`.`user` WHERE `addtime`='2015-11-11 20:25:00' AND `id`=3 AND `name`='С??' LIMIT 1; #start 257427 end 265754 time 2016-12-26 11:44:56
...
DELETE FROM `test`.`user` WHERE `addtime`='2016-12-14 23:09:07' AND `id`=24530 AND `name`='tt' LIMIT 1; #start 257427 end 504272 time 2016-12-26 11:44:56
INSERT INTO `test`.`user`(`addtime`?? `id`?? `name`) VALUES ('2016-12-10 00:04:33'?? 32722?? 'С??'); #start 504299 end 504522 time 2016-12-26 11:49:42
...