I believe that students of back-end research and development often encounter the need to temporarily modify online data during the development process of products. If they are very stable, they will be glad to complete the task quickly. Unfortunately, one day, they suddenly modify or delete the data in the table by mistake.

If you have BDA or colleagues with experience in this field, you can solve this problem quickly. If not, I hope this article can help you.

Mysql > enable binlog

Check whether binklog is enabled

show variables like '%log_bin%';
Copy the code

/data/mysql = /data/mysql

show variables like '%datadir%';
Copy the code

The value is “OFF” and needs to be enabled. The value is “ON” and needs to be enabled.

If binlog is not enabled and the rollback SQL is not pre-generated, you may not be able to roll back quickly. You are strongly advised to enable binlog for MySQL that stores important service data.

Step 2: Go to the binlog directory and find the log files

**/ mysqlbinlog/mysqlbinlog/mysqlbinlog/mysqlbinlog/mysqlbinlog/mysqlbinlog/mysqlbinlog

Step 4: Use the mysqlbinlog tool command to check the database add, delete, change, and check records (you must switch to the mysqlbinlog directory to be valid)

Example 1: Query the operation logs of the 2018-11-12 09:00:00 to 2018-11-13 20:00:00 database as Youxi, and run the following command to write the data to a spare 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
Copy the code

Example 2: Query operation logs generated in the 2018-11-12 09:00:00 to 2018-11-13 20:00:00 database as Youxi and output them 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
Copy the code

Example 3: Query operation logs from 2018-11-12 09:00:00 to 2018-11-13 20:00:00 as youxi and filter out operation logs from template_coupon_tb_product_category Type 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
Copy the code

Mysqlbinlog syntax Mysqlbinlog mysql - bin. 0000 xx | mysql -u username -p password database name -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 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 Only the local log log) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - not commonly used options: -u --user=name User name for connecting to the remote host -p --password[=name] Password for connecting to the remote host -h --host=name Obtain binlog logs from the remote host --read-from-remote-server Read binlog logs from a MySQL serverCopy the code

The fifth step: use the SQL statement or TXT text output in the fourth step for statement filtering, re-insert data or update data