表被delete后，如何flahsback
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 6 | mongodb |
+----+---------+
mysql> delete from t1;
由于被误删除的表有2个字段，在加上两行delete和where，所以取其中的4行
方法1：
mysqlbinlog -vvv mysql-bin.000002 >/tmp/002.log
grep "###" /tmp/002.log |grep "### DELETE FROM `mydb`.`t1`" -A 4 >/tmp/003.log
cat /tmp/003.log |sed 's/### //g' >/tmp/004.log
sed -i 's/\/\*.*/,/g' /tmp/004.log
sed -i 's/DELETE FROM/INSERT INTO/g' /tmp/004.log
cat /tmp/004.log | sed -r 's/(@4.*),/\1;/g' |sed 's/@[1-9]=//g' >/tmp/005.log
sed -i 's/WHERE/values(/g' /tmp/005.log
sed -i "s/' ,/' );/g" /tmp/005.log
1. 删除--上一行
sed -i -e :a -e '$!N;s/.*\n\(.*--.*\)/\1/;ta' -e 'P;D' /tmp/005.log
2. 删除--这一行
sed -i '/.*--.*/'d /tmp/005.log
mysql mydb
方法2：OK
环境：mysql5.6 binlog_format=row
mysql> desc exception_record;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| RECORDID | bigint(20) | NO | PRI | NULL | auto_increment |
| classname | varchar(100) | NO | | NULL | |
| systemid | varchar(15) | NO | | NULL | |
| hostname | varchar(30) | NO | | NULL | |
| timefrom | datetime | NO | MUL | NULL | |
| timeto | datetime | NO | MUL | NULL | |
| totalcount | int(11) | NO | | NULL | |
| servicename | varchar(100) | YES | | NULL | |
| linenum | varchar(10) | YES | | NULL | |
| methodname | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
mysql> delete from exception_record;
Query OK, 222 rows affected (0.01 sec)
1.将binlog解析到一个sql中
mysqlbinlog -vvv mysql-bin.000003 >/tmp/003.sql
2.逆向SQL脚本
cat /tmp/003.sql |sed -n '/###/p' |sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' >/tmp/insert.sql
脚本解析：
sed -n '/###/p' 显示###的行
sed 's/### //g //删除###
s/\/\*.*/,/g 用逗号替换/* */ \转义符 ;号在里面是持续下去的意思
如：
@1=1184414 /* LONGINT meta=0 nullable=0 is_null=0 */
@1=1184411 ,
3.用实际字段名替换@1,@2等字符
sed -i 's/@1/RECORDID/g;s/@2/classname/g;s/@3/systemid/g;s/@4/hostname/g;s/@5/timefrom/g;s/@6/timeto/g;s/@7/totalcount/g;s/@8/servicename/g;s/@9/linenum/g;s/@10/methodname/g' /tmp/insert.sql
4.将最后一个字段后面加；结束符号
sed -i -r 's/(RECORDID0=.*)./\1;/g' /tmp/insert.sql
5.将所有RECORD=的字符全去掉
sed -i 's/.*=//g' /tmp/insert.sql
6.导入数据库表
mysql monitor