Must collect! MySQL data deletion Guide

Time:2021-4-21

Must collect! MySQL data deletion Guide
Author: selling tea at the end of the year / Laoye Teahouse
source:https://yq.aliyun.com/article…

First of all, the top types of data deleted by MySQL are as follows:

1. Delete the document by mistake
two . Delete database and table by mistake
3. Delete / update the whole error table
4. Upgrade operation error

Let’s see how many you hit, HOHO.

Let’s talk about a big accident I made myself.

It was probably a season of warm spring and blooming flowers. My heart was full of excitement, because I had already arranged my vacation plan. A few days ago, the database environment of a new project has been deployed, includingAutomated backup

When I went out to play, the tragedy happened and the business required to be carried outData rollback, but the backup file was found to be unavailable becauseThe specified character set and table character set are inconsistent during backup. As a result, the original project adopted a new character set, but I didn’t carefully check, confirm and modify the backup script, resulting in the failure of the backup. Finally, because of this, the results of the quarter were downgraded, and the boss also took the blame

Well, let’s get back to the point. Let’s first say a few immature suggestions for preventing file / data loss caused by misoperation:

1. If you want to delete a file, change the RM command to MV. At the system level, make the RM command as an alias (or refer to Windows / Mac OSX to delete the file in the recycle bin).

When deleting a database or table, do not use the drop command, but rename it to a special archive;

2. When deleting the data in the table, do not use the delete or truncate command directly, especially the truncate command. At present, transactions are not supported and cannot be rolled back.

3. When deleting data with the delete command, the transaction should be explicitly opened first, so that there is an opportunity to roll back in case of misoperation.

4. When you want to delete data in large quantities, you can insert… Select these data into a new table and delete them after confirmation. Or vice versa, write the data you want to keep to a new table, and then rename the table.

5. Before executing important orders, prepare relevant orders and make sure they are correct again and again. For new birds, it’s better to ask your boss to sit next to you in the town for several times, otherwise it’s very likely that everyone will be involved

The above principles are also my own. All in all,Always be in awe of the online production environment. Although most of the operations can now be completed by the platform, the platform is not omnipotent. There have been some failures of the platform itself, such as data loss, code rollback, deployment errors, etc. I won’t name them.

Do a good job of backup, whether it is physical or logical backup!

Do a good job of backup, whether it is physical or logical backup!

Do a good job of backup, whether it is physical or logical backup!

It’s not too much to say important things three times.

With regard to preventive measures, let’s talk about how to remedy it as quickly as possible in case of misoperation. We list several common situations:

one . Execute drop database / The drop table command will delete the library table by mistake. If the shared table space mode happens to be adopted, there is still a chance of recovery. If not, please restore directly from the backup file. Shenma, you don’t even have backup files? Please quit DBA. A person who doesn’t even bother to do backup doesn’t deserve to be a DBA.

2. Next, in the shared table space mode, kill (kill – 9) MySQL related processes (mysqld) immediately after deleting_ Then try to recover the data from the ibdatax file.

3. Delete the running MySQL table IBD or ibdatax file by mistake. Please apply for the maintenance of the instance immediately. Of course, it does not mean to close the instance, but to suspend the business, or remove the instance from the online environment, and no longer write new data. Then, using the characteristics of the Linux system’s proc file, copy the IBD file out of the memory, and then restore it, because at this time, the mysqld instance keeps the file open in the memory Don’t close the mysqld instance.

4. After copying the ibdatax or IBD file back to dataDir, restart mysqld and enter recovery mode, InnoDB_ force_ The recovery option is tested level by level from 0 to 6 until all the data (the whole instance or single table) can be backed up, and then the instance (or single table) can be rebuilt to recover the data.

5. When the transaction mode is not opened, delete the data by mistake. When you realize it, you immediately add mysqld (and mysqld)_ Kill the process (kill – 9), do not hesitate, and then use tools to read out the table space data. Because after deleting, the actual data is not physically cleared, but is labeled as deleted mark first and then cleaned up uniformly, so there is still a time lag.

5. Execute truncate to clear the table. If you don’t use the shared table space mode, don’t think about it. Let’s go with backup and recovery + binlog.

6. Execute the update without where condition, or update the wrong data. Don’t bother, go backup recovery + binlog.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]