[MySQL] use binlog to roll back DML operation

Introduction:

Misoperation is inevitable during database operation, especially in test environment, developers or testers sometimes delete or update some wrong data. At this point, you can flashback DML operations with binlog.

Conditions:

1. mysql binlog must exist and be in row format 2. tables generated in reverse must have primary keys 3. table structure cannot be changed 1. shell script flashback: #script del_time_recovery.sh (recovery according to start and end time) used to rollback delete operation: #!/ bin/bash# File Name : del_time_recovery.sh# Author : wang# Description : delete recover according to starttime and endtime.Usage() {cat &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'`;do ((n++));donesed -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 binlog name start time end time library name table name,#directly use sh del_time_recovery.sh -b /mysqllog/mysql-bin. 00005-s "2017 - 11 - 02 19:10:00" -d test_db -t test_tb can call #script update_time_recovery.sh (according to the time recovery) for rollback update operation: #!/ bin/bash# File Name : update_time_recovery.sh# Author : wang# Description : update recover according to starttime and endtime.Usage() {cat &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'`;do ((n++));donemysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS --start-datetime="$starttime" --stop-datetime="$endtime" $logname |sed -n '/### UPDATE `'${db}'`.` '${table}'`/,/COMMIT/p' \| 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 '$0=$1'`;do ((n++)); sed -i "s/@$n\b/$i/g" $tmpfiledonesed -i -r "s/($i=.*),/\ 1/g" $tmpfile#Usage: -b -s -e -d -t with binlog name start time end time library name table name,#directly use sh update_time_recovery.sh -b /mysqllog/mysql-bin. 00005-s "2017-11-03 14:30:00" -e "2017 - 11 - 03 15:00:00" -d test_db -t test_tb 2. Use mysqlbinlog_back.py script:

Reference:

mysqlbinlog_flashback tool

3. Using MyFlash tools:

Reference:

MyFlash rollback mysql binlog

There are many similar open source projects on the Internet, such as binlog2sql, etc. can be referred to.