mysql恢复--flashback

最新推荐文章于 2024-06-20 21:45:31 发布

skate

最新推荐文章于 2024-06-20 21:45:31 发布

阅读量6.7k

点赞数

分类专栏： Mysql Management

本文链接：https://blog.csdn.net/wyzxg/article/details/9454375

版权

Mysql Management 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

author：skate
time:2013/07/23

mysqlbinlog flashback功能是淘宝彭立勋(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

安装
#cd mysql-5.5.18
#wget 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

安装完之后，看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*/;
[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*/;

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

# /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/

--end--