MySQL DATA mistaken deletion Attack and Defense

I believe that you may have encountered or heard that the data has been deleted by the application by mistake. Maybe the spelling of SQL causes the predicate to permanently delete or update all the data? Or a false surprise? Therefore, we need:

1. "attack": how to prevent this situation

2. "shou": What should I do if something happens?

For "attack ":

Because we are MySQL, we mainly use MySQL as an example:

1. SQL _safe_update: When set to 1

For update: If the predicate has no index and there is no limit, it will be rejected.

For delete: If the predicate is permanent or empty, or the predicate has no index and no limit is denied

In fact, these two points are very meaningful for oltp.

2. filter data at the driver/proxy Layer

The rule engine can be embedded into the driver (such as druid) or proxy (such as cobar), which is more flexible and configurable.

3. timed-out automatic kill. For OLTP, if it is to die, it will die early, and it is also called fail fast. If it is really a large statement, update/delete will produce terrible consequences for a long time, in this case, it would be better to kill it directly (of course, the above method is more elegant, and it will be rejected directly according to the Rules)

4. Process Automation/Standardization

SQL online process, automated and standardized test environment

For "guard ":

0. Regular backup and Validity tests are required. This is the bottom line.

1. Delayed replication can be created:

There are a lot of methods, pt-slave-delay/tunststen-replicator/blabla...

2. MySQL Flashback:

Http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog

Welcome to add J.

Delete all database files by mistake (including data files, control files, log files, etc.) and restore

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases