MySQL闪回工具之：binlog2sql  https://github.com/danfengcao/binlog2sql

MYSQL Binglog分析利器:binlog2sql使用详解  ：http://blog.itpub.net/27067062/viewspace-2135398/

实践

利用binlog2sql查询两个binlog之间的SQL：必须是两个binlog日志，指定start-file和stop-file

binlog2sql -h127.0.0.1 -P3309 -udba -p'xxxxxx' -dsakila -t employee --start-file='mysql-bin.000112' --stop-file='mysql-bin.000113' > /tmp/db.sql

利用binlog2sql查询两个binlog之间的闪回SQL：

binlog2sql --flashback -h127.0.0.1 -P3309 -udba -p'xxxxxx' -dsakila -t employee --start-file='mysql-bin.000112' --stop-file='mysql-bin.000113' > /tmp/flashback.sql

美团点评MyFlash工具   https://github.com/Meituan-Dianping/MyFlash

http://blog.itpub.net/29987453/viewspace-2151627

数据恢复测试案例DELETE：
1）在sakila库，删除actors表的actor_id < 6的数据
mysql> use sakila;
Database changed
mysql> select * from actors limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 120 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> delete from actors where actor_id < 6;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from actors limit 10;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
| 12 | KARL | BERRY | 2006-02-15 04:34:33 |
| 13 | UMA | WOOD | 2006-02-15 04:34:33 |
| 14 | VIVIEN | BERGEN | 2006-02-15 04:34:33 |
| 15 | CUBA | OLIVIER | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
10 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 4836 |
+------------------+-----------+
2 rows in set (0.00 sec)

2）确定binlog 确认start position、stop position
[root@prod logs]#mysqlbinlog --base64-output=decode-rows -v -d sakila mysql-bin.000006 > /tmp/delactors.sql

BEGIN

/*!*/;

# at 4601

#181019 8:40:49 server id 33051132 end_log_pos 4660 CRC32 0x9c986f17 Table_map: `sakila`.`actors` mapped to number 94

# at 4660

#181019 8:40:49 server id 33051132 end_log_pos 4805 CRC32 0x5839b8ff Delete_rows: table id 94 flags: STMT_END_F

### DELETE FROM `sakila`.`actors`

### WHERE

### @1=1 /* SHORTINT meta=0 nullable=0 is_null=0 */

### @2='PENELOPE' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @3='GUINESS' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @4=1139949273 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### DELETE FROM `sakila`.`actors`

### WHERE

### @1=2 /* SHORTINT meta=0 nullable=0 is_null=0 */

### @2='NICK' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @3='WAHLBERG' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @4=1139949273 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### DELETE FROM `sakila`.`actors`

### WHERE

### @1=3 /* SHORTINT meta=0 nullable=0 is_null=0 */

### @2='ED' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @3='CHASE' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @4=1139949273 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### DELETE FROM `sakila`.`actors`

### WHERE

### @1=4 /* SHORTINT meta=0 nullable=0 is_null=0 */

### @2='JENNIFER' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @3='DAVIS' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @4=1139949273 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

### DELETE FROM `sakila`.`actors`

### WHERE

### @1=5 /* SHORTINT meta=0 nullable=0 is_null=0 */

### @2='JOHNNY' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @3='LOLLOBRIGIDA' /* VARSTRING(135) meta=135 nullable=0 is_null=0 */

### @4=1139949273 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

# at 4805

#181019 8:40:49 server id 33051132 end_log_pos 4836 CRC32 0xa24d4dbb Xid = 143

COMMIT/*!*/;

# at 4836

# at 4915

#181019 8:45:55 server id 33051132 end_log_pos 4962 CRC32 0xf0172e13 Rotate to mysql-bin.000007 pos: 4

COMMIT /* added by mysqlbinlog *//*!*/;

DELIMITER ;

# End of log file

binlog

3）生成闪回binlog_output_base.flashback文件

binlog的偏移量
start-positon=4601
stop-position=4962
[root@prod logs]# flashback --binlogFileNames=/data/mysql_33051/logs/mysql-bin.000006 --start-position=4601 --stop-position=4962
[root@prod logs]# ls
binlog_output_base.flashback
4）闪回数据
[root@cgdb logs]#mysqlbinlog --skip-gtids /data/mysql_33051/logs/binlog_output_base.flashback |mysql -uroot -p -S /data/mysql_33051/run/mysql.sock
验证恢复的数据：
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from actors limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

actors表的actor_id < 6的数据，已经恢复！

最新文章

  1. ASP.NET Button、ImageButton、LinkButton、HyperLink区别
  2. (转)C# 打印PDF文件使用第三方DLL
  3. C# 自定义特性
  4. Python 学习---------Ｄay3
  5. Composer ： php依赖管理工具
  6. iOS全局调用的提示 没有网络 没有更多 等。。 短时间内自动消失
  7. AutoEventWireup=&quot;false&quot;
  8. 奇异值分解（We Recommend a Singular Value Decomposition）
  9. DataBase 之 表操作
  10. MocorDroid编译工程快速建立编译环境
  11. 3月题外：关于GeoServer和OpenLayers3实用开源插件或组件的总结
  12. 第二次作业评分可能要 delay 一些
  13. 二分算法C实现
  14. spring framework体系结构及内部各模块jar之间的maven依赖关系
  15. RPM-GPG-KEY详解
  16. JAVA 程序的基本语法
  17. 【软件工程1916|W（福州大学）_助教博客】团队第四次作业（第7次）成绩公示
  18. IOS中的三大事件
  19. 在后台运行rtorrent
  20. Python 爬虫-正则表达式

热门文章

  1. bfs（同一最短路径）
  2. html5动画之等待加载动画
  3. nodeJS打包安装和问题处理
  4. 分布式事务中的2PC和3PC
  5. Cockpit- Linux 服务器管理接口
  6. FPGA异步时钟系统中信号处理之单比特信号
  7. Python实现IP地址归属地查询
  8. VMware虚拟机NAT模式无法上外网
  9. JVM Direct Memory
  10. Kettle整理