MySQL mistakenly delete data life-saving guide

Recommendations for prevention of file/data loss due to mis-operation:

1. To delete the file, change the RM command to MV, you can alias the RM command at the system level (or refer to the Windows/mac OSX procedure, the advanced Recycle Bin when deleting files).
2. When deleting the database, the table, do not use the drop command, but rename to a dedicated archive library;
3. When deleting data from a table, do not use the DELETE or truncate command directly, especially the TRUNCATE command, currently does not support transactions and cannot be rolled back.
4. When deleting data with the Delete command, you should explicitly open the transaction so that there is an opportunity to roll back when the error occurs.
5. To delete data in large batches, you can insert...select the data to a new table, and then delete it when you are sure it is correct. Or daoxing, write the data you want to keep to a new table, and then rename the table.
6. Before executing the important order, prepare the relevant order and confirm the error.

Several common remedial situations:

1. Execute the DROP database/drop table command to delete the library table by mistake, and if you happen to use shared tablespace mode, there is a chance of recovery. If not, please restore it directly from the backup file. God horse, you don't even have backup files? That trouble out of the DBA, a person who doesn't even bother to make a backup, doesn't deserve to be a DBA.
2. Then, using the shared tablespace mode, immediately kill (kill-9) the MySQL-related process (Mysqld_safe, mysqld) and then try to recover the data from the Ibdatax file.
3. Mistakenly delete an IBD or Ibdatax file in a running MySQL table. Please immediately apply for maintenance of this instance, of course, does not mean to shut down the instance, but to suspend the business, or to remove the instance from the online environment, no longer write new data, and then take advantage of the Linux system proc file features, the IBD file from memory to copy out, and then restore, Because the MYSQLD instance is kept open in memory at this point, remember not to shut down the mysqld instance at this time.
4. After copying the copied ibdatax or IBD files back to DataDir, reboot mysqld into recovery mode, innodb_force_recovery option to step through 0-6 until all data (entire instance or single table) can be backed up , and then rebuild the instance (or a single table) to restore the data.
5. When transaction mode is not turned on, execute delete to delete the data incorrectly. Realize immediately kill the mysqld (and Mysqld_safe) process (kill-9), without any hesitation, and then use the tool to read the tablespace data. Because after the delete deletion, the actual data is not physically cleared, but the first Deleted-mark label, followed by a unified cleanup, so there is a time difference.
6. Execute TRUNCATE error scrubbed table. If you do not use the shared tablespace mode, basically don't think about it, go back to restore +binlog.
7. Perform an update without a where condition, or update the wrong data. Don't bother, go back and restore +binlog.

MySQL mistakenly delete data life-saving guide