美文网首页DBA
MariaDB的闪回flashback

MariaDB的闪回flashback

作者: 丶Daniel | 来源:发表于2019-12-10 15:32 被阅读0次

    测试背景
    MariaDB是MySQL的分支，使用也是越来越广泛。闪回是对误操作最大的福音，本文是用来测试MariaDB支持的闪回功能

    测试环境

    • MariaDB 10.2.22
    • CentOS 7.4
    • mysqlbinlog工具

    测试要求

    • binlog_format=ROW
    • binlog_row_image=FULL
    • DML操作（INSERT, DELETE, UPDATE）

    测试步骤

    [root@mariadb ~]# mysqlbinlog --help | grep flashback
  -B, --flashback     Flashback feature can rollback you committed data to a special time point.

MariaDB [testdb]> select now();create table test ( id int primary key, name varchar(20) );           
+---------------------+
| now()               |
+---------------------+
| 2019-02-28 10:06:47 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

MariaDB [testdb]> select now();insert into test values(1,'lei'),(2,'liu'),(3,'chen');                                 
+---------------------+
| now()               |
+---------------------+
| 2019-02-28 10:07:00 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 3 rows affected (0.46 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select now();create table test2 ( id int primary key, name varchar(20) );                  
+---------------------+
| now()               |
+---------------------+
| 2019-02-28 10:07:22 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

MariaDB [testdb]> select now();insert into test2 values(4,'lei'),(5,'liu'),(6,'chen'); 
+---------------------+
| now()               |
+---------------------+
| 2019-02-28 10:07:43 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

    查看闪回单库（所有表）信息

    [root@mariadb ~]# mysqlbinlog -vv --start-datetime='2019-02-28 10:06:40' -B -d testdb  /var/lib/mysql/archive/mysql-bin.000001 > /root/flushback-all.sql    
Warning: mysqlbinlog: unknown variable 'loose_default-character-set=utf8'

[root@mariadb ~]# cat flushback-all.sql 
/*!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 /*!*/;
#190228 10:06:33 server id 3306102  end_log_pos 256 CRC32 0xf870631d    Start: binlog v 4, server v 10.2.22-MariaDB-log created 190228 10:06:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
KUJ3XA92cjIA/AAAAAABAAABAAQAMTAuMi4yMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAApQndcEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgEdY3D4
'/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 620 CRC32 0x0270792a    Annotate_rows:
**这里显示的是原SQL**
#Q> insert into test values(1,'lei'),(2,'liu'),(3,'chen')
#190228 10:07:00 server id 3306102  end_log_pos 672 CRC32 0x7ea7d664    Table_map: `testdb`.`test` mapped to number 26
#190228 10:07:43 server id 3306102  end_log_pos 1058 CRC32 0x538d2013   Annotate_rows:
**这里显示的是原SQL**
#Q> insert into test2 values(4,'lei'),(5,'liu'),(6,'chen')
#190228 10:07:43 server id 3306102  end_log_pos 1111 CRC32 0x945d77fc   Table_map: `testdb`.`test2` mapped to number 28
#190228 10:07:43 server id 3306102  end_log_pos 1203 CRC32 0x9ca76eb2   Xid = 139
BEGIN/*!*/;
#190228 10:07:43 server id 3306102  end_log_pos 1172 CRC32 0x310419eb   Delete_rows: table id 28 flags: STMT_END_F

BINLOG '
b0J3XBN2cjIANQAAAFcEAAAAABwAAAAAAAEABnRlc3RkYgAFdGVzdDIAAgMPAjwAAvx3XZQ=
b0J3XBl2cjIAPQAAAJQEAAAAABwAAAAAAAEAAv/8BgAAAARjaGVu/AUAAAADbGl1/AQAAAADbGVp
6xkEMQ==
'/*!*/;
**这里显示的是回滚SQL**
### DELETE FROM `testdb`.`test2`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='chen' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test2`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liu' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test2`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='lei' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
COMMIT
/*!*/;
#190228 10:07:22 server id 3306102  end_log_pos 939 CRC32 0xaacc45b2    Query   thread_id=16    exec_time=0     error_code=0
SET TIMESTAMP=1551319642/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 764 CRC32 0x3fb04d27    Xid = 132
BEGIN/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 733 CRC32 0x221071e2    Delete_rows: table id 26 flags: STMT_END_F

BINLOG '
REJ3XBN2cjIANAAAAKACAAAAABoAAAAAAAEABnRlc3RkYgAEdGVzdAACAw8CPAACZNanfg==
REJ3XBl2cjIAPQAAAN0CAAAAABoAAAAAAAEAAv/8AwAAAARjaGVu/AIAAAADbGl1/AEAAAADbGVp
4nEQIg==
'/*!*/;
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='chen' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liu' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='lei' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
COMMIT
/*!*/;
#190228 10:06:47 server id 3306102  end_log_pos 502 CRC32 0xbd5d94cf    Query   thread_id=16    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1551319607/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    查看闪回单表信息

    [root@mariadb ~]# mysqlbinlog -vv --start-datetime='2019-02-28 10:06:40' -B -d testdb -T test /var/lib/mysql/archive/mysql-bin.000001 > /root/flushback-test.sql

[root@mariadb ~]# cat flushback-test.sql 
/*!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 /*!*/;
#190228 10:06:33 server id 3306102  end_log_pos 256 CRC32 0xf870631d    Start: binlog v 4, server v 10.2.22-MariaDB-log created 190228 10:06:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
KUJ3XA92cjIA/AAAAAABAAABAAQAMTAuMi4yMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAApQndcEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgEdY3D4
'/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 620 CRC32 0x0270792a    Annotate_rows:
#Q> insert into test values(1,'lei'),(2,'liu'),(3,'chen')
#190228 10:07:00 server id 3306102  end_log_pos 672 CRC32 0x7ea7d664    Table_map: `testdb`.`test` mapped to number 26
#190228 10:07:43 server id 3306102  end_log_pos 1203 CRC32 0x9ca76eb2   Xid = 139
BEGIN/*!*/;
COMMIT
/*!*/;
#190228 10:07:22 server id 3306102  end_log_pos 939 CRC32 0xaacc45b2    Query   thread_id=16    exec_time=0     error_code=0
SET TIMESTAMP=1551319642/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 764 CRC32 0x3fb04d27    Xid = 132
BEGIN/*!*/;
#190228 10:07:00 server id 3306102  end_log_pos 733 CRC32 0x221071e2    Delete_rows: table id 26 flags: STMT_END_F

BINLOG '
REJ3XBN2cjIANAAAAKACAAAAABoAAAAAAAEABnRlc3RkYgAEdGVzdAACAw8CPAACZNanfg==
REJ3XBl2cjIAPQAAAN0CAAAAABoAAAAAAAEAAv/8AwAAAARjaGVu/AIAAAADbGl1/AEAAAADbGVp
4nEQIg==
'/*!*/;
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='chen' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liu' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='lei' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
COMMIT
/*!*/;
#190228 10:06:47 server id 3306102  end_log_pos 502 CRC32 0xbd5d94cf    Query   thread_id=16    exec_time=0     error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1551319607/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    恢复操作

    [root@mariadb ~]# mysql -uroot -pabc123 < flushback-all.sql 

[root@mariadb ~]# mysql -uroot -pabc123

MariaDB [test]> use testdb
Database changed

MariaDB [testdb]> select * from test;
Empty set (0.00 sec)

MariaDB [testdb]> select * from test2;
Empty set (0.00 sec)

    总结
    MariaDB在10.2.4以上的版本中加入了flashback功能，但是只是针对于DML操作，原理和binlog2sql类似，但是劣势是拥有一定的局限性，优势自然是不需要额外下载软件了
    如果我们明确知道回滚的binlog的pos位点，可以使用--start-position生成回滚SQL
    使用mysqld启动MariaDB时，加上--flashback选项是代表开启binlog和binlog_format=ROW

    相关文章

      网友评论

        本文标题：MariaDB的闪回flashback

        本文链接：https://www.haomeiwen.com/subject/aujggctx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        DBA

        关于我们|服务条款|联系我们|MariaDB的闪回flashback|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！