MySQL???????????

?????danfengcao ???????[ 2017/1/4 11:13:47 ] ????????????? MySQL

????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
...