通过mysqlbinlog 实现对mysql操作的闪回

简介：
     本文中的mysqlbinlog命令，是通过淘宝--彭立勋改进后的命令。原作者文章： http://www.penglixun.com/tech/database/mysql_flashback_feature.html
     本文中的操作，参考了 吴炳锡的博客。作者连接：http://www.mysqlsupport.cn/mysqlbinlog-flashback/

一、mysqlbinlog使用限制
     1. 本文测试用的mysqlbinlog命令，不是mysql自带的命令，是淘宝--彭立勋改进后的命令。补丁包下载地址：http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
      2. 对binlog格式限制。
         2.1 binlog的格式需要设置为row；
         2.2 binlog 格式设置方法：
               首先查看，当前binglog的格式。结果为mixed。
            mysql> show variables like 'binlog_format';
                +---------------------+------------+               
                | Variable_name  | Value    |               
                +---------------------+------------+               
                | binlog_format   | MIXED   |               
                +----------------------+-----------+               
               然后，修改binlog的格式
          mysql>set global binlog_format=row;         
          mysql>show variables like 'binlog_format';   
                +----------------------+---------+                 
                | Variable_name   | Value |                 
                +----------------------+---------+                 
                | binlog_format     | ROW |                
                +----------------------+---------+                  
          注：binglog_format是动态参数，可在线修改。需注意，mysql参数分session(回话级)和global(全局级)，修改时，需注意。
          官网文档连接：http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
    3. 闪回限制
        mingbinlog的flashback功能，仅针对insert、update、delete做闪回
二、测试过程
     1. 准备测试环境
         1.1 检查binglog格式
              mysql> show variables like 'binlog_format'; 
               +---------------------+------------+                   
                | Variable_name  | Value    |                  
                +---------------------+------------+                  
                | binlog_format   | MIXED   |                  
                +----------------------+-----------+                  
        1.2 查看当前使用的binlog日志
            mysql>show binary logs;               
                 +-------- ------------+------------ ----+  
                  | Log_name      | File_size      | 
                  +------------- -------+------- ---------+ 
                  | binlog.000012 |    820833   | 
                  | binlog.000013 |      7811    |  
                  +---------------------+---------------+ 
             最新的日志为binlog.000013
      1.3 创建测试表
            mysql>create table b (id int);                
             Query OK, 0 rows affected (0.24 sec)    
            mysql>insert into b values(1),(2),(3),(4);
             Query OK, 4 rows affected (0.04 sec)    
            Records: 4  Duplicates: 0  Warnings: 0    
     测试环境创建完毕
   2.测试insert的闪回
      2.1 插入一条数据
            mysql>insert into b values(5);               
            Query OK, 1 row affected (0.04 sec)     
      2.2 查看binlog中的position，包括start position 和stop position
           mysql>show binlog events in 'binlog.000013';                                                                                            
           +---------------------+--------+------------------+--------------+-------------------+------------------------------------------------------------+ 
             | Log_name      | Pos   | Event_type  | Server_id | End_log_pos | Info                                                      | 
            +---------------------+--------+-------------------+-------------+-------------------+------------------------------------------------------------+ 
            | binlog.000013 | 8575 | Xid              |        11  |        8602      | COMMIT /* xid=3634 */                          | 
             | binlog.000013 | 8602 | Query          |        11  |        8676      | BEGIN                                                   | 
             | binlog.000013 | 8676 | Table_map   |        11  |        8722       | table_id: 41 (help_table.b)                      | 
             | binlog.000013 | 8722 | Write_rows  |        11  |        8756       | table_id: 41 flags: STMT_END_F               | 
             | binlog.000013 | 8756 | Xid              |        11  |        8783       | COMMIT /* xid=3635 */                         | 
            +---------------------+---------+------------------+-------------+---------------------+------------------------------------------------------------+
           start position=8602;stop position=8783
        2.3 使用mysqlbinlog命令对insert进行闪回
             首先检查原数据
              mysql>select * from b;
                +-----+                      
                  | id  |                      
                +-----+                       
                 |    1 |                       
                 |    2 |                       
                 |    3 |                       
                 |    4 |                       
                 |    5 |                       
               +------+                       

             mysqlbinlog -B -v --start-position=8602 --stop-position=8783 /home/mysql/binlog.000013|mysql test
              mysql>select * from b;
                 +-----+                      
                  | id  |                      
                +-----+                       
                 |    1 |                       
                 |    2 |                       
                 |    3 |                       
                 |    4 |                       
               +------+                                     
             使用insert 命令插入的第5条数据被删除
   3. binglog的解析差异
       下面看一下，两个mysqlbinlog命令对binlog解析的不同之处；
        3.1 mysql自带的mysqlbinlog命令
             BEGIN
              /*!*/;
             # at 8676
             # at 8722
             #140607 17:59:30 server id 11  end_log_pos 8722         Table_map: `help_table`.`b` mapped to number 41
             #140607 17:59:30 server id 11  end_log_pos 8756         Write_rows: table id 41 flags: STMT_END_F


             BINLOG '
             guKSUxMLAAAALgAAABIiAAAAACkAAAAAAAEACmhlbHBfdGFibGUAAWIAAQMAAQ==
             guKSUxcLAAAAIgAAADQiAAAAACkAAAAAAAEAAf/+BQAAAA==
             '/*!*/;
              ### INSERT INTO help_table.b
              ### SET
              ###   @1=5
             # at 8756
             #140607 17:59:30 server id 11  end_log_pos 8783         Xid = 3635
             COMMIT/*!*/;
             DELIMITER ;
             # End of log file
             ROLLBACK /* added by mysqlbinlog */;
             /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
            可见，正常的mysqlbinlog命令，解析出来的是正行执行的sql语句
      3.2 可以闪回的mysqlbinlog命令
           BINLOG '
           guKSUxMLAAAALgAAABIiAAAAACkAAAAAAAEACmhlbHBfdGFibGUAAWIAAQMAAQ==
           guKSUxkLAAAAIgAAADQiAAAAACkAAAAAAAEAAf/+BQAAAA==
           '/*!*/;
            ### DELETE FROM help_table.b
            ### WHERE
            ###   @1=5
           DELIMITER ;
           # End of log file
           ROLLBACK /* added by mysqlbinlog */;
           /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
           可见，mysqlbinlog命令生成了delete的sql，用于实现闪回
      同理，对update和delete的闪回，也是相同的实现方法
4.总结
   通过mysqlbinlog的B参数，生成相反的sql语句，实现mysql的部分闪回，可以尽量减少对生产环境的影响。
   闪回的过程，可以总结为：
        1. 通过命令"show binlog events in 'binlog'"确定闪回的start position和stop position。如果不确定选择的区间是否正确，可以通过mysqlbinlog命令查看解析的sql是否是自己需要的
        2. 闪回之前，最好对线上的环境进行备份，防止出现因闪回不当，造成的其他数据损失
        3. 确认好闪回区间后，执行命令：mysqlbinlog -v -B --start-position=0 --stop=position=1 /home/mysqll/binlog|mysql test对数据库进行闪回
    注：闪回功能只支持闪回insert update 和delete

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