In practice, there will always be scenarios where data is accidentally deleted. In the absence of backup, it is very important to quickly restore accidentally deleted data.
This article is based on MySQL's binlog log mechanism. When the log format is set to "binlog_format=ROW", the whole process of manually parsing binlog and restoring accidentally deleted data step by step is recorded for your reference.

1. Determine the deletion time period

View the name of the database log, view the operation event, and confirm the specific time of deletion to locate the deleted log content.
show master logs;
show binlog events in 'binlog.000067';
show binlog events in 'binlog.000067' FROM 1293303 LIMIT 0, 20;
Through a statement similar to the above, the specific log name, specific operation time point or position can be determined

mysql> show binlog events in 'binlog.000002' FROM 2853160 LIMIT 0, 20;
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos     | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| binlog.000002 | 2853160 | Query          |         1 |     2853246 | BEGIN                                |
| binlog.000002 | 2853246 | Table_map      |         1 |     2853315 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2853315 | Update_rows    |         1 |     2859755 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2859755 | Query          |         1 |     2859842 | COMMIT                               |
| binlog.000002 | 2859842 | Anonymous_Gtid |         1 |     2859921 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2859921 | Query          |         1 |     2860007 | BEGIN                                |
| binlog.000002 | 2860007 | Table_map      |         1 |     2860076 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2860076 | Update_rows    |         1 |     2866516 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2866516 | Query          |         1 |     2866603 | COMMIT                               |
| binlog.000002 | 2866603 | Anonymous_Gtid |         1 |     2866682 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2866682 | Query          |         1 |     2866759 | BEGIN                                |
| binlog.000002 | 2866759 | Table_map      |         1 |     2866828 | table_id: 94 (liking.wp_options)     |
| binlog.000002 | 2866828 | Delete_rows    |         1 |     2866936 | table_id: 94 flags: STMT_END_F       |
| binlog.000002 | 2866936 | Query          |         1 |     2867014 | COMMIT                               |
| binlog.000002 | 2867014 | Anonymous_Gtid |         1 |     2867093 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2867093 | Query          |         1 |     2867179 | BEGIN                                |
| binlog.000002 | 2867179 | Table_map      |         1 |     2867247 | table_id: 95 (liking.wp_postmeta)    |
| binlog.000002 | 2867247 | Update_rows    |         1 |     2867347 | table_id: 95 flags: STMT_END_F       |
| binlog.000002 | 2867347 | Query          |         1 |     2867434 | COMMIT                               |
| binlog.000002 | 2867434 | Anonymous_Gtid |         1 |     2867513 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+

2. Get all sql statements in the specified time period

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2022-10-27 16:54:00" --stop-datetime="2022-10-27 18:27:00" binlog.000288 > liking.sql

3. Take the delete statement

sed -n '/### DELETE FROM data desensitization. data desensitization data desensitization/,/COMMIT/p' liking.sql > liking-1.sql
Get a result similar to the following:

head -50 liking-1.sql
### DELETE FROM `Data desensitization`.`Data desensitization data desensitization`
### WHERE
###   @1=222248 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Data desensitization data desensitization' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[3rd is omitted here~45 record lines]
###   @46='Data desensitization' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @47='Order' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @48='Data desensitization' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */

4. Only keep the row data starting with ###

sed -n '/^###/'p liking-1.sql > liking-2.sql

5. Remove the ### character at the beginning

sed 's/### //g' liking-2.sql > liking-3.sql
Get a result similar to the following:

head -50 liking-3.sql
DELETE FROM `Data desensitization`.`data desensitization data desensitization`
WHERE
  @1=222248 /* INT meta=0 nullable=0 is_null=0 */
  @2='data desensitization data desensitization' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[The third one is omitted here~45th field]
  @46='Data desensitization' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
  @47='Order' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
  @48='Data desensitization' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */

6. Use # as the delimiter to replace the content starting with /* with ,

sed 's#/*.*#,#g' liking-3.sql > liking-4.sql
Got the following result:

head -50 liking-4.sql
DELETE FROM `Data desensitization`.`data desensitization data desensitization`
WHERE
  @1=222248 ,
  @2='data desensitization data desensitization' ,
[The third one is omitted here~45th field]
  @46='Data desensitization' ,
  @47='Order' ,
  @48='Data desensitization' ,

