mysql 回滚 工具_【MySQL】MySQL回滚工具

#!/bin/bash

table_name="test_update"### DELETE DML2 rows inbinlog

delete=2### UPDATE DML3 rows inbinlog

update=3### How many columnsforthis 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.txtcat ./mysql-bin.txt | awk ‘{$1="";print>"./bin.log"}‘#echo | awk ‘{$1="";print}‘ ./mysql-bin.txt > ./bin.log

### CountforDML

dml_delete_count=`cat ./bin.log | grep DELETE | wc -l `

dml_update_count=`cat ./bin.log | grep UPDATE | wc -l `echo -e "33[47;30m dml_delete_count $dml_delete_count 33[0m"

echo -e "33[47;30m dml_update_count $dml_update_count 33[0m"### How many rowsforone 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=3dml_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_rowecho -e "33[47;30m dml_delete_row $dml_delete_row 33[0m"

echo -e "33[47;30m dml_update_row $dml_update_row 33[0m"fun_delete()

{

b=‘‘

for((i=1;i<=${dml_delete_count};i++))do

sed -n ‘1,‘$dml_delete_row‘p‘ ./bin.log > ./bin.tmpsed -i ‘1,‘$delete‘d‘ ./bin.tmpcat ./not_used.values | whileread columns_valuesdo

sed -i ‘/‘$columns_values‘/d‘ ./bin.tmpdonedata=`awk -F ‘=‘ ‘{$1="";print}‘ ./bin.tmp | awk ‘{print $1}‘ | tr "n" "," | sed ‘s/,$//‘`

# data=`sed ‘s/,$//‘ ./sql.tmp`cp ./table.cnf ./dml_columns.tmpcat ./not_used.values | whileread columns_valuesdo

sed -i ‘/‘$columns_values‘/d‘ ./dml_columns.tmpdonedml_columns=`awk -F ‘=‘ ‘{print $2}‘ ./dml_columns.tmp | tr "n" "," | sed ‘s/,$//‘`echo "insert into $table_name($dml_columns) values ($data);" >> ./rollback.sqlsed -i ‘1,‘$dml_delete_row‘d‘ ./bin.logrm -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=#$bdone

rm -rf ./bin.logecho -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.tmpsed -n ‘‘$dml_update_set_row_begin‘,‘$dml_update_set_row_finish‘p‘ ./bin.tmp > ./bin.wheresed -n ‘‘$dml_update_where_row_begin‘,‘$dml_update_where_row_finish‘p‘ ./bin.tmp > ./bin.set

### data have been set,and this datamake to search for new data inrollback SQL,choose columnscat ./not_used.where | whileread columns_wheredo

sed -i ‘/‘$columns_where‘/d‘ ./bin.wheredonedml_where=`awk ‘{print $1}‘ ./bin.where | tr "n" "," | sed ‘s/,$//‘`

### data will be update,all columns or part of themcat "./not_used.set" | whileread columns_setdo

sed -i ‘/‘$columns_set‘/d‘ ./bin.setdonedml_set=`awk ‘{print $1}‘ ./bin.set | tr "n" "," | sed ‘s/,$//‘`echo "update $table_name set $dml_set where $dml_where;" >> ./rollback.sqlsed -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 "33[47;30m change column‘s names 33[0m"

cat ./table.cnf | whileread t_tmpdot_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.sqldone

echo done}case $1 indelete)echo -e "33[47;32m begin fun_delete 33[0m";sleep 2;fun_delete

;;

update)echo -e "33[47;32m begin fun_update 33[0m";sleep 2;fun_update

;;*)echo -e "33[47;31m err input,please choose delete or update,quit 33[0m";exit 1

esac