Mysql accidental deletion of data solution and kill statement principle

mysql accidentally deleted data

• Using the delete statement to accidentally delete a row of data
• Use drop table or truncate table to accidentally delete a data table
• Accidentally deleting a database using the drop database statement
• Using rm to accidentally delete the entire mysql instance
  

For accidentally deleted rows

• Use the flashback tool to flash back and restore the data. The principle is to modify the contents of binlog and retrieve them from the original database for replay. You need to ensure that binlog_format=row and binlog_row_imsge=Full
  
• Specific recovery time
  
  • If it is an insert, change the binlog event type from write_rows event to delete_rows event.
  • If it is delete, the opposite is true.
  • If it is an update, the binlog contains the values ​​before and after the data is modified. Just swap these two lines.
• Multiple things are also executed in reverse order according to the above principles.
• Prevention: Set the sql_safe_updates parameter to on. In this way, if we forget to write the where condition in the delete or update statement, or the where condition does not contain the index field, the execution of this statement will report an error.
  

For accidentally deleted databases/tables

You need to use a full backup plus incremental logs. Is it required to have regular full backup online and back up binlog in real time?

If someone accidentally deletes a database at 12 noon, the process of restoring the data is as follows:

Take the most recent full backup. Assume that the database is backed up once a day and the last backup was at 0:00 on the same day.

Restore a temporary library from the backup;

From the log backup, take out the logs after 0:00 am

Apply all these logs, except for statements that accidentally delete data, to the temporary database.

Notice:

To speed up data recovery, if there are multiple databases on this temporary library, you can add a –database parameter when using the mysqlbinlog command to specify the library where the accidentally deleted table is located. This avoids the need to apply other library logs when restoring data.

When applying logs, you need to skip the binlog of the statement with the erroneous operation at 12 o'clock:

Method to speed up recovery: After backing up and restoring a temporary instance, set this temporary instance as a slave database of the online backup database.

It is impossible for a system to back up unlimited logs. You also need to set the number of days to retain logs based on cost and disk space resources. If your DBA team tells you that they can guarantee that an instance can be restored to any time point within half a month, this means that the backup system retains logs for at least half a month.

Although "no one wants this to happen", if accidental deletion occurs, you can quickly restore the data and minimize the losses, so you don't have to run away. However, if you are in a hurry to perform manual operations and end up making mistakes, causing secondary damage to the business, that would be unacceptable.

Delayed replication of standby database

• If the backup of a database is particularly large, or the time between the erroneous operation and the last full backup is a long time, for example, in an instance with weekly backup, if an erroneous operation occurs on the 6th day after the backup, then 6 days of logs need to be restored, and the recovery time may be calculated on a daily basis.
• A delayed replication standby database is a special standby database. By using the CHANGE MASTER TO MASTER_DELAY = N command, you can specify that the standby database maintains a delay of N seconds with the master database.
• For example, if you set N to 3600, this means that if data is accidentally deleted on the primary database and the erroneous operation command is discovered within 1 hour, the command has not yet been executed on the delayed replication standby database. At this time, execute stopslave on the standby database, and then use the method introduced earlier to skip the erroneous operation command to restore the required data.

For rm to delete data

As long as the entire cluster is not deleted maliciously and only the data of one of the nodes is deleted, the HA system will start working and select a new master database to ensure the normal operation of the entire cluster. At this point, all you have to do is restore the data on this node and then connect it to the entire cluster.

Of course, now not only DBAs have automated systems, but SAs (system administrators) also have automated systems, so perhaps an operation of taking machines offline in batches will wipe out all the nodes in your entire MySQL cluster. To deal with this situation, my only advice is to try to save your backups across data centers, or preferably across cities. Kill sql statement

Does session B terminate the thread directly and exit without doing anything? Obviously, this won't work.

When adding, deleting, modifying, or querying a table, an MDL read lock is added to the table. Therefore, although session B is in the blocked state, it still holds an MDL read lock. If the thread is killed, it will terminate directly, and then the MDL read lock will not have a chance to be released.

