Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of mistakenly deleting data in MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "how to solve the problem of mistakenly deleting data from MySQL".

Step 1: make sure that mysql has enabled binlog, and check the command:

Check to see if binklog is on

Show variables like'% log_bin%'

Check the directory where log files are stored in binlog (as shown below, the blogger binlog directory is / data/mysql):

Show variables like'% datadir%'

The value is OFF, which needs to be enabled, and the value is ON, which is enabled.

If binlog is not enabled and the rollback SQL is not enabled, it may not be possible to roll back quickly. It is strongly recommended to enable binlog for MySQL that stores important business data.

Step 2: go to the binlog file directory and find the log files

Step 3: change to the mysqlbinlog directory (when there is an error in online data, you can first ask the specific operator to record the time point. At this time, you can use the binlog parsing tool mysqlbinlog included with mysql, which is located in the mysql installation directory * * / mysql/bin/).

Step 4: view the database addition, deletion, modification and query records through the mysqlbinlog tool command (you must change to the mysqlbinlog directory to be valid)

Example 1: query the operation log of 2018-11-12 09:00:00 to 2018-11-13 20:00:00 database as youxi, enter the following command to write the data to an alternate txt file

Mysqlbinlog-no-defaults-database=youxi-start-datetime= "2018-11-12 09:00:00"-- stop-datetime= "2018-11-13 20:00:00" / data/mysql/mysql-bin.000015 > template_coupon_tb_product_category.txt

Example 2: query the operation log of 2018-11-12 09:00:00 to 2018-11-13 20:00:00 database as youxi and output to the screen

Mysqlbinlog-no-defaults-database=youxi-start-datetime= "2018-11-12 09:00:00"-- stop-datetime= "2018-11-13 20:00:00" / data/mysql/mysql-bin.000015 | more

Example 3: query the operation logs for which the database is youxi from 09:00:00 to 2018-11-13 20:00:00, and filter out the operation records that include only template_coupon_tb_product_category table data. Enter the following command to write the data to an alternate txt file

Mysqlbinlog-no-defaults-database=youxi-start-datetime= "2018-11-12 09:00:00"-- stop-datetime= "2018-11-13 20:00:00" / data/mysql/mysql-bin.000015 | grep template_coupon_tb_product_category > template_coupon_tb_product_category.txt

Syntax format of mysqlbinlog command: mysqlbinlog mysql-bin.0000xx | mysql- u username-p password database name-explanation of common parameter options:-- start-position=875 start pos point-- stop-position=954 end pos point -- start-datetime= "2016-9-25 22:01:08" start time-- stop-datetime= "2019-9-25 22:09:46" end time-- database=zyyshop specifies that only zyyshop databases are restored (there are often multiple databases on a single host Local log logs only)-not commonly used option:-u-user=name user name connected to the remote host-p-password [= name] password connected to the remote host-h-host=name from the remote Get binlog logs on the host-- read-from-remote-server reads binlog logs from a MySQL server

Step 5: filter the statement using the sql statement or txt text output in step 4, reinsert or update the data

At this point, I believe you have a deeper understanding of "how to solve the problem of mistakenly deleting data from MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.