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

This article mainly introduces "how to delete data by MySQL". In daily operation, I believe that many people have doubts about how to delete data by MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to delete data by MySQL". Next, please follow the editor to study!

First of all, let's take a look at the top categories of mysql mistakenly deleted data:

1. Mistakenly delete files

two。 Erroneous deletion of libraries and tables

3. Error full table delete / update

4. Upgrade operation error

Let's see how many you've hit, hoho.

Let's briefly talk about a big accident I made myself.

It was probably a spring season, and my heart was full of excitement because I had arranged a holiday plan. A few days ago, the database environment for a new project was deployed, including automated backups.

When I went out to play, a tragedy happened. the business required data rollback, but found that the backup file was not available because the character set specified at the time of backup was inconsistent with the table character set. I am sorry, the original project uses a new character set, but I did not carefully check to confirm and modify the backup script, resulting in backup invalidation. Finally, because of this, when the quarterly performance results were downgraded, boss also took the blame for it.

All right, back to the point, let's start with a few of my immature suggestions to prevent file / data loss caused by misoperation:

1. When you want to delete a file, change the rm command to mv, you can do an alias of the rm command at the system level (or refer to Windows / Mac OSX practice, advanced Recycle Bin when deleting files).

When deleting databases and tables, do not use the drop command, but rename to a dedicated archive library

two。 When deleting data from a table, do not directly use the delete or truncate command, especially the truncate command. Currently, transactions are not supported and cannot be rolled back.

3. When you delete data with the delete command, you should explicitly open the transaction first, so that in case of misoperation, there is still a chance to roll back.

4. When you want to delete data in bulk, you can insert...select the data to a new table, confirm it, and then delete it. Or do the opposite, write the data you want to retain to the new table, and then rename the table.

5. Before carrying out important orders, prepare the relevant orders and confirm again and again before the trip. For new birds, it is best to ask your boss to sit next to you several times, otherwise it is very likely to affect everyone.

The above are also my own principles. In short, we should always be in awe of the online production environment. Although most of the operations can be done by the platform now, the platform is not omnipotent. There have been incidents such as data loss, code rollback and deployment errors caused by defects in the platform itself, so I will not call the roll.

Make a good backup, whether it is physical backup or logical backup!

Make a good backup, whether it is physical backup or logical backup!

Make a good backup, whether it is physical backup or logical backup!

You can't say too many important things three times.

After talking about the preventive measures, we will talk about how to remedy them as quickly as possible in case of misoperation. Let's list several common situations:

1. Execute the DROP DATABASE / DROP TABLE command to erroneously delete the database table, and there is a chance of recovery if you happen to use the shared tablespace mode. If not, please restore it directly from the backup file. What, you don't even have a backup file? Then please quit the DBA session, a person who is too lazy to do backup does not deserve to be a DBA.

two。 Then, in shared tablespace mode, kill (kill-9) mysql-related processes (mysqld_safe, mysqld) immediately after erroneous deletion, and then try to recover data from the ibdataX file.

3. Mistakenly delete the running MySQL table ibd or ibdataX file. Please apply for 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, and then take advantage of the proc file characteristics of the linux system to copy the ibd file from memory and restore it. Because the mysqld instance keeps the file open in memory at this time, remember not to close the mysqld instance at this time.

4. Then, after copying the copied ibdataX or ibd file back to datadir, restart mysqld to enter recovery mode. The innodb_force_recovery option is tested step by step 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) is rebuilt and the data is restored.

5. Execute delete to erroneously delete data when transaction mode is not enabled. As soon as you realize it, kill the mysqld (and mysqld_safe) process (kill-9) without hesitation, and then use the tool to read out the tablespace data. Because the actual data is not physically cleared after delete deletion, it is tagged with deleted-mark first, and then cleaned up later, so there is still a time lag.

5. Perform truncate miscleaning of the table. If you don't use shared tablespace mode, forget about it and go to backup recovery + binlog.

6. Execute update without where conditions, or update bad data. Don't bother, go to backup recovery + binlog.

At this point, the study on "how to delete data by MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.