Kill does not mean to stop immediately, but to tell the execution thread that this statement no longer needs to be executed and it can start "execution stop logic".

In fact, when executing kill query thread_id_b, the thread that processes the kill command in MySQL does the following:

• Change the running state of session B to THD::KILL_QUERY
• A signal is sent to the execution thread of session B.

Because in our example in Figure 1, session B is in the lock waiting state. If we just set the thread state of session B to
THD::KILL_QUERY, thread B is unaware of this state change and will continue to wait. The purpose of sending a signal is to make session B exit the waiting state to handle the THD::KILL_QUERY state.

The above contains three meanings:

• There are multiple embedding points during the execution of a statement. The thread status is determined at these "embedding points". If the thread status is found
• It is THD::KILL_QUERY that starts the statement termination logic;
• If it is in a waiting state, it must be a waiting state that can be awakened, otherwise it will not be executed to the "buried point" at all;
• There is a process from the beginning of a statement entering the termination logic to the completion of the termination logic.

An example of a kill

Execute set global innodb_thread_concurrency=2 to set the upper limit of InnoDB concurrent threads to 2; then, execute the following sequence:

You can see:

Session C was blocked during execution;

However, the kill query C command executed by session D has no effect.

It is not until session E executes the kill connection command that session C is disconnected, and the message "Lost connection to MySQL server during query" is displayed.

But at this time, if you execute show processlist in session E, you can see the following picture:

The Commnad column of the thread with id=12 shows Killed. In other words, although the client is disconnected, the statement is still being executed on the server.

In this example, the waiting logic of thread 12 is as follows: every 10 milliseconds, it checks whether it can enter the InnoDB execution
If it fails, call nanosleep function to enter sleep state.

That is to say, although the status of thread 12 has been set to KILL_QUERY, the status of the thread is not judged during the loop waiting to enter InnoDB, so the termination logic stage will not be entered at all.

When session E executes the kill connection command, it does this:

• Set the state of thread 12 to KILL_CONNECTION;
• Close the network connection of thread 12. Because of this operation, you will see that session C receives a disconnection prompt.

Why do we see the Command column displayed as killed when executing show processlist? In fact, this is because there is a special logic when executing show processlist:

If the state of a thread is KILL_CONNECTION, the Command column is displayed as Killed.

So in fact, even if the client exits, the status of this thread is still waiting. Only when the conditions for entering InnoDB are met and the query statement of session C continues to execute, can it be possible to determine that the thread state has become KILL_QUERY or KILL_CONNECTION and then enter the termination logic stage.

The first type of situation where kill is invalid is when the thread does not execute the logic for determining the thread status. It is also possible that due to excessive IO pressure, the read and write IO functions cannot return, resulting in the inability to determine the thread status in time.

• In the second case, the termination logic takes a long time
• A very large transaction was killed during execution, and the rollback operation took a long time.
• Large-scale rolling operations, such as large temporary files generated during the query process, require waiting for IO resources to delete the temporary files, resulting in a long time consumption.
• When the DDL is executed to the final stage and is killed, temporary files in the middle process need to be deleted, which also requires IO resources.

ctrl+C, mysql actually starts a connection process and sends the kill query command.

Misconceptions about slow client connections

If there are many tables in the database, the connection will be slow. For example, if there is a library with tens of thousands of tables, when connecting using default parameters, MySQL will provide a local library name and table name completion function:

• Execute show databases
• Switch to db1 and execute show tables
• The results of these two commands are used to build a local hash table.

The third step is a time-consuming operation, which means that the slowness we perceive is not due to a full connection or a slow server, but a slow client. If you add -A to this link, you can cancel the auto-completion function and return quickly.

The effect of automatic completion is that when entering a library name or table name, the prefix will be entered, and you can use tab to automatically complete or display a prompt. In fact, if you don’t use auto-completion very often, you can add -A every time you use it.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.