What’s Time Machine

• Rolling back instances/databases/tables to a snapshot
• Implement on Server-Level, so support all engines.
• By full image format binary logs
• Currently, it’s a feature inside mysqlbinlog tool (with --flashback option)

How Time Machine Works

• As we know, if binlog_format is ROW, all columns’ values are store in the row event, so we can
  get the data before mis-operation.
• Just do following things:
  • Change Event Type, INSERT->DELETE, DELETE->INSERT
  • For Update_Event, swapping the SET part and WHERE part
  • Applying those events from the last one to the first one which mis-operation happened.
  • All the data will be recovered by inverse operations of mis-oprerations

DDL supports are necessary

• For ADD INDEX/COLUMN, or CREATE TABLE query, just drop the index, column, table when running Flashback.
• For DROP INDEX/COLUMN, or DROP TABLE query, will copy or rename the old table to a reserved database. When Flashback is running, I can drop new table, and rename the saved old table to original database.
• For TRUNCATE table, I just rename old table to reserved database and create a new empty table.

How to Use Time Machine

• I will create a Time Machine script to package mysqlbinlog --flashback command