"MySQL" rolls back DML operations with Binlog

Introduction：
The operation of the database will inevitably occur during the misoperation, especially in the test environment developers or testers sometimes mistakenly delete or update some of the wrong data. The DML operation can then be flashed back with Binlog.

Conditions:

• 1.mysql Binlog must exist and is in row format
• 2. The reverse-generated table must have a primary key
• 3. Table structure cannot be changed

1.shell script Flash Back:

# Script del_time_recovery.sh (recovery based on start and end time) for rollback delete operation: #!/bin/bash# File Name???: del_time_recovery.sh# Author??????: Wang # Description:delete Recover according to StartTime and Endtime. Usage () {cat << eofmysql_delete_recoveryoptions:??? -B?????? Binlog name??? -S?????? StartTime??? -E?????? Endtime??? -D?????? Database name??? -T?????? Table Namefor secrity:this Scripts Check the full need argumentseof}while getopts ": b:s:e:d:t:" OPT; Do?? Case $opt in???? b)?????? Logname=${optarg}??????;;???? s)?????? Starttime=${optarg}??????;;???? e)?????? Endtime=${optarg}??????;;???? d)?????? Db=${optarg}??????;;???? T)?????? Table=${optarg}??????;;???? \?)?????? echo "Invalid option:-$OPTARG" >&2?????? Exit 1??????;;????:)?????? echo "option-$OPTARG requires an argument." >&2?????? Usage?????? Exit 1??????;;?? Esacdoneif [$#! = 10]; Then???? Usage???? Exit 1;fipath=$[path:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin:/usr/local/mysql/bin] ( Http://path/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin:/usr/local/mysql/bin) Export pathuser=rootpwd= ' xxxxxxxx ' tmpfile=/tmp/del_ Recovery_$table.sqlmysqlbinlog--no-defaults-vv--base64-output=decode-rows--start-datetime= "$starttime"-- Stop-datetime= "$endtime" $logname |sed-n '/### DELETE from ' ${db} '. ' ${table '/,/commit/p ' | Sed-n '/###/p '???? | Sed ' s/###//g;s/\/\*.*/,/g;s/delete from/insert into/g;s/where/select/g; '??? > $tmpfilen =0;for i in ' mysql-u$user-p$pwd?--skip-column-names--silent-e "desc $db. $table" |awk ' $0=$1 ';d o??????? ? ((n++));d onesed-i-R "s/(@ $n. *),/\1;/g" $tmpfilesed-i ' s/@[1-9].*=//g ' $tmpfilesed-i ' s/@[1-9][0-9]=//g ' $tmpfile # Usage: -b-s?-e-d-T takes the name of the time library name with the time end of Binlog first, # Direct use? sh del_time_recovery.sh-b/mysqllog/mysql-bin.000005-s "2017- 11-02 19:10:00 "-E" 2017-11-02 19:20:00 "-D test_db-t TEST_TB can be called

# Script update_time_recovery.sh (recovery based on start and end time) is used to roll back the update operation: #!/bin/bash# File name:update_time_recovery.sh# Author:wa ng# description:update Recover according to StartTime and Endtime.      Usage () {cat << eofmysql_update_recoveryoptions:-B binlog name-s starttime-e endtime-d Database name-t table namefor secrity:this Scripts Check the full need argumentseof}while getopts ": b:s:e:d:t: "OPT;    Do case $opt in B) logname=${optarg};;    s) starttime=${optarg};;    e) Endtime=${optarg};;    d) db=${optarg};;    t) table=${optarg};;      \?)    echo "Invalid option:-$OPTARG" >&2 exit 1;;  :) echo "option-$OPTARG requires an argument." >&2 Usage exit 1;; Esacdoneif [$#! = 10]; Then Usage exit 1;fiuser=rootpwd= ' xxxxxxx ' Tmpfile=/tmp/update_recovery_$table.sqln=0;for i in ' mysql-u$user-p$pwd --skip-column-names--SILENT-E "desc $dB. $table "|awk ' $0=$1";d O ((n++));d onemysqlbinlog--no-defaults-vv--base64-output=decode-rows--start-datetime= "       $starttime "--stop-datetime=" $endtime "$logname |sed-n '/### UPDATE ' ${db} '. ' ${table ' | Sed '/where/{:a;       N;/set/!ba;s/\ ([^\n]*\) \n\ (. *\) \n\ (. *\)/\3\n\2\n\1/} ' | Sed-r '/where/{:a;       n;/@ ' "$n" '/!ba;s/### @2.*//g} ' |       Sed ' s/###//g;s/\/\*.*/,/g ' | Sed '/where/{:a;       N;/@1/!ba;s/,/;/g};s/#.*//g;s/commit,//g ' | Sed '/^$/d ' > $tmpfilen =0;for i in ' mysql-u$user-p$pwd--skip-column-names--silent-e "desc $db. $table" |awk "          =$1 ';d O ((n++)); Sed-i "s/@ $n \b/$i/g" $tmpfiledonesed-i-R "s/($i =.*),/\1/g" $tmpfile # Usage:-b-s-E -d-t the time library name with the time at the end of the Binlog name, # directly using sh update_time_recovery.sh-b/mysqllog/mysql-bin.000005-s "2017-11-03 14:30:00 "-E" 2017-11-03 15:00:00 "-D test_db-t TEST_TB can be called

2. Using the mysqlbinlog_back.py script:

Reference:
The "MySQL" Mysqlbinlog_flashback tool uses

3. Using the Myflash tool:

Reference:
"MySQL" myflash rollback MySQL Binlog

There are many similar open-source projects on the Internet such as: Binlog2sql, etc. can be consulted.

"MySQL" rolls back DML operations with Binlog