MySQL restores or rolls back data through binlog

1、 Preface

Recently, there was a mistake in the project development , So I searched the Internet mysql Rollback operation for , Here is a record of .

Under the simple said binlog,binlog yes mysql Binary log in , It records various changes in the database change . So pass binlog You can roll back or recover the wrong operation .

Restore general use mysqlbinlog command , The order is mysql Self contained , Easy to use . The essence of its operation is to execute the events in the log again . Third party tools are generally used to roll back binlog2sql, The essence of rollback is to parse the log file and generate the data to be rolled back sql, We copy and execute the sql that will do .

2、 Create a test database , Prepare an empty table t1, Contains two fields id、name, as follows ：

 3、 Check whether it is enabled by the following command binlog Recording function （ If not, search the Internet to open the tutorial , There is not much here ）：

show variables like 'log_bin';

4、 To facilitate observation and testing , Use the following series of commands to generate a new binlog Log files , So that our subsequent operations are recorded in the new binlog in .

flush logs // Create a new log file 

show variables like 'log_bin_basename' // View log storage address 

show master status; // View the latest log file name 

5、 Insert three pieces of data , as follows ：

 6、 Look at the binlog logging

show binlog events in 'binlog.000068';

  Here we can see our three insertion events

7、 Delete a piece of data , The table data and binlog as follows ：

 

  You can see one more delete event , Pay attention here binlog Every event in the has a begin and commit, It starts and ends when we restore or rollback later pos Are the starting and ending points of the whole event . For example, the starting point of the above deletion event is actually 1105, The end point is 1316.

8、mysqlbinlog Restore data

Note that here is the recovery data , Not rollback data , The essence of recovery is to execute the original insert statement again , Rollback is to go back to the state before deletion .
mysqlbinlog yes mysql The order that comes with you , Usually in mysql Install under directory bin Directory . Because we are restoring data , So find the corresponding write event of the deleted statement , Execute the event again .
Through the first show binlog events in 'binlog.000068' Confirm the event location of the next insert statement

You can see The starting position is 815, End position is 1026, Then use mysqlbinlog Order recovery ：

mysqlbinlog --no-defaults   ..\data\binlog.000068 --start-position=815 --stop-position=1026  | mysql -uroot -p123456 test

  Let's take a look at the table and data binlog Information ：

 

You can see that the data in the table has been recovered ,binlog There is also one more write event in .

Summary ：
mysqlbinlog The command is only used to restore , Cannot be used for rollback . If the data is update operation , It is difficult to recover with this command . So this command is more Applicable to some data migration , Data synchronization scenario .

mysqlbinlog If... Occurs during operation unknown variable 'default-character-set=utf8mb4' abnormal , You can add... After this command --no-defaults Parameter Solving ：mysqlbinlog --no-defaults

mysqlbinlog The detailed usage of the command is not introduced here , If necessary, you can search on the official website or Baidu .

9、binlog2sql Data rollback

binlog2sql Is a third-party tool , It was debugged for middle note. , In order not to clutter the logic of the article , Here I opened a new article to explain （binlog2sql Tool installation, use and problem summary _Interest1_wyt The blog of -CSDN Blog ）, If you don't know the tool, you can refer to it .

binlog2sql The principle of rollback is Generate the event corresponding to the event to be rolled back sql sentence , Finally, we only need to copy the statement . Here for easy observation , Let's empty t1 surface , Then reopen one binlog Record , Open and view new binlog The command was described earlier , Here are only screenshots to show ：

  towards t1 Insert three pieces of data into the table again ：

  Modify the last data name Field , At this point, the table and binlog The records of are as follows ：

  adopt binlog2sql Generate rollback sql：

E:\Program Files\PYTHON3.9.5\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")
  result = self._query(query)
UPDATE `test`.`t1` SET `id`=3, `name`=' Roll back 3' WHERE `id`=3 AND `name`=' to update ' LIMIT 1; #start 1105 end 1312 time 2022-04-16 11:20:00

Copy the rollback statement and observe it after execution t1 Table and binlog journal ：

  You can see more than one event in the update log , The data in the table is also restored .