mysql恢复--flashback

￡神魔★判官ぃ 2021-12-15 15:29 259阅读 0赞

author：skate  
time:2013/07/23   
    
mysqlbinlog flashback功能是淘宝彭立勋([http://www.penglixun.com/][http_www.penglixun.com])开源的一个工具  
主要功能：对rows格式的binlog可以进行逆向操作，如delete反向生成insert，update生成反向的update,insert反向生成delete。在DBA误操作时，可以把数据库恢复到以前某个时间点

1.源码安装

下载：  
[http://mysql.taobao.org/index.php/Patch\_source\_code\#Add\_flashback\_feature\_for\_mysqlbinlog][http_mysql.taobao.org_index.php_Patch_source_code_Add_flashback_feature_for_mysqlbinlog]

安装  
\#cd mysql-5.5.18  
\#wget [http://mysql.taobao.org/images/0/0f/5.5.18\_flashback.diff][http_mysql.taobao.org_images_0_0f_5.5.18_flashback.diff]  
\#patch -p0 <5.5.18\_flashback.diff

2.直接下载二进制文件：[http://download.csdn.net/download/wyzxg/5809143][http_download.csdn.net_download_wyzxg_5809143]

安装完之后，看mysqlbinlog是否带有“-B”参数，如果带有-B，说明具有flashback功能

**使用场景**

场景一：因误操作等原因，需要把数据库恢复到之前某个状态

测试：  
测试表：  
mysql> select \* from zxg;  
\+------+------+  
| a    | b    |  
\+------+------+  
|    1 | 1    |  
|    2 | 2    |  
|    3 | 3    |  
|    5 | 4    |  
\+------+------+  
4 rows in set (0.00 sec)

开始做DML操作  
mysql> insert into zxg select \* from zxg;  
Query OK, 4 rows affected (0.02 sec)  
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into zxg select \* from zxg;  
Query OK, 8 rows affected (0.03 sec)  
Records: 8  Duplicates: 0  Warnings: 0

用mysqlbinlog分析binlog，找到要要恢复的position位置：

\[root@slave92 data\]\# /mysqlinstall/mysqlbinlog  -v --base64-output=decode-rows  --start-position=459 --stop-position=893  mysql-bin.000018  | more  
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;  
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;  
DELIMITER /\*!\*/;  
\# at 459  
\#130723 22:19:15 server id 2  end\_log\_pos 530   Query   thread\_id=141   exec\_time=0     error\_code=0  
SET TIMESTAMP=1374589155/\*!\*/;  
SET @@session.pseudo\_thread\_id=141/\*!\*/;  
SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;  
SET @@session.sql\_mode=0/\*!\*/;  
SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;  
/\*!\\C latin1 \*//\*!\*/;  
SET @@session.character\_set\_client=8,@@session.collation\_connection=8,@@session.collation\_server=33/\*!\*/;  
SET @@session.lc\_time\_names=0/\*!\*/;  
SET @@session.collation\_database=DEFAULT/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 530  
\# at 578  
\#130723 22:19:15 server id 2  end\_log\_pos 578   Table\_map: \`testdb2\`.\`zxg\` mapped to number 44  
\#130723 22:19:15 server id 2  end\_log\_pos 635   Write\_rows: table id 44 flags: STMT\_END\_F  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=5  
\#\#\#   @2='4'  
\# at 635  
\#130723 22:19:15 server id 2  end\_log\_pos 662   Xid = 2290949  
COMMIT/\*!\*/;  
\# at 662  
\#130723 22:19:17 server id 2  end\_log\_pos 733   Query   thread\_id=141   exec\_time=0     error\_code=0  
SET TIMESTAMP=1374589157/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 733  
\# at 781  
\#130723 22:19:17 server id 2  end\_log\_pos 781   Table\_map: \`testdb2\`.\`zxg\` mapped to number 44  
\#130723 22:19:17 server id 2  end\_log\_pos 866   Write\_rows: table id 44 flags: STMT\_END\_F  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=5  
\#\#\#   @2='4'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# INSERT INTO testdb2.zxg  
\#\#\# SET  
\#\#\#   @1=5  
\#\#\#   @2='4'  
\# at 866  
\#130723 22:19:17 server id 2  end\_log\_pos 893   Xid = 2290950  
COMMIT/\*!\*/;  
DELIMITER ;  
\# End of log file  
ROLLBACK /\* added by mysqlbinlog \*/;  
/\*!50003 SET [COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/][COMPLETION_TYPE_OLD_COMPLETION_TYPE];  
\[root@slave92 data\]\#

说明：如果binlog很大，可以搜索表名，找到start-position和stop-position，在这例子中可以看找到的position是否可记录的一样。

用mysqlbinlog验证flashback的binlog是否和预期的一样

\[root@slave92 data\]\# /mysqlinstall/mysqlbinlog  -B -v --base64-output=decode-rows  --start-position=459 --stop-position=893  mysql-bin.000018  | more  
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;  
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;  
DELIMITER /\*!\*/;  
\# at 459  
\# at 530  
\# at 578  
\# at 635  
\# at 662  
\# at 733  
\# at 781  
\# at 866  
DELIMITER ;  
\#130723 22:19:17 server id 2  end\_log\_pos 893   Xid = 2290950  
COMMIT/\*!\*/;  
\#130723 22:19:17 server id 2  end\_log\_pos 781   Table\_map: \`testdb2\`.\`zxg\` mapped to number 44  
\#130723 22:19:17 server id 2  end\_log\_pos 866   Write\_rows: table id 44 flags: STMT\_END\_F  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=5  
\#\#\#   @2='4'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=5  
\#\#\#   @2='4'  
\#130723 22:19:17 server id 2  end\_log\_pos 733   Query   thread\_id=141   exec\_time=0     error\_code=0  
SET TIMESTAMP=1374589157/\*!\*/;  
BEGIN  
/\*!\*/;  
\#130723 22:19:15 server id 2  end\_log\_pos 662   Xid = 2290949  
COMMIT/\*!\*/;  
\#130723 22:19:15 server id 2  end\_log\_pos 578   Table\_map: \`testdb2\`.\`zxg\` mapped to number 44  
\#130723 22:19:15 server id 2  end\_log\_pos 635   Write\_rows: table id 44 flags: STMT\_END\_F  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=1  
\#\#\#   @2='1'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=2  
\#\#\#   @2='2'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=3  
\#\#\#   @2='3'  
\#\#\# DELETE FROM testdb2.zxg  
\#\#\# WHERE  
\#\#\#   @1=5  
\#\#\#   @2='4'  
DELIMITER ;  
\# End of log file  
ROLLBACK /\* added by mysqlbinlog \*/;  
/\*!50003 SET [COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/][COMPLETION_TYPE_OLD_COMPLETION_TYPE];

确认无误的话，就可以恢复了

\# /mysqlinstall/mysqlbinlog  -B   --start-position=459 --stop-position=893  mysql-bin.000018 | mysql testdb2

然后再查表zxg数据，发现表zxg已经恢复，如下

mysql> select \* from zxg;  
\+------+------+  
| a    | b    |  
\+------+------+  
|    1 | 1    |  
|    2 | 2    |  
|    3 | 3    |  
|    5 | 4    |  
\+------+------+  
4 rows in set (0.00 sec)

说明：在生产环境这种场景使用不现实，但在开发测试环境或独有数据库，这种场景到可以一用，用之前最好备好

场景二：因对某个表误操作，只需要把表恢复到误操作之前

这种场景实际遇到比较多，只是在场景一的基础上把误操作的binlog位置找到，然后用mysqlbinlog反向解析，为了安全起见，  
最好在另外一台机器上，创建相同的表(所在数据库也要一样)，然后在此基础上恢复，最后在把恢复结果导入源库中。这样比较安全

参考：  
[http://www.mysqlsupport.cn/mysqlbinlog-flashback/][http_www.mysqlsupport.cn_mysqlbinlog-flashback]

\--end--

[http_www.penglixun.com]: http://www.penglixun.com/
[http_mysql.taobao.org_index.php_Patch_source_code_Add_flashback_feature_for_mysqlbinlog]: http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog
[http_mysql.taobao.org_images_0_0f_5.5.18_flashback.diff]: http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
[http_download.csdn.net_download_wyzxg_5809143]: http://download.csdn.net/download/wyzxg/5809143
[COMPLETION_TYPE_OLD_COMPLETION_TYPE]: mailto:COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
[http_www.mysqlsupport.cn_mysqlbinlog-flashback]: http://www.mysqlsupport.cn/mysqlbinlog-flashback/