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 ##写到这个文件里面的是update回滚时不需要更新的列 ##例如假设回滚不恢复 id 列，文件中应该如下 @1= 文件not_used.where ##写到这个文件里面的是update回滚时条件忽略的列 ##例如假设回滚时不需要列 time 和 column_b 作为回滚条件，文件中应该如下，顺序不敏感 @=3 @=4
文件not_used.values
##写到这个文件里面的是delete回滚时不自动插入的列，例如自增列或者TIMESTAMP
##例如假设回滚时不需要列 time 和 id 作为回滚条件，文件中应该如下，顺序不敏感
@4=
@1=
有的表列比较多，写个脚本自己拼配置文件
mysql里面show create table，把结果写到table.txt
#!/bin/bash awk '{print $1}' ./table.txt >./table.ini n=`wc -l ./table.ini` i=1 cat ./table.ini | while read columns_name do echo ""@"$i"="$columns_name" >> ./table.cnf i=$[$i+1] done rm -rf ./table.txt ./table.ini
然后not_used.where、not_used.set、not_used.values也可以用table.cnf转换一下编辑
awk -F '`' '{print $1}' table.cnf > ./not_used.set
脚本：表名自己写吧
#!/bin/bash table_name="$2" ### 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 echo -e " 33[47;30m wait for change binlog format 33[0m" #cat ./mysql-bin.txt | awk '{$1="";print>"./bin.log"}' echo -e " 33[47;30m change binlog format OK 33[0m" ### 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 " 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 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 " 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.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 " " "," | sed 's/,$//' ` 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 " " "," | 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%%] " done rm -rf ./bin.log echo -e " " echo done } fun_update() { if [ $dml_update_count -lt 5000 ] then file=1 else file=1000 fi file_count=$[${dml_update_count}/${file}] file_mod=$[${dml_update_count}%${file}] file_dml_pos_begin=1 file_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=1 rows_no_update_finish=$dml_update_row for((i=1;i<=${dml_update_count};i++)) do sed -n ''$rows_no_update_begin','$rows_no_update_finish'p' ./bin.log.$f > ./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 " " "," | 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 " " "," | 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 | 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 done file_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%%] " echo -e " " done echo done } case $1 in delete)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