mysql 回滚工具_【MySQL】MySQL回滚工具-CSDN博客

本文链接：https://blog.csdn.net/weixin_39956182/article/details/113199140

#!/bin/bash

table_name="$2"### 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 binlogecho -e "\033[47;30m wait for change binlog format \033[0m"#cat ./mysql-bin.txt | awk '{$1="";print>"./bin.log"}'

echo -e "\033[47;30m change binlog format OK \033[0m"### CountforDML

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 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 "\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.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/,$//'`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"

done

rm -rf ./bin.logecho -e "\n"

echo done}

fun_update()

{if [ $dml_update_count -lt 5000]then

file=1

else

file=1000

fifile_count=$[${dml_update_count}/${file}]

file_mod=$[${dml_update_count}%${file}]

file_dml_pos_begin=1file_dml_pos_finish=$[${file_count}*${dml_update_row}]for((f=1;f<=$[${file}+1];f++))do

sed -n ''$file_dml_pos_begin','$file_dml_pos_finish'p' ./bin.log > ./bin.log.$f

rows_no_update_begin=1rows_no_update_finish=$dml_update_rowfor((i=1;i<=${dml_update_count};i++))do

sed -n ''$rows_no_update_begin','$rows_no_update_finish'p' ./bin.log.$f > ./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.sql

# delete big bin.log too slow

#sed -i '1,'$dml_update_row'd' ./bin.log

rows_no_update_begin=$[$[${dml_update_row}*${i}]+1]

rows_no_update_finish=$[${dml_update_row}*$[${i}+1]]

# change columns'name

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

donefile_dml_pos_begin=$[$[${file_count}*${f}*${dml_update_row}]+1]

file_dml_pos_finish=$[${file_count}*$[${f}+1]*${dml_update_row}]rm -rf ./bin.log.$f

h=`echo | awk '{print int("'$f'"/"'$dml_update_count'"*"100%")}'`

printf"progress:[$h%%]\r"

echo -e "\n"