Seven, browse the results to do the corresponding processing

Since there are still some insert and update statements, only the previous delete statement is taken out:
sed -n '1,493200p' liking-4.sql > liking-5.sql

8. Replace the keyword in the full text and change the delete to insert

Change 'DELETE FROM' to 'INSERT INTO'
Change 'WHERE' to 'SELECT'
sed 's#DELETE FROM#INSERT INTO#g' liking-5.sql > liking-6.sql
sed 's#WHERE#SELECT#g' liking-6.sql > liking-7.sql
Got the following result:

head -50 liking-7.sql
INSERT INTO `Data desensitization`.`data desensitization data desensitization data desensitization`
SELECT
  @1=222248 ,
  @2='data desensitization data desensitization' ,
[The third one is omitted here~45th field]
  @46='Data desensitization' ,
  @47='Order' ,
  @48='Data desensitization' ,

9. Match the lines starting with ',' and ending with '@48=', replace ',' with ';'

sed -r 's#(@48=.)(,)#\1;#g' liking-7.sql > liking-8.sql
-r: means to set the characters that need to be escaped in the regular expression to not need to be escaped
Parentheses are used to match consecutive occurrences
.identifier matches any unit character except the newline character \n
In order to remove ',', separate (,)
\1 is used to take the first of the () symbol, and spliced ​​with ';'
The records recovered this time are all 48 fields, and a semicolon is added at the end to mark the end of SQL, which is easier to understand.
Get a result similar to the following:

INSERT INTO `Data desensitization`.`data desensitization data desensitization data desensitization`
SELECT
  @1=222248 ,
  @2='data desensitization data desensitization' ,
[The third one is omitted here~45th field]
  @46='Data desensitization' ,
  @47='Order' ,
  @48='Data desensitization' ,

10. Replace '@??=' with '', that is, remove the part similar to '@11='

sed -r 's#(@.=)(.)#\2#g' liking-8.sql > liking-9.sql
Get a result similar to the following:

head -50 liking-9.sql
INSERT INTO `Data desensitization`.`data desensitization data desensitization`
SELECT
  222248 ,
  'CMHE-202200393' ,
  'Data DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData DesensitizationData Desensitization' ,
  4.1772e+07           ,
  '\'8000062202216124' ,
[6th is omitted here~45th field]
  'Data desensitization' ,
  'Order' ,
  'Ma Lei' ;

Eleven, the final splicing commit statement

sed -i '$a commit;' liking-9.sql
Check out the last few lines to confirm:

tail -51 liking-9.sql
INSERT INTO `Data desensitization`.`data desensitization data desensitization`
SELECT
  251837 ,
  'CMHE-202101003' ,
  'data desensitization data desensitization data desensitization data desensitization data desensitization' ,
  5.04e+08             ,
  '\'8000004202200006' ,
[6th is omitted here~45th field]
  'Data desensitization' ,
  'Order' ,
  'Chen Peng 3' ;
commit;

12. Browse and adjust individual exception format fields

At this time, you need to roughly browse the obtained text results, pay special attention to escape characters, and adjust them in time if necessary.
It is found that there are still field formats similar to the following
''8000004202200006'
To remove '
Then do the following:
sed "s#\'##g" liking-9.sql > liking-10.sql
The final confirmation is as follows:

tail -51 liking-10.sql
INSERT INTO `Data desensitization`.`data desensitization data desensitization`
SELECT
  251837 ,
  'CMHE-202101003' ,
  'data desensitization data desensitization data desensitization data desensitization data desensitization' ,
  5.04e+08             ,
  '8000004202200006' ,
[6th is omitted here~45th field]
  'Data desensitization' ,
  'Order' ,
  'Chen Peng 3' ;
commit;

13. Perform data recovery

After review and adjustment, the result obtained at this time can already perform the INSERT operation to restore the data normally!

Written at the end, I re-emphasize that backup is the first priority of operation and maintenance. The importance of backup cannot be overemphasized, unless you can bear the consequences of data loss.
There are tens of millions of rows of data, and backup is the most important; if data is not backed up, two rows of tears will be lost!

Reference documentation:
https://blog.csdn.net/qq_37701372/article/details/115188461