How to Implement flash back in MySQL (MySQLFlashbackFeature)

Anyone who has used the Oracle database knows that Oracle has a FlashRecoveryArea that can write changed blocks to this area. When data operations are incorrect and need to be restored, you can use the data block stored in the flash back space to overwrite it back, or you can reconstruct the rollback segment to restore it to the desired consistency point. Asweknow, TherehasaFlashRecover

Anyone who has used the Oracle database knows that Oracle has a Flash Recovery Area that can write changed blocks to this Area. When data operations are incorrect and need to be restored, you can use the data block stored in the flash back space to overwrite it back, or you can reconstruct the rollback segment to restore it to the desired consistency point. As we know, There has a Flash Recover

Anyone who has used the Oracle database knows that Oracle has a Flash Recovery Area that can write changed blocks to this Area. When data operations are incorrect and need to be restored, you can use the data block stored in the flash back space to overwrite it back, or you can reconstruct the rollback segment to restore it to the desired consistency point.

As we know, There has a Flash Recovery Area in Oracle DB, Which allows the modified blocks been written. so that, if there's any incorrect deletion of data, and need to recover, DBA can use the data blocks which were stored in the Flash Recovery Area, or reconstructed rollback segments, to restore the data to the consistent point.

MySQL/InnoDB does not provide these functions at the moment, but many InnoDB designs refer to Oracle, So I think InnoDB can also implement the Flashback function.

MySQL/InnoDB haven' t got Med this great and useful function before I worked on it, though was designs of InnoDB are referred to Oracle. in this case, I think InnoDB shoshould implement Flashback as well.

At the beginning, I wanted to use the undo log to flash back like Oracle. By marking the TRX of COMMITTED as UNCOMMITTED, InnoDB considered that the COMMITTED transaction was not COMMITTED and then rolled back.

The specific solution is as follows:

At first, I want to implement this feature, Oracle of reference. I can set COMMITTED transactions to UNCOMMITTED status during InnoDB starting with processing undo log. then InnoDB will regard these committed transactions as uncommitted one, and rollback it.

Here are the details:

1. configure a InnoDB_Flashback_Trx_ID parameter in my. cnf to identify the consistent status of the trx_id.

1. Add an option on my. cnf named InnoDB_Flashback_Trx_ID. It mean InnoDB need rollback to this trx snapshot.

2. When InnoDB starts a read rollback segment to construct a rollback transaction, all transactions larger than InnoDB_Flashback_Trx_ID are marked as UNCOMMITTED.

2. When InnoDB starting, and reading undo segments, I will set all transactions that trx_id> InnoDB_Flashback_Trx_ID to UNCOMMITTED.

3. InnoDB considers the committed transactions as not committed, and constructs uncommitted transactions. InnoDB uses its own mechanism to roll back these transactions before opening the database.

3. InnoDB will consider these committed transactions are uncommitted, so construction the trx, and after construction all uncommitted transactions, InnoDB will rollback these transactions.

However, this solution has obvious drawbacks. It can only be applied to InnoDB first, and then the flash-back operation needs to be restarted. In the test of the actual encoding implementation method, it is found that if DDL occurs, if you perform another transient return to the TRX_ID before the DDL operation, InnoDB will crash and cannot be started again. The data file is damaged because InnoDB's undo is a logical record rather than a physical record.

But this way have an Obvious disadvantages, it can only used by InnoDB. and flashback need restart MySQL. in the actual coding I found that if InnoDB did DDL, and I will rollback to the TRX_ID before DDL, InnoDB will crash, and can't start again. I think the datafiles is already upted, because InnoDB undo is logical records, not physical records.

Therefore, the second solution is to use binlog, because if it is a binlog in the ROW format, which records the complete information of each ROW, INSERT will contain the value of each field, DELETE also contains the values of each field. UPDATE will include all the field values in the SET and WHERE sections. Therefore, binlog is a complete logical redo, and the operation is reversed, which is the required "undo ".

The specific solution is as follows:

So I think another way that use binlog. because the ROW format binlog will record whole information about modified rows. INSERT/DELETE will contain all columns 'values. UPDATE will contain all columns 'on SET/WHERE part. so binlog like a whole logical redo log, reversed them can get the "undo" I need. detail:

1. Modify the print result of Row_log_event and convert Event_type reverse: WRITE_ROWS_EVENT to DELETE_ROWS_EVENT/DELETE_ROWS_EVENT to WRITE_ROWS_EVENT. You only need to change the flag bit to 4th bytes ptr [4].

1. Modifying the result of Row_log_event: print that reversed Event_type: Modifying WRITE_ROWS_EVENT to DELETE_ROWS_EVENT/DELETE_ROWS_EVENT to ignore, this change need only modify a byte, that's ptr [4].

2. For UPDATE_ROWS_EVENT, you need to call the SET and WHERE sections. This is the only thing that is relatively troublesome. I added an exchange_update_rows function. The print_verbose_one_row function is used to parse the length of the SET and WHERE parts, so as to infer the Split points between the SET and WHERE, and then use memcpy for switching.

2. with UPDATE_ROWS_EVENT, it need swap SET/WHERE parts. this is the only place has little trouble, I added an exchange_update_rows () function to do it. it will use print_verbose_one_row () to parse the length of SET/WHERE parts, so I can get the cut-point of SET/WHERE parts, and then swap it with memcpy ().

3. After the Event is reversed, the output needs to be reversed. Therefore, I intercepted the output in the memory. I modified the Write_on_release_cache class and added a buff to Log_event to print the print result of the Event in the buff, therefore, mysqlbinlog can get the output of each event, which is stored in the memory.

3. after get the reversed Event, it need reverse the sequence of Events. so I intercepted event output in memory by modifying Write_on_release_cache class, and I added a buff member on Log_event to save the print output. so mysqlbinlog can get all events 'output, and store in memory.

4. in mysqlbinlog, I use a dynamic array to store all event outputs, and then reversely output all events from the end. In this way, we can obtain the flashback Inverse Operation file, importing this file into the target database can flash back.

4. I used DYNAMIC_ARRAY to cache all events 'output in mysqlbinlog. and then I print the events 'output from end to begin, so I get the flashback file. you can import this file to MYSQL, data can flashback.

The benefits of this solution are obvious. It is applicable to all storage engines because binlog is at the Server layer. In addition, you can use various filters existing in mysqlbinlog to filter some logs and output them as rollback logs. In this way, you can flexibly choose to flash back an operation to a specific database, operations in a certain period of time.

The advantage of this way is that all store engines can use it, because binlog is the log of Server. and then, mysqlbinlog have writable filters, such as start-position/start-datatime and so on.

Original article address: design idea for implementing Flash back in MySQL (MySQL Flashback Feature). Thank you for sharing it with me.