Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to delete the database by mistake". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to delete the database by mistake.

In general, this situation will not be encountered if you delete the database and delete the table in a large company. after all, the account you give us development will not open such high permissions. But in a small company, it's not necessarily, it's not so standard. What if we shake our hands and accidentally delete it wrong?

For example, if you delete wrong, delete other lines. Then if your database binlog configuration is

Binlog_format = row and binlog_row_image = FULL

For example, the binlog event type corresponding to your delete statement is

Delete_rows event

And then change it to

Write_rows event

Just take the modified binlog back to the original library and replay it.

If you update, the binlog also records the pre-and post-modified values of the data. You just need to switch the positions of these two rows of data.

The Flashback tool is recommended, and the principle of the tool is exactly what I said above.

In addition, it is not recommended to perform this operation on the master library. In general, back up a library, or perform these operations on which slave library, confirm that there is no problem, and then reply to the master library to ensure the security of the master library.

But some buddies may be fierce, he may be drop or truncate which table or drop database. In this case, binlog remembers that it will only be a drop/truncate statement, so it is impossible to use the above method.

What to do, find the full backup before, no word. You know

Find the previous full backup and cooperate with the incremental log restore. The process is:

Find the most recent full backup. For example, it is 2019-3-25 at 11:00 in the evening

Restore a temporary library from this backup

Find logs after 11:00 in the evening on March 25, 2019 in the log backup.

Apply all these logs to the temporary library except the statement that was mistakenly deleted.

Article 4 for this operation, if your database instance uses the GTID schema, first

Set gtid_next= (the GTID of your delete statement); begin;commit

If you do not use this mode, you can only execute before the statement with the-stop-position parameter before applying to the delete statement, and then-start-position starts the execution after the statement.

We do master-slave backup, and in order to prevent problems caused by mistakenly deleting data, we can set up a slave database with a delay of one hour, for example, if we back up once a week, then if we make something wrong on the seventh day and need to restore it, how long will it take to run? So get a special repository, through

CHANGE MASTER TO MASTER_DELAY = N

To set the delay between the slave database and the master database for N seconds, so as to find that the master database has been misoperated, immediately execute stop slave on the slave database, and then skip the command of erroneous deletion by the above method to restore the data!

Thank you for your reading, the above is the content of "how to delete the database by mistake". After the study of this article, I believe you have a deeper understanding of how to delete the database by mistake, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.