美文网首页运维相关
【MySQL】利用binlog回滚DML操作

【MySQL】利用binlog回滚DML操作

作者: MySQL技术 | 来源:发表于2018-06-30 16:58 被阅读46次

简介
数据库运行过程中难免会发生误操作,特别是在测试环境 开发人员或测试人员有时会误删或者更新错误某些数据。这时可以用binlog闪回DML操作。

条件:

  • 1.mysql binlog必须存在且是row格式的
  • 2.反向生成的表必须有主键
  • 3.表结构不能有更改
1.shell脚本闪回:
# 脚本 del_time_recovery.sh(根据起止 time恢复)用于回滚delete操作:

#!/bin/bash
# File Name   : del_time_recovery.sh
# Author      : wang
# Description : delete recover according to starttime and endtime.
Usage() {
cat << EOF
mysql_delete_recovery
OPTIONS:
   -b      binlog name
   -s      starttime
   -e      endtime
   -d      database name
   -t      table name
For secrity: This scripts check the full need arguments
EOF
}
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
      ;;
  esac
done
if [ $# != 10 ] ; then
    Usage
    exit 1;
fi
PATH=$[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 PATH

user=root
pwd='xxxxxxxx'
tmpfile=/tmp/del_recovery_$table.sql
mysqlbinlog --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;'   > $tmpfile
n=0;
for i in `mysql -u$user -p$pwd --skip-column-names --silent -e "desc $db.$table" |awk '$0=$1'`;
do
        ((n++));
done
sed -i -r "s/(@$n.*),/\1;/g" $tmpfile
sed -i 's/@[1-9].*=//g' $tmpfile
sed -i 's/@[1-9][0-9]=//g' $tmpfile

# 用法:-b -s  -e -d -t 分别带别binlog名字 开始的time 结束的time 库名 表名,
# 直接使用  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 即可调用
# 脚本 update_time_recovery.sh(根据起止 time恢复)用于回滚update操作:

#!/bin/bash
# File Name   : update_time_recovery.sh
# Author      : wang
# Description : update recover according to starttime and endtime.
Usage() {
cat << EOF
mysql_update_recovery
OPTIONS:
   -b      binlog name
   -s      starttime
   -e      endtime
   -d      database name
   -t      table name
For secrity: This scripts check the full need arguments
EOF
}
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
      ;;
  esac
done
if [ $# != 10 ] ; then
    Usage
    exit 1;
fi
user=root
pwd='xxxxxxx'
tmpfile=/tmp/update_recovery_$table.sql
n=0;
for i in `mysql -u$user -p$pwd --skip-column-names --silent -e "desc $db.$table" |awk '$0=$1'`;
do
        ((n++));
done
mysqlbinlog --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'     > $tmpfile
n=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" $tmpfile
done
sed -i -r "s/($i=.*),/\1/g" $tmpfile

# 用法:-b -s  -e -d -t 分别带别binlog名字 开始的time 结束的time 库名 表名,
# 直接使用  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 即可调用
2.利用mysqlbinlog_back.py 脚本:

参考:
【MySQL】mysqlbinlog_flashback工具使用

3.利用MyFlash工具:

参考:
【MySQL】MyFlash 回滚mysql binlog

网上还有很多类似的开源项目 如:binlog2sql等 都可以参考下。

相关文章

  • 【MySQL】利用binlog回滚DML操作

    简介:数据库运行过程中难免会发生误操作,特别是在测试环境 开发人员或测试人员有时会误删或者更新错误某些数据。这时可...

  • mysql binlog的应用

    binlog 就是binary log,二进制日志文件,这个文件记录了mysql所有的dml操作。通过binlog...

  • mysqlbinlog怎么恢复数据

    Mysql之binlog日志说明及利用binlog日志恢复数据操作记录 众所周知,binlog日志对于mysql数...

  • mysql 事务回滚,online ddl

    回滚当事务中有DDL时,事务回滚会出错,当事务中只有DML时,回滚一般不会出问题DDL:新建表等操作DML:数据插...

  • 数据库事务

    提交操作(事务成功) start transaction DML语句 commit 回滚操作(事务失败) star...

  • 数据库的binlog

    什么是binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML...

  • MySQL的redo log、undo log、binlog

    一、MySQL日志文件类型 重做日志(redo log) 回滚日志(undo log) 二进制日志(binlog)...

  • MySQL数据恢复

    利用binlog闪回误操作数据 用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态。然后跳过误...

  • 数据库(增删改查)

    MySQL 数据操作 DML DML(Data Manipulation Language 数据操纵语言) DML...

  • MySQL 基础之 数据 操作(增删改查)

    MySQL 数据操作 DML DML(Data Manipulation Language 数据操纵语言) DML...

网友评论

    本文标题:【MySQL】利用binlog回滚DML操作

    本文链接:https://www.haomeiwen.com/subject/ykatuftx.html