基于时间点的恢复

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/*!*/;
/*!\C latin1 *//*!*/;
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 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t10`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t10`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t10`
### SET
###   @1=201 /* INT meta=0 nullable=0 is_null=0 */
# 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` /* generated by server */
/*!*/;
# 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29313086/viewspace-2105871/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29313086/viewspace-2105871/