binlog增量备份需要注意的地方，会死人的！！！

justin033

最近公司备份方式在调整，采用周日全库+每小时binlog增量备份。我这里说下有两点需要注意的地方，体现了mysql在备份功能的差强人意。肯定还有方面，欢迎大家的经验和心得


1. 用到临时表时，恢复binlog需要注意，只能在一个session里恢复所有binlog文件的数据。因为临时表只存在于单个session时间

2. 针对5.0版本的，一定要注意statement binlog模式对create table b select * from a; 只能记录语句而不是数据。如果表a删除了，恢复表b的数据就麻烦了

###刚开始binlog_format为row
create table tbl_a (a int)
/*!*/;
# at 195
#100422 23:23:20 server id 1  end_log_pos 263   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949800/*!*/;
BEGIN
/*!*/;
# at 263
# at 307
#100422 23:23:20 server id 1  end_log_pos 307   Table_map: `test`.`tbl_a` mapped
to number 135
#100422 23:23:20 server id 1  end_log_pos 351   Write_rows: table id 135 flags:
STMT_END_F

BINLOG '
6GnQSxMBAAAALAAAADMBAAAAAIcAAAAAAAAABHRlc3QABXRibF9hAAEDAAE=
6GnQSxcBAAAALAAAAF8BAAAQAIcAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
# at 351
#100422 23:23:20 server id 1  end_log_pos 420   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949800/*!*/;
COMMIT
/*!*/;
# at 420
#100422 23:24:43 server id 1  end_log_pos 488   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
BEGIN
/*!*/;
# at 488


###binlog_format没变，通过create table select from来创建表。在row_based mode下。创建的语句，变成先创建表再插入值
#100422 23:24:43 server id 1  end_log_pos 602   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
CREATE TABLE `tbl_b` (
  `a` int(11) DEFAULT NULL
)
/*!*/;
# at 602
# at 646
#100422 23:24:43 server id 1  end_log_pos 646   Table_map: `test`.`tbl_b` mapped
to number 136
#100422 23:24:43 server id 1  end_log_pos 690   Write_rows: table id 136 flags:
STMT_END_F

BINLOG '
O2rQSxMBAAAALAAAAIYCAAAAAIgAAAAAAAAABHRlc3QABXRibF9iAAEDAAE=
O2rQSxcBAAAALAAAALICAAAQAIgAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
# at 690
#100422 23:24:43 server id 1  end_log_pos 759   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
COMMIT
/*!*/;

###接着把session的binlog_format设置成statement，通过create table select from来创建表。对create table b select * from a; 只能记录语句而不是数据
# at 759
#100422 23:27:57 server id 1  end_log_pos 860   Query   thread_id=1     exec_tim
e=1     error_code=0
SET TIMESTAMP=1271950077/*!*/;
create table tbl_c select * from tbl_a
/*!*/;

###最后把session的binlog_format设置成mixed，通过create table select from来创建表。对create table b select * from a; 只能记录语句而不是数据
# at 860
#100422 23:30:04 server id 1  end_log_pos 961   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271950204/*!*/;
create table tbl_d select * from tbl_a
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

andkylee

怎么个死法？

justin033

1. 用到临时表时，恢复binlog需要注意，只能在一个session里恢复所有binlog文件的数据。因为临时表只存在于单个session时间

--- 如果用到临时表的事务数据跨了多个binlog文件，就得用一个session恢复此多个binlog文件


2. 针对5.0版本的，一定要注意statement binlog模式对create table b select * from a; 只能记录语句而不是数据。如果表a删除了，恢复表b的数据就麻烦了

--- 如果是statement based binlog，这样种情况你就得先恢复a表再恢复b表

andkylee

回复 3# justin033


    create table b select * from a; 只能记录语句而不是数据。如果表a删除了，恢复表b的数据就麻烦了


表a被删除，难道表b也被删除了？


这样的话，和这条 create table b select * from a语句就没有关系了。


直接转到另外一个问题：　如何利用日志恢复误操作导致的表b被删除？

yueliangdao0608

分析的很好，鼓励下。

lh0365

楼主牛啊。这些天一直在研究MYSQL的增量备份，不知道大家有没有好的方法

ruochen

LZ很不错

最近学习的尽头很足啊，希望能多多记笔记，然后共享出来

vitas333_cu

临时表的意思？？  恢复的话 还得如何操作？？