MySQL Flash Back Tool Myflash

I. INTRODUCTION

背景: Due to operations, mis-operation or business bugs, we occasionally have accidental deletion of data in the operation. In the early days to recover data, it was time-consuming and error-prone for the business to construct data that was mistakenly deleted based on the online operation logs, or the DBA used Binlog and backup to recover the data.

MyFlashis a tool for the company's technical engineering department to develop and maintain a rollback DML operation. The tool completes the rollback operation by parsing the V4 version of Binlog. Relative to the existing rollback tool, it adds more filtering options, making rollback easier.

Two. Recovery Tool comparison

1. mysqlbinlog工具配合sed、awk . This method first parses the binlog into SQL-like text, and then uses SED, awk to convert the class-SQL text into real SQL.

• Pros: When the field type in SQL is simple, you can quickly generate the SQL you need and the programming threshold is low.
• Cons: When the field types in SQL are complex, especially when the text in a field contains HTML code, with tools such as awk, SED, and so on, you need to consider extremely complex escapes, and the probability of error is great.

2. Patch the database source code. 该方式扩展了mysqlbinlog的功能，增加Flashback选项.

• Pros: Reuse code such as Binlog parsing in MySQL server layer, once stabilized, no need to care about complex field types and be more efficient.
• Cons: Before you modify it, you need to have a deeper understanding of the structure and details of your MySQL copy code. Version is more sensitive, patches made on MySQL 5.6 are not available for MySQL 5.7 rollback operations. Upgrades are difficult because patch code is distributed across MySQL files and functions, and once the MySQL code changes, especially the refactoring of the copy layer, the upgrade is as hard as a complete re-write.

3. Using the industry-provided library of parsing binlog and then SQL constructs, its excellent representative is binlog2sql .

• Pros: With the industry's proven library, the stability is good and the difficulty of getting started is low.
• Cons: Efficiency is often low, and implementation is subject to the functionality provided by the Binlog library.

4. MyFlash Features

• 无需把binlog解析成文本，再进行转换。
• 提供原生的基于库、表、SQL类型、位置、时间等多种过滤方式。
• Supports multiple versions of MySQL (5.6 and 5.7)
• The database is not sensitive to code refactoring, which facilitates the upgrade.
• Autonomous control of Binlog parsing, providing the most flexible way possible.

Three. Myflash Installation

1. Extract
   unzip MyFlash-master.zip
2. Dynamic compilation
   gcc -w pkg-config --cflags --libs glib-2.0 source/binlogParseGlib.c -o binary/flashback

Four. Myflash use 4.1. Parameters

All parameters should be viewed by--help, the following refers to the enumeration of some common parameters.

• Databasenames
• Specifies the name of the database that needs to be rolled back. Multiple databases can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all databases.
• Tablenames
• Specifies the name of the table that needs to be rolled back. Multiple tables can be separated by ",". If you do not specify this parameter, it is equivalent to specifying all tables.
• Start-datetime
• Specifies the start time of the rollback. Note The format must be%y-%m-%d%h:%m:%s. If not specified, the time is not limited
• Stop-datetime
• Specifies the end time of the rollback. Note The format must be%y-%m-%d%h:%m:%s. If not specified, the time is not limited
• SqlTypes
• Specifies the type of SQL that needs to be rolled back. The filter types currently supported are INSERT, UPDATE, DELETE. Multiple types can be separated by ",".
• Binlogfilenames
• Specifies the Binlog file that needs to be rolled back, currently supports only a single file, and subsequent additions to multiple file support.
• Outbinlogfilenamebase
• Specifies the Binlog file prefix for the output, which, if not specified, defaults to Binlog_output_base.flashback.

4.2. Example

Rollback bin.000009, all SQL related to the delete operation on the Messages_center table, after ' 2018-04-17 15:00:00 '.

1. Filter and regenerate the rollback binary log
./flashback --databaseNames stdzwfw9release --tableNames Messages_Center --start-datetime 2018-04-17 15:00:00 --sqlTypes DELETE --binlogFileName bin.000009

2. Apply the generated rollback log
mysqlbinlog binlog_output_base.flashback | mysql -h

Five. Personal summary

The error deletion recovery under MySQL is often encountered, before our common practice is to generate rollback data through the Mysqlbinlog flashback parameter, but sometimes it is troublesome, because it is not possible to select only one type of operation, so the recovered data will cover other types of operations. It takes time and effort to manually filter again.

Myflash parameters can choose DML operation, then recovery is much simpler, specify the recovery of the BINLOG,DML operation, the time period of recovery, you can generate data, roll back.

Appendix

Https://github.com/Meituan-Dianping/MyFlash

MySQL Flash Back Tool Myflash