基于时间点的恢复

1、  拷贝老库的binlog到恢复库(注意不要把新库的日志给覆盖掉)

2、  解析binlog找出at时间点:

mysqlbinlog -vv mysql-bin.000001:

# at 3535
#160524  9:57:58 server id 16205624  end_log_pos 3626 CRC32 0xa7cedf84 Query thread_id=11 exec_time=0 error_code=0
use `test`;
SET TIMESTAMP=1464055078;
;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33;
create table t10 like t1
;
# at 3626
#160524  9:58:10 server id 16205624  end_log_pos 3698 CRC32 0xfec0431d Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1464055090;
BEGIN
;
# at 3698
#160524  9:58:10 server id 16205624  end_log_pos 3744 CRC32 0x0aa287fa Table_map: `test`.`t10` mapped to number 107
# at 3744
#160524  9:58:10 server id 16205624  end_log_pos 3799 CRC32 0x39ec7657 Write_rows: table id 107 flags: STMT_END_F


BINLOG '
MrVDVxM4R/cALgAAAKAOAAAAAGsAAAAAAAEABHRlc3QAA3QxMAABAwAA+oeiCg==
MrVDVx44R/cANwAAANcOAAAAAGsAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAAP7JAAAAV3bsOQ==
';
### INSERT INTO `test`.`t10`
### SET
###   @1=1
### INSERT INTO `test`.`t10`
### SET
###   @1=2
### INSERT INTO `test`.`t10`
### SET
###   @1=3
### INSERT INTO `test`.`t10`
### SET
###   @1=201
# at 3799
#160524  9:58:18 server id 16205624  end_log_pos 3830 CRC32 0x4870e01d Xid = 323
COMMIT;
# at 3830
#160524  9:58:18 server id 16205624  end_log_pos 3945 CRC32 0xd22c27fc Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1464055098;
DROP TABLE `t3`
;
# at 3945
#160524  9:58:51 server id 16205624  end_log_pos 4021 CRC32 0x3a14cd61 Query thread_id=6

3、  恢复数据(注意要包括use test否则不知道插到哪个库？)：

mysqlbinlog mysql-bin.000006 --start-position=3535  --stop-position=3830|mysql -uroot -p123456 -S /tmp/mysql5630.sock