• 【MySQL】MariaDB10.2新特性--Flashback


    MariaDB10.2新特性--Flashback

    Flashback可以回滚到旧的数据,用于解决用户误删除数据的问题。

    实战例子

    MariaDB [zsd]> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 张盛东    |
    |    2 | 刘忠浩    |
    |    3 | 胡轲      |
    +------+-----------+
    3 rows in set (0.001 sec)
    
    MariaDB [zsd]> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2019-09-26 16:17:51 |
    +---------------------+
    1 row in set (0.000 sec)
    
    MariaDB [zsd]> delete from test;
    Query OK, 3 rows affected (0.012 sec)
    
    MariaDB [zsd]> commit;
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [zsd]> select * from test;
    Empty set (0.000 sec)
    

    对插入之前的时间点,做基于时间点的闪回查询,如下:

    [mysql@oradb ~]$ mysqlbinlog /u01/data/binlog/mysql-bin.000003 -vv -d zsd -T test --start-datetime="2019-09-26 16:17:51" --flashback
    

    mariadb它比Oracle更好的是,你知道恢复的数据是什么,如下:

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!40019 SET @@session.max_insert_delayed_threads=0*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    #190926 15:25:20 server id 30200  end_log_pos 256 CRC32 0x508a050f      Start: binlog v 4, server v 10.3.18-MariaDB-log created 190926 15:25:20 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    4GeMXQ/4dQAA/AAAAAABAAABAAQAMTAuMy4xOC1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAADgZ4xdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAEEwQADQgICAoKCgEPBYpQ
    '/*!*/;
    #190926 16:17:56 server id 30200  end_log_pos 1073 CRC32 0x57f3bac4     Annotate_rows:
    #Q> delete from test
    #190926 16:17:56 server id 30200  end_log_pos 1126 CRC32 0x1d0d9eaf     Table_map: `zsd`.`test` mapped to number 33
    # Number of rows: 3
    #190926 16:17:56 server id 30200  end_log_pos 1359 CRC32 0xb26e3c08     Xid = 98
    BEGIN/*!*/;
    #190926 16:17:56 server id 30200  end_log_pos 1328 CRC32 0x97af664e     Update_rows: table id 33 flags: STMT_END_F
    
    BINLOG '
    NHSMXRP4dQAANQAAAGYEAAAAACEAAAAAAAEAA3pzZAAEdGVzdAAEAw8REQQ8AAYGA6+eDR0=
    NHSMXRj4dQAAygAAADAFAAAAACEAAAAAAAEABP//8AMAAAAG6IOh6L2yXYxrKARwLV2MdDQHfbTw
    AwAAAAbog6HovbJdjGsoBHAtf////w9CP/ACAAAACeWImOW/oOa1qV2MauMNiCVdjHQ0B3208AIA
    AAAJ5YiY5b+g5rWpXYxq4w2IJX////8PQj/wAQAAAAnlvKDnm5vkuJxdjGrjDYglXYx0NAd9tPAB
    AAAACeW8oOebm+S4nF2MauMNiCV/////D0I/Tmavlw==
    '/*!*/;
    ### UPDATE `zsd`.`test`
    ### WHERE
    ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='胡轲' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483560.290861 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=1569485876.490932 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ### SET
    ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='胡轲' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483560.290861 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ### UPDATE `zsd`.`test`
    ### WHERE
    ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='刘忠浩' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483491.886821 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=1569485876.490932 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ### SET
    ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='刘忠浩' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483491.886821 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ### UPDATE `zsd`.`test`
    ### WHERE
    ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='张盛东' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483491.886821 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=1569485876.490932 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ### SET
    ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='张盛东' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
    ###   @3=1569483491.886821 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    ###   @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
    COMMIT
    /*!*/;
    COMMIT
    /*!*/;
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    

    执行flashback恢复操作

    目的:闪回数据库zsd中的test 表。

    [mysql@oradb ~]$ mysqlbinlog /u01/data/binlog/mysql-bin.000003 -vv -d zsd -T test --start-datetime="2019-09-26 16:17:51" --flashback | mysql -uroot -predhat
    
    [mysql@oradb ~]$ mysql -uroot -predhat
    MariaDB [(none)]> use zsd;
    
    Database changed
    MariaDB [zsd]> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 张盛东    |
    |    2 | 刘忠浩    |
    |    3 | 胡轲      |
    +------+-----------+
    3 rows in set (0.000 sec)
    
    MariaDB [zsd]> 
    

    可以看到之前删除的三条数据,恢复了。

    参考文献

    https://mariadb.com/kb/en/library/flashback/

  • 相关阅读:
    杂记
    asp.net preview 5 bug[转]
    jquery笔记
    北京互联网创业团队诚邀英才加盟
    伊瓜苏大瀑布
    log4net udp组件的应用
    Mock介绍
    自写的BackgroundWorker的学习例子
    RegexBuddy使用例子,及Visual Studio中正则使用的请教
    TestDriven.NET2.14.2190(not RTM) last update at 2008723
  • 原文地址:https://www.cnblogs.com/zhangshengdong/p/11724941.html
Copyright © 2020-2023  润新知