欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL闪回技术恢复误操作数据

程序员文章站 2022-05-30 09:42:08
...

我之前写过一篇《update忘加where条件误操作恢复过程演示》,是通过分析binlog,把binlog用shell转换成误操作之前的SQL语句,再导入到数据库中恢复,这个操作过

我之前写过一篇《update忘加where条件误操作恢复过程演示》,是通过分析binlog,把binlog用shell转换成误操作之前的SQL语句,再导入到数据库中恢复,这个操作过程很复杂。


如今,淘宝开发了一个闪回工具,,项目主页:,原理同上,但操作过程方便了很多。


下面我来演示一下:


1、下载MySQL5.5.18源代码

2、下载闪回补丁

# wget

3、打补丁

# cd mysql-5.5.18

# patch -p0

4、编译MySQL并安装

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql_flashback
# make;make install


操作:

mysql> select * from t1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec) mysql> update t1 set id=id+10; Query OK, 8 rows affected (0.00 sec) Rows matched: 8 Changed: 8 Warnings: 0


首先把那条误操作的语句找出来,并且得到Position点。

root@m1:/var/log/mysql# mysqlbinlog -vv mysql-bin.000001 |egrep -i -C 20 'update|t1' |more ROLLBACK/*!*/; BINLOG ' tpuGUg8BAAAAZwAAAGsAAAABAAQANS41LjMxLTArd2hlZXp5MS1sb2cAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAC2m4ZSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #131116 6:10:01 server id 1 end_log_pos 175 Query thread_id=44 exec_time=0 error_code=0 SET TIMESTAMP=1384553401/*!*/; SET @@session.pseudo_thread_id=44/*!*/; 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 utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 175 # at 216 #131116 6:10:01 server id 1 end_log_pos 216 Table_map: `test`.`t1` mapped to number 42 #131116 6:10:01 server id 1 end_log_pos 326 Update_rows: table id 42 flags: STMT_END_F BINLOG ' uZuGUhMBAAAAKQAAANgAAAAAACoAAAAAAAEABHRlc3QAAnQxAAEDAAA= uZuGUhgBAAAAbgAAAEYBAAAAACoAAAAAAAEAAf///gEAAAD+CwAAAP4CAAAA/gwAAAD+AwAAAP4N AAAA/gQAAAD+DgAAAP4FAAAA/g8AAAD+BgAAAP4QAAAA/gcAAAD+EQAAAP4IAAAA/hIAAAA= '/*!*/; ### UPDATE `test`.`t1` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=11 /* INT meta=0 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=12 /* INT meta=0 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=14 /* INT meta=0 nullable=0 is_null=0 */ --More--


开始的起点以107为准,因为107下面紧跟着BEGIN,结束的点以end_log_pos 326下一个点为准,


root@m1:/var/log/mysql# mysqlbinlog -vv --start-position=326 mysql-bin.000001 |more /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #131116 6:09:58 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.31-0+wheezy1-log created 131116 6:09:58 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' tpuGUg8BAAAAZwAAAGsAAAABAAQANS41LjMxLTArd2hlZXp5MS1sb2cAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAC2m4ZSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 326 #131116 6:10:01 server id 1 end_log_pos 395 Query thread_id=44 exec_time=0 error_code=0 SET TIMESTAMP=1384553401/*!*/; SET @@session.pseudo_thread_id=44/*!*/; 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 utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; COMMIT /*!*/; DELIMITER ; --More--

这里

# at 326
#131116 6:10:01 server id 1 end_log_pos 395


那么结束的点就是395,因为下面紧跟着COMMIT


现在我们已经找到了开始起点为107,结束点为395,下面开始恢复。


# mysqlflashback -B -vv --start-position=107 --stop-position=395 mysql-bin.000001 | mysql


mysql> select * from t1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec)


大功告成。


同样的方法,delete操作也一样


mysql> select * from t1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec) mysql> delete from t1; Query OK, 8 rows affected (0.08 sec) mysql> select * from t1; Empty set (0.00 sec)


我们可以看到binlog里,把刚刚delete的行,变成了insert

MySQL闪回技术恢复误操作数据


注:这里一定要设置binlog格式ROW,否则不能恢复。


mysqlflashback我已经编译完,如果嫌麻烦自己不想编译,请在附件里下载我编译好的,64位版本。



本文出自 “贺春旸的技术专栏” 博客,请务必保留此出处