Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10570

Integrate existing DML only “Flashback” for MariaDB Server 10.2

    Details

      Description

      An implementation exists which needs to be

      • ported to MariaDB Server 10.2.
      • tested with 10.2
      • mysqlbinlog changes need to be tested against 10.2

      This Sub-Tasks does not cover new implementations to be able to "undo" DDL based changes.

      ==== Description ====

      Flashback can rollback the instances/databases/tables to an old snapshot.
      It's implement on Server-Level by full image format binary logs (--binlog-row-image=FULL), so it supports all engines.
      Currently, it’s a feature inside mysqlbinlog tool (with --flashback arguments).

      Because the flashback binlog events will store in the memory, you should check if there is enough memory in your machine.

      ==== New Arguments ====

      --flashback (-B)
      It will let mysqlbinlog to work on FLASHBACK mode.

      ==== Example ====

      I have a table "t" in database "test", we can compare the output with "--flashback" and without.

      1. 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
      2. client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" -B > /tmp/2.sql

      Then, importing the output flashback file (/tmp/2.log), it can flashback your database/table to the special time (--start-datetime).
      And if you know the exact postion, " – start-postion " is also works, mysqlbinlog will output the flashback logs that can flashback to " – start-postion " position.

      ==== Implement ====

      1. As we know, if binlog_format is ROW (binlog-row-image=FULL in 10.1 and later), all columns value are store in the row event, so we can get the data before mis-operation.

      2. Just do following things:

      2.1 Change Event Type, INSERT->DELETE, DELETE->INSERT.
      For example:
      INSERT INTO t VALUES ( ... ) ---> DELETE FROM t WHERE ...
      DELETE FROM t ... ---> INSERT INTO t VALUES ( ... )

      2.2 For Update_Event, swapping the SET part and WHERE part.
      For example:
      UPDATE t SET cols1 = vals1 WHERE cols2 = vals2
      --->
      UPDATE t SET cols2 = vals2 WHERE cols1 = vals1

      2.3 For Multi-Rows Event, reverse the rows sequence, from the last row to the first row.
      For example:
      DELETE FROM t WHERE id=1; DELETE FROM t WHERE id=2; ... ; DELETE FROM t WHERE id=n;
      --->
      DELETE FROM t WHERE id=n; ... ; DELETE FROM t WHERE id=2; DELETE FROM t WHERE id=1;

      2.4 Output those events from the last one to the first one which mis-operation happened.
      For example:
      Evnet(1); Event(2); ... ; Event( n );
      --->
      Event( n ); ... ; Event(2); EVent(1);

      2.5 Import the output file, then all the data will be recovered by inverse operations of mis-oprerations.

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              ralf.gebhardt@mariadb.com Ralf Gebhardt
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: