Accidentally Deleting Rows
If you accidentally delete data rows using the delete statement, you can use the Flashback tool to restore the data via flashback. The principle of Flashback restoring data is to modify the contents of the binlog and replay it in the original library. The prerequisites for using this solution are ensuring that binlog_format=row and binlog_row_image=FULL. When specifically restoring data, handle individual transactions as follows:
- For insert statements, the corresponding binlog event type is Write_rows event, just change it to Delete_rows event.
- Similarly, for delete statements, change the Delete_rows event to Write_rows event.
- If it is Update_rows, the binlog records the values before and after the data row modification, and you just need to switch these two rows.
Accidentally Deleting a Database/Table
In this case, to restore data, you need to use a full backup combined with incremental logs. This solution requires regular full backups online and real-time backup of binlog. If someone accidentally deletes a database at noon, the process of restoring data is as follows:
- Take the most recent full backup, assuming the database is backed up daily, and the last backup was at 0:00 that day.
- Use the backup to restore a temporary database.
- Extract the logs after midnight from the log backup.
- Apply all these logs, except the statement that accidentally deleted data, to the temporary database.
Data Deletion with rm
For a MySQL cluster with high availability mechanisms, the least worrisome is data deletion with rm. As long as the entire cluster is not maliciously deleted and only the data of a certain node is deleted, the HA system will start working, select a new primary library, and ensure the normal operation of the entire cluster. At this time, what you need to do is to restore the data on this node and reconnect it to the entire cluster.
When a user executes kill query thread_id_B, the thread processing the kill command in MySQL does two things:
- Change the running status of session B to THD::KILL_QUERY (assign the variable killed as THD::KILL_QUERY).
- Send a signal to the execution thread of session B.
The Impact of Full Table Scan on the Server Layer
The data of InnoDB is stored on the primary key index, so a full table scan is actually a direct scan of the primary key index of table t. Each row found can be directly put into the result set and then returned to the client. The server does not need to save a complete result set. The process of taking data and sending data is as follows:
- Obtain a row, write it into the net_buffer. The size of this memory is defined by the parameter net_buffer_length, which defaults to 16k.
- Repeatedly retrieve rows until the net_buffer is full, call the network interface to send it out.
- If the sending is successful, clear the net_buffer, then continue to take the next row and write it into the net_buffer.
- If the sending function returns EAGAIN or WSAEWOULDBLOCK, it means that the local network stack (socket send buffer) is full and waits until the network stack is writable again, then continues to send.
MySQL is "reading and sending at the same time", which means that if the client receives slowly, it will cause the execution time of this transaction on the MySQL server side to increase due to the inability to send out the results.
The Impact of Full Table Scan on InnoDB
InnoDB memory management uses the Least Recently Used (LRU) algorithm, the core of which is to eliminate the least used data.
The LRU algorithm of InnoDB managing the Buffer Pool is implemented by a linked list.
- In the state 1 in the figure, the head of the list is P1, which indicates that P1 is the data page that has just been accessed recently; suppose only so many data pages can be put in memory;
- At this time, there is a read request to access P3, so it becomes state 2, and P3 is moved to the front;
- State 3 indicates that the data page accessed this time does not exist in the list, so a new data page Px is needed in the Buffer Pool, added to the top of the list. However, since the memory is already full, new memory cannot be applied. Therefore, the memory of the data page Pm at the end of the list will be emptied, the content of Px will be stored, and then it will be placed at the top of the list.
- In effect, the data page Pm, which has not been accessed for the longest time, is eliminated. Assuming that according to this algorithm, a 200G table is scanned, and this table is a historical data table, and it is not accessed by the business usually. Then, scanning according to this algorithm will eliminate all the data in the current Buffer Pool, and store the content of the data pages accessed during the scanning process. That is to say, the Buffer Pool mainly contains data from this historical data table.
For a library that is doing business services, this is not good. The memory hit rate of the Buffer Pool drops sharply, the disk pressure increases, and the SQL statement response becomes slower.
InnoDB cannot directly use this LRU algorithm. In fact, InnoDB has improved the LRU algorithm.
In the InnoDB implementation, the entire LRU list is divided into a young area and an old area in a ratio of 5:3. The LRU_old in the figure points to the first position of the old area, which is 5/8 of the entire list. That is, 5/8 of the list head is the young area, and 3/8 of the list tail is the old area.
The execution process of the improved LRU algorithm has changed to the following.
- In state 1 in the figure, to access data page P3, since P3 is in the young area, just like the LRU algorithm before optimization, move it to the top of the list, and become state 2.
- Then you want to access a new data page that does not currently exist in the list. At this time, you still eliminate data page Pm, but the newly inserted data page Px is placed at LRU_old.
- The data page in the old area needs to do the following judgment every time it is accessed:
- If this data page has been in the LRU list for more than 1 second, move it to the top of the list;
- If this data page has been in the LRU list for less than 1 second, the position remains the same. This 1 second time is controlled by the parameter innodb_old_blocks_time. Its default value is 1000, in milliseconds.
