Hi Monty, I patched my flashback feature to 10.0-base, and this is the diff file, please help me to review it. How to use this feature? I added several new arguments for mysqlbinlog: --flashback (-B), it will let mysqlbinlog working on FLASHBACK mode. --table (-T), it likes -d, but this argument is for table. --review, it will le mysqlbinlog print the SQL for reviewing. Reviewing feature will create a new "review" table to record the data that will be changed by FLASHBACK feature. If you aren't special the --review-dbname/tablename, "review" table will created on current DB, and the table name will be "__"+original_table_name. And for getting the original table struct, mysqlbinlog need to connect mysql, so --user/--host/--password is needed if necessary. --review-dbname, the DB that you want to store the review table. --review-tablename the TABLE that you want to store the changed data. Only if you set -T, this argument is useful. For example: I have a table "t" in database "test", CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `info` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9043880 DEFAULT CHARSET=utf8; And I done this operation, root@localhost : (none) 14:52:50> reset master; root@localhost : (none) 14:53:02> use test root@localhost : test 14:53:08> delete from t limit 10; root@localhost : test 14:53:25> insert into t (info) select "你好"; root@localhost : test 14:54:01> update t set info ='hello你好' limit 5; We can compare the output with --flashback or not. #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" > /tmp/1.sql #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -uroot -d test -T t --start-datetime="2013-03-27 14:54:00" -B --review
/tmp/2.sql
How to implement Flashback: 1. mysqlbinlog tool can parse the binlog files and print them, so we can use the most of code in mysqlbinlog. 2. Changing the event type (INSERT -> DELETE, DELETE -> INSERT) 3. Exchanging the SET/WHERE part in UPDATE (UPDATE table SET x=1 WHERE y=2 -> UPDATE table SET y=2 WHERE x=1) 4. Invert the event output, from end to beginning (binlog: e1,e2,e3... -> flashback_log: en...e3,e2,e1) Then, importing the flashback output can flashback database/table to the special time. But binlog_format should be ROW. Thanks, Lixun -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com