参考：http://wubx.net/?s=mysqlbinlog
mysql官方的mysqlbinlog没有回滚的功能，淘宝大牛对官方代码进行了修改使之能够将binlog中的DML操作变成互逆的语句，比如delete变为insert，update的set和where的互换。不过注意前提是binlog的格式必须是binlog_format=ROW
另外注意导入回滚数据较多时，需要调整超时时间和最大package的大小：
binlog_format=ROW
max_allowed_packet=1024M
#max_allow_packet大小不够时报错如下：
ERROR 1153 (08S01) at line 403133: Got a packet bigger than ‘max_allowed_packet’ bytes
重构代码
http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog
有三个版本，测试percona5518版本可以使用5.5.18_flashback_all.diff，mysql官方版本只能使用5.5.18_flashback.diff。
http://mysql.taobao.org/images/5/53/5.5.18_flashback_all.diff
http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
percona5.5.18版本的下载链接：
mysql5.5.18
http://cdn.mysql.com/archives/mysql-5.5/mysql-5.5.18.tar.gz
cd mysql-5.5./
wget http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
patch -p0 < 5.5.18_flashback.diff
## 编译成功无报错即可，无特殊编译参数要求
编译完成后，开始测试：
[root@server1 /]# /percona5518/bin/mysqlbinlog --help
在说明中多了
-B, --flashback Flashback data to start_postition or start_datetime.
然后可以用一个binlog文件做测试
mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec) mysql> select * from t1 where id<5;
+----+------+------+
| id | a | b |
+----+------+------+
| 1 | aaaa | bbbb |
| 2 | aaaa | bbbb |
| 3 | aaaa | bbbb |
| 4 | aaaa | bbbb |
+----+------+------+
4 rows in set (0.00 sec) mysql> flush logs;
Query OK, 0 rows affected (0.11 sec) mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000010
Position: 108
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec) mysql> update t1 set a='XXXX',b='SSSS' where id<5;
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush logs;
Query OK, 0 rows affected (0.09 sec)
打印出binlog
[root@server1 bin]# ./mysqlbinlog -uroot -p ../data/mysql-bin. -v -v --base64-output=decode-rows
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at
# :: server id end_log_pos Start: binlog v , server v 5.5.-log created ::
# at
# :: server id end_log_pos Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;
SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at
# at
# :: server id end_log_pos Table_map: `test`.`t1` mapped to number
# :: server id end_log_pos Update_rows: table id flags: STMT_END_F
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
# at
# :: server id end_log_pos Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos Rotate to mysql-bin. pos:
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
打印出回滚的binlog
[root@server1 bin]# ./mysqlbinlog -uroot -p ../data/mysql-bin. -v -v --base64-output=decode-rows -B
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# :: server id end_log_pos Start: binlog v , server v 5.5.-log created ::
# :: server id end_log_pos Rotate to mysql-bin. pos:
# :: server id end_log_pos Xid =
COMMIT/*!*/;
# :: server id end_log_pos Table_map: `test`.`t1` mapped to number
# :: server id end_log_pos Update_rows: table id flags: STMT_END_F
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### UPDATE test.t1
### WHERE
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='XXXX' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='SSSS' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### SET
### @= /* INT meta=0 nullable=0 is_null=0 */
### @='aaaa' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @='bbbb' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
delete语句的回滚是把delete换成了insert
在导入的时候遇到了问题，导入一直不成功。
[root@server1 bin]# ./mysqlbinlog ../data/mysql-bin.000010 -v -v --base64-output=decode-rows -B | ./mysql -uroot -p -S ../tmp/mysql.sock test
没有效果！
后来发现是参数的问题，再试成功了，如果使用mysql官方版本好像除了-B 参数不用带其他参数，如果测试有问题可以增减参数试试。
[root@server1 bin]# ./mysqlbinlog ../data/mysql-bin. -v-v -B | ./mysql -uroot -p -S ../tmp/mysql.sock test
另外注意几个比较实用的参数
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: -- :: (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: -- :: (you should
probably use quotes for your shell to set it properly).
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line. -d, --database=name List entries for just this database (local log only). -f, --force-read Force reading unknown binlog events.
恢复一个事务，找到开始和结束的pos位置
[root@server1 bin]# ./mysqlbinlog -v -v ../mysql-bin. --start-position= --stop-position= -B | ./mysql -uroot -p -S ../tmp/mysql.sock test
如何能回滚一个binlog中一个表的数据而不影响其他表？如果是一个事务可以使用“--start-position=”和“--stop-position=”，如果不是一个连续的事务呢？
参考：http://hcymysql.blog.51cto.com/5223301/1553080
不过我在5.5的版本中测试不能给库级和表级授权super