maria-developers team mailing list archive

[Lixun Peng <penglixun@xxxxxxxxx>]

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

Attachment: mariadb-10-base_flashback.patch
Description: Binary data

Attachment: 1.sql
Description: Binary data

Attachment: 2.sql
Description: Binary data