Recovering MySQL 5.7 Data After an Erroneous UPDATE Using Binlog

MySQL does not provide a built‑in flashback feature like Oracle; recovery after a mistaken UPDATE must rely on the binary log, which requires binlog_format=row . The article walks through a complete example of extracting the needed information from the binlog and rebuilding the original data.

Table structure

CREATE TABLE `update_test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Test data is inserted, and then an erroneous update changes the user_id column to the value 'ture' for all rows.

update update_test set user_id='ture';

To locate the change in the binary log, the following command is used:

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000003 | grep -B 15 'ture' | more

The relevant portion of the binlog looks like:

# at 197210475
#200610 10:59:05 server id 1023306  end_log_pos 197210536 CRC32 0xee919b04      Rows_query
# update update_test set user_id='ture'
# at 197210536
#200610 10:59:05 server id 1023306  end_log_pos 197210598 CRC32 0xeb431251      Table_map: `tc01`.`update_test` mapped to number 120
# at 197210598
#200610 10:59:05 server id 1023306  end_log_pos 197211734 CRC32 0x5f211a8d      Update_rows: table id 120 flags: STMT_END_F
## UPDATE `tc01`.`update_test`
## WHERE
##   @1=1 /* INT meta=0 nullable=0 is_null=0 */
##   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
##   @3=4502 /* INT meta=0 nullable=0 is_null=0 */
##   @4=2 /* INT meta=0 nullable=0 is_null=0 */
##   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
##   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
## SET
##   @1=1
##   @2='ture'
##   @3=4502
##   @4=2
##   @5=1
##   @6='2020-06-04 11:34:17'
…

Using a series of sed commands the binlog fragment is transformed into a set of UPDATE statements that restore the original values. First the raw fragment is saved:

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000003 | sed -n '/# at 197210598/,/COMMIT/p' > ./update_test.txt

Then the following pipeline converts it to executable SQL (place‑holders @1 … @6 are used initially):

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update_test.txt \
| sed -r '/WHERE/{:a;N/@6/!ba;s/###   @2.*//g}' \
| sed 's/### //g;s/\/\*.*/,/g' \
| sed '/WHERE/{:a;N/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' \
| sed '/^$/d' > ./update_test_recover.sql

After the placeholders are replaced with the actual column names:

sed -i 's/@1/id/g;s/@2/user_id/g;s/@3/vote_num/g;s/@4/group_id/g;s/@5/status/g;s/@6/create_time/g' update_test_recover.sql

The resulting recovery script looks like:

UPDATE `tc01`.`update_test`
SET
  id=1 ,
  user_id='ddddddddddd' ,
  vote_num=4502 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=1 ;
… (repeated for each row)

Finally the script is executed:

source update_test_recover.sql

Querying the table shows that all rows have been restored to their original values, confirming that the data recovery was successful.

This step‑by‑step process demonstrates how to use MySQL binlog extraction and simple shell utilities to undo an accidental UPDATE without having a native flashback feature.