Implementing MySQL Flashback via Binlog Patch and Event Reversal

MySQL’s binary log (binlog) records all data‑changing statements and is the foundation for replication and point‑in‑time recovery. Flashback, loosely defined as rolling data back to a previous state, can be built on top of binlog by generating reverse operations for each logged change.

The concept originated about seven years ago when Peng Lixun submitted a feature request to Oracle’s MySQL team. Later, MariaDB added native flashback support (since 10.2.4) and open‑source tools such as Meituan’s MyFlash and python‑mysql‑replication provided community implementations. NetEase’s game database team combined these ideas and patched their own MySQL code to deliver a production‑ready flashback feature.

The patch works only when the binlog format is ROW and binlog‑row‑image is set to FULL , ensuring that before‑image (BI) and after‑image (AI) data are available. It defines a “minimal execution unit” consisting of one table_map_event followed by a series of row_event s, which can be reversed independently.

Reversal logic varies by event type: Write_Rows_event is turned into a Delete_Rows_event . Delete_Rows_event becomes a Write_Rows_event . Update_Rows_event swaps its BI and AI parts and reverses the row order. Key code snippets illustrate the process:

main()
|-> dump_multiple_logs();
    |-> dump_single_log(logname);
        |-> dump_local_log_entries(print_event_info, logname)
            |-> check_header(); // verify binlog header
            |-> ev = Log_event::read_log_event(file, glob_description_event, opt_verify_binlog_checksum)
            |-> process_event(print_event_info, ev, old_off, logname)
                // handle each event according to its type

void Rows_log_event::change_to_flashback_event(temp_buf)
{
    for (uchar *value = m_rows_buf; value < m_rows_end; )
    {
        if (ev_type == binary_log::UPDATE_ROWS_EVENT ||
            ev_type == binary_log::UPDATE_ROWS_EVENT_V1)
        {
            length1 = print_verbose_one_row(); // AI length
            value += length1;
            memcpy(swap_buff1, start_pos, length1);
            length2 = print_verbose_one_row(); // BI length
            memcpy(swap_buff2, start_pos + length1, length2);
            // Swap SET and WHERE parts
            memcpy(start_pos, swap_buff2, length2);
            memcpy(start_pos + length2, swap_buff1, length1);
        }
        // store row for later reverse output
        one_row.length = length1 + length2;
        one_row.str = (char *)my_malloc(PSI_NOT_INSTRUMENTED, one_row.length, MYF(0));
        memcpy(one_row.str, start_pos, one_row.length);
        insert_dynamic(&rows_arr, (uchar *)&one_row);
    }
    // Output rows in reverse order
    for (uint i = rows_arr.elements; i > 0; --i)
    {
        LEX_STRING *one_row = dynamic_element(&rows_arr, i - 1, LEX_STRING*);
        memcpy(rows_pos, (uchar *)one_row->str, one_row->length);
        rows_pos += one_row->length;
        my_free(one_row->str);
    }
}

rows_log_event::print_helper()
    |-> print_base64(&print_event_info->body_cache, ev);
        |-> ev->change_to_flashback(tmp_buf);
        |-> my_base64_encode(tmp_buf, (size_t)size, tmp_str);
        |-> my_b_printf(&print_event_info->body_cache, "%s\n", tmp_str);
    // non‑flashback path prints original event body

After reversing each minimal unit, the events are stored in an array and printed in reverse order, effectively generating a flashback binlog that can be applied to restore the database to the desired point.

Beyond MySQL, the article compares flashback with system‑versioned tables introduced in SQL:2011, showing how MariaDB, Oracle, and TiDB implement native multi‑version storage and time‑travel queries. Example DDL for a system‑versioned table and sample AS OF queries are provided.

Practical advice includes: flashback currently supports only DML (no DDL), the server must run in read‑only mode during rollback to avoid conflicts, and careful handling of BI/AI consistency is required to prevent incomplete restores.

The article concludes with a list of references to feature requests, design documents, and open‑source tools that informed the implementation.