maria-developers team mailing list archive

[Lixun Peng <penglixun@xxxxxxxxx>]

Hi Sergei,

Do you have a look this patch?
We have used it in production environment.

Thanks,
Lixun



On Sat, Apr 6, 2013 at 9:48 PM, Sergei Golubchik <serg@xxxxxxxxxxxx> wrote:

> Hi, Lixun!
>
> Great, thanks!
> I'll look at it as soon as possible.
>
> On Apr 06, Lixun Peng wrote:
> > Hi all,
> >
> > I pushed my patch to
> >
> http://bazaar.launchpad.net/~maria-captains/maria/10.0-flashback/revision/3510
> >
> > Compare with the pre-patch, I modified the exchange_update_rows()
> function,
> > it will revert the rows in one update event, too.
> >
> >
> > On Wed, Mar 27, 2013 at 3:38 PM, Lixun Peng <penglixun@xxxxxxxxx> wrote:
> >
> > > 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.
>
> Regards,
> Sergei
>



-- 
Senior Database Engineer @ Taobao.com

Mobile Phone: +86 18658156856 (Hangzhou)
Gtalk: penglixun(at)gmail.com
Twitter: http://www.twitter.com/plinux
Blog: http://www.penglixun.com