Data Recovery Methods

Recovering Deleted Rows

If you accidentally delete data rows using a DELETE statement, you can recover the data using the Flashback tool. The Flashback tool works by modifying the contents of the binlog, allowing you to replay the data back into the original database. To use this method, ensure that binlog_format is set to row and binlog_row_image is set to FULL.

When recovering individual transactions, follow these steps:

  1. For INSERT statements (Write_rows event in the binlog), change them to DELETE statements (Delete_rows event).

  2. Similarly, for DELETE statements (Delete_rows event in the binlog), change them to INSERT statements (Write_rows event).

  3. If the event is an Update_rows event, which records both the old and new values of the rows, swap the positions of these two rows.

Recovering Deleted Databases or Tables

To recover data when an entire database or table is deleted, you’ll need to rely on full backups and incremental logs. This method assumes that you have regular full backups and real-time backups of binlogs.

For instance, if someone deletes a database at 12 PM, follow these steps:

  1. Retrieve the latest full backup, assuming it’s taken daily at midnight.

  2. Restore a temporary copy of the database from the backup.

  3. Extract the binlog entries from after midnight.

  4. Apply all these binlog entries, excluding the statements responsible for the accidental deletion, to the temporary copy.

Recovering from rm Data Deletion

In a MySQL cluster with a high-availability mechanism, data deletion with rm isn’t a major concern. As long as it doesn’t destroy the entire cluster and only affects one node’s data, the HA system will elect a new master and ensure the cluster’s normal operation. Your task in such a scenario is to restore the data on the affected node and reintegrate it into the cluster.

Killing a Query Thread

When a user executes KILL QUERY thread_id_B, MySQL’s thread handling for the KILL command does two things:

  1. Changes the execution state of session B to THD::KILL_QUERY (sets the variable killed to THD::KILL_QUERY).

  2. Sends a signal to the execution thread of session B.

Impact of Full Table Scans on Server Layer

InnoDB stores data on the primary key index, making full table scans equivalent to scanning the primary key index of table t. Each row retrieved can be placed directly into the result set and sent to the client.

The server doesn’t need to maintain a complete result set. Data retrieval and sending follow this process:

  1. Retrieve one row and write it to the net_buffer. The size of this memory block is defined by the net_buffer_length parameter, typically set to 16KB.

  2. Keep retrieving rows until the net_buffer is full, then send it through the network interface.

  3. If the send operation succeeds, clear the net_buffer and continue with the next row.

  4. If the send function returns EAGAIN or WSAEWOULDBLOCK, indicating the local network stack’s send buffer is full, enter a wait state until the network stack becomes writable again, then resume sending.

MySQL operates in a “read and send” manner, which means that if the client is slow to receive, it can prolong the execution time of a transaction.

Impact of Full Table Scans on InnoDB

InnoDB uses a Least Recently Used (LRU) algorithm for memory management. This algorithm prioritizes evicting the least recently used data.

InnoDB’s LRU algorithm for managing the Buffer Pool uses a linked list.

  1. In state 1 of the diagram, P1 is at the head of the list, indicating it was the most recently accessed data page. Let’s assume that memory can only hold this many data pages.

  2. At this point, a read request accesses P3, moving it to the front as state 2.

  3. State 3 indicates that the accessed data page doesn’t exist in the list, so a new data page Px is allocated in the Buffer Pool. However, since memory is full, a data page Pm at the end of the list is cleared, and Px’s content is stored in it, moving Px to the front.

Effectively, the data page Pm, which hasn’t been accessed for the longest time, is evicted.

Imagine scanning a 200GB table using this algorithm, especially for a historical data table that isn’t typically accessed. The scanning process would replace the contents of the Buffer Pool with the scanned data pages, leading to a drop in Buffer Pool hit rate and slower SQL response times.

InnoDB cannot use this LRU algorithm directly. In reality, InnoDB has improved upon the LRU algorithm.

In InnoDB’s implementation, the LRU list is split into a young area and an old area in a 5:3 ratio. LRU_old points to the start of the old area, which is at 5/8 of the list. This means that the first 5/8 of the list is the young area, while the last 3/8 is the old area.

The improved LRU algorithm works as follows:

  1. In state 1, when accessing data page P3, since it’s in the young area, it’s moved to the front, becoming state 2.

  2. When accessing a new data page not in the current list, it will be placed in the LRU_old section if memory is full.

  3. Data pages in the old area, when accessed, will be judged based on how long they have been in the list:

    • If a data page has been in the LRU list for more than 1 second, it will be moved to the front.
    • If a data page has been in the list for less than 1 second, its position remains unchanged. The 1-second duration is controlled by the innodb_old_blocks_time parameter, with a default value of 1000 milliseconds.

Suppose you scan a 200GB table using this algorithm, and the table contains historical data with infrequent access. In this case, the scanning process will effectively replace the contents of the Buffer Pool with data from the historical table.

For a production database serving live business operations, this is not ideal. The Buffer Pool’s memory hit rate drops, disk I/O pressure increases, and SQL query responses become slower.