1、 拷貝老庫(kù)的binlog到恢復(fù)庫(kù)(注意不要把新庫(kù)的日志給覆蓋掉)
大城網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),大城網(wǎng)站設(shè)計(jì)制作,有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為大城上1000+提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)公司要多少錢,請(qǐng)找那個(gè)售后服務(wù)好的大城做網(wǎng)站的公司定做!
2、 解析binlog找出at時(shí)間點(diǎn):
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、 恢復(fù)數(shù)據(jù)(注意要包括use test否則不知道插到哪個(gè)庫(kù)?):
mysqlbinlog mysql-bin.000006 --start-position=3535 --stop-position=3830|mysql -uroot -p123456 -S /tmp/mysql5630.sock