MySQL回滚工具

brownfish 发布于4月前 阅读996次
0 条评论
1、 mysqlbinlog把事务从binlog中导出

2、从导出的binlog中找到要回滚的事务,去掉第一个DML语句前和最后一个DML语句后与DML无关的binlog信息

3、在目录中新建一个table.cnf,把表结构以@1=columns这样的顺序一行写一列

4、update回滚支持选择条件列和回滚的数据列,把回滚时不需要的条件(列)写到not_used.set和not_used.where中

例如:

文件 table.cnf

@1=id
@2=column_a
@3=column_b
@4=time

文件not_used.set
##写到这个文件里面的是回滚时不需要更新的列
##例如假设回滚不恢复 id 列,文件中应该如下

@1=

文件not_used.where
##写到这个文件里面的是回滚时作为条件的列
##例如假设回滚时不需要列 time 和 column_b 作为回滚条件,文件中应该如下,顺序不敏感

@=3
@=4
#!/bin/bash
table_name='test'

### DELETE DML 2 rows in binlog
delete=2

### UPDATE DML 3 rows in binlog
update=3

### How many columns for this rollback table
table_columns=`wc -l ./table.cnf | awk '{print $1}'`

### Format binlog
#/usr/bin/awk '{$1="";print > "/export/scripts/rollback_autoSQL/bin.log";close("/export/scripts/rollback_autoSQL/bin.log")}' /export/scripts/rollback_autoSQL/mysql-bin.txt 
cat /export/scripts/rollback_autoSQL/mysql-bin.txt | awk '{$1="";print>"/export/scripts/rollback_autoSQL/bin.log"}' 
#echo |  awk '{$1="";print}' ./mysql-bin.txt  > ./bin.log

### Count for DML
dml_delete_count=`cat ./bin.log | grep DELETE | wc -l `
dml_update_count=`cat ./bin.log | grep UPDATE | wc -l `
echo -e "\033[47;30m dml_delete_count $dml_delete_count \033[0m"
echo -e "\033[47;30m dml_update_count $dml_update_count \033[0m"

### How many rows for one DML
dml_delete_row=`echo |awk '{print "'$delete'"+"'$table_columns'"}'`
dml_update_row=`echo |awk '{print "'$update'"+"'$table_columns'"+"'$table_columns'"}'`
dml_update_where_row_begin=3
dml_update_where_row_finish=`echo |awk '{print 2+"'$table_columns'"}'`
dml_update_set_row_begin=`echo |awk '{print 4+"'$table_columns'"}'`
dml_update_set_row_finish=$dml_update_row
echo -e "\033[47;30m dml_delete_row $dml_delete_row \033[0m"
echo -e "\033[47;30m dml_update_row $dml_update_row \033[0m"

fun_delete()
{
b=''
for((i=1;i<=${dml_delete_count};i++))
do
  sed -n '1,'$dml_delete_row'p' ./bin.log  > ./bin.tmp
    sed -i '1,'$delete'd' ./bin.tmp 
  awk -F '=' '{$1="";print}' ./bin.tmp | awk '{print $1}' | tr "\n" ","  > ./sql.tmp
    data=`sed 's/,$//' ./sql.tmp`
    echo "insert into $table_name values ($data);" >> ./rollback.sql 
    sed -i '1,'$dml_delete_row'd' ./bin.log
    rm -rf ./bin.tmp ./sql.tmp
  h=`echo | awk '{print int("'$i'"/"'$dml_delete_count'"*"100%")}'`
  printf "progress:[$h%%]\r"
#	printf "progress:[%-100s]%d%%\r" $b $h
#	b=#$b
done
rm -rf ./bin.log
echo -e "\n"
echo done
}


fun_update()
{
for((i=1;i<=${dml_update_count};i++))
do
  sed -n '1,'$dml_update_row'p' ./bin.log  > ./bin.tmp
    sed -n ''$dml_update_set_row_begin','$dml_update_set_row_finish'p' ./bin.tmp > ./bin.where
    sed -n ''$dml_update_where_row_begin','$dml_update_where_row_finish'p' ./bin.tmp > ./bin.set
### data have been set,and this data make to search for new data in rollback SQL,choose columns
  cat ./not_used.where | while read columns_where
  do
    sed -i '/'$columns_where'/d' ./bin.where
  done
  dml_where=`awk '{print $1}' ./bin.where | tr "\n" "," | sed 's/,$//'`

### data will be update,all columns or part of them
  cat "./not_used.set" | while read columns_set
  do
    sed -i '/'$columns_set'/d' ./bin.set
  done
  dml_set=`awk '{print $1}' ./bin.set | tr "\n" "," | sed 's/,$//'`

  echo "update $table_name set $dml_set where $dml_where;" >> ./rollback.sql
  sed -i '1,'$dml_update_row'd' ./bin.log
  
    h=`echo | awk '{print int("'$i'"/"'$dml_update_count'"*"100%")}'`
    printf "progress:[$h%%]\r"

done
rm -rf ./bin.*
echo -e "\n"
echo -e "\033[47;30m change column's names \033[0m"
cat ./table.cnf | while read t_tmp
do
  t_1="`echo $t_tmp | awk -F '=' '{print $1}'`="
  t_2="`echo $t_tmp | awk -F '=' '{print $2}'`="
  sed -i 's/'$t_1'/'$t_2'/g' ./rollback.sql
done
echo done
}


case $1 in
  delete)echo -e "\033[47;32m begin fun_delete \033[0m";sleep 2;fun_delete
  ;;
  update)echo -e "\033[47;32m begin fun_update \033[0m";sleep 2;fun_update
  ;;
  *)echo -e "\033[47;31m err input,please choose delete or update,quit \033[0m";exit 1
esac

共收到0条回复

需要 登录 后回复方可回复, 如果你还没有账号你可以 注册 一个帐号。