文件 table.cnf@1=id@2=column_a@3=column_b@4=time文件not_used.set##写到这个文件里面的是update回滚时不需要更新的列##例如假设回滚不恢复 id 列，文件中应该如下@1=文件not_used.where##写到这个文件里面的是update回滚时条件忽略的列##例如假设回滚时不需要列 time 和 column_b 作为回滚条件，文件中应该如下，顺序不敏感@=3@=4

文件not_used.values
##写到这个文件里面的是delete回滚时不自动插入的列，例如自增列或者TIMESTAMP
##例如假设回滚时不需要列 time 和 id 作为回滚条件，文件中应该如下，顺序不敏感

@4=
@1=

#!/bin/bashtable_name="test_update"### DELETE DML 2 rows in binlogdelete=2### UPDATE DML 3 rows in binlogupdate=3### How many columns for this rollback tabletable_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 ./mysql-bin.txt | awk ‘{$1="";print>"./bin.log"}‘ #echo |  awk ‘{$1="";print}‘ ./mysql-bin.txt  > ./bin.log### Count for DMLdml_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 DMLdml_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.tmp        sed -i ‘1,‘$delete‘d‘ ./bin.tmp    cat ./not_used.values | while read columns_values        do                sed -i ‘/‘$columns_values‘/d‘ ./bin.tmp        done     data=`awk -F ‘=‘ ‘{$1="";print}‘ ./bin.tmp | awk ‘{print $1}‘ | tr "\n" "," | sed ‘s/,$//‘ `#        data=`sed ‘s/,$//‘ ./sql.tmp`            cp ./table.cnf ./dml_columns.tmp    cat ./not_used.values | while read columns_values        do                sed -i ‘/‘$columns_values‘/d‘ ./dml_columns.tmp        done    dml_columns=`awk -F ‘=‘ ‘{print $2}‘ ./dml_columns.tmp | tr "\n" "," | sed ‘s/,$//‘`        echo "insert into $table_name($dml_columns) 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=#$bdonerm -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.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"donerm -rf ./bin.*echo -e "\n"echo -e "\033[47;30m change column‘s names \033[0m"cat ./table.cnf | while read t_tmpdo    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.sqldoneecho 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 1esac