暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / MySQL数据闪回

MySQL数据闪回

IT那活儿 2022-03-10
1657

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

背景描述

开发人员在线上环境更新或者误删除了数据，并且影响比较大，就需要能快速的回滚。
传统恢复的方法是利用备份重新搭建实例的方式来进行恢复，但如果数据量比较大的情况下，这样的做法显然会付出比较大的代价。
接下来介绍一种高效的数据闪回工具：binlog2sql
方案描述

1. 适用范围：

  • MySQL

  • 平台：Linux

2. 闪回原理：
MySQL binlog 以 event 为单位，记录数据库的变更信息，这些信息能够帮助我们重现这之间的所有变化，也就是所谓的闪回。利用 binlog 做闪回，需要将 binlog 格式设置为 row，因为我们需要最详尽的信息来确定操作之后数据不会出错。
既然 binlog 以 event 形式记录了所有的变更信息，那么我们把需要回滚的 event，从后往前回滚回去即可。
3. 回滚操作：
1）对于 delete 操作，我们从 binlog 提取出 delete 信息，反向生成 insert 回滚语句；
2）对于 insert 操作，反向生成 delete 回滚语句；
3）对于 update 操作，根据信息生成反向的 update 语句。
4. 前提要求：
1）mysql配置要求：
[mysqld]server_id = 1log_bin = data/mysql/log/binary/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full
2）用户权限要求
SELECT, REPLICATION SLAVE, REPLICATION CLIENT。

  • select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句

  • super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表

  • replication slave：通过BINLOG_DUMP协议获取binlog内容的权限

3）binlog2sql的使用参数说明：

  • mysql连接配置

    -h host; -P port; -u user; -p password

  • 解析模式

    --stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
    -K, --no-primary-key 对INSERT语句去除主键。可选。
    -B, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句SLEEP SELECT。可选。与stop-never或no-primary-key不能同时添加。

  • 解析范围控制

    --start-file 起始解析文件。必须。
    --start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
    --stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
    --stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
    --start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。
    --stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。

  • 对象过滤

    -d, --databases 只输出目标db的sql。可选。默认为空。
    -t, --tables 只输出目标tables的sql。可选。默认为空。
5. 操作步骤：
1）进行用户授权，以下测试使用root用户，故不在创建新的用户。
2）测试库测试：
mysql> show global variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+1 row in set (0.00 sec)mysql>mysql> show master status;+------------------+----------+| File | Position |+------------------+----------+| mysql-bin.000107 | 120 |+------------------+----------+1 row in set (0.00 sec)mysql> select * from tb1;+------+------+| id | name |+------+------+|    1 | aa || 2 | bb |+------+------+2 rows in set (0.00 sec)mysql>
3）进行数据的DML操作：
mysql>  insert into tb1 values (3,'cc');Query OK, 1 row affected (0.00 sec)mysql>  insert into tb1 values (4,'dd');Query OK, 1 row affected (0.00 sec)mysql>  update tb1 set name='new_aa' where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql>  delete from tb1 where id=2;Query OK, 1 row affected (0.00 sec)mysql>  select * from tb1;+------+--------+| id | name |+------+--------+| 1 | new_aa || 3 | cc || 4 | dd |+------+--------+3 rows in set (0.00 sec)mysql>
4）使用binlog2sql进行格式为ROW的binlog生成标准SQL，带个-d的参数指定库名：
[root@db_server_xuanzhi ~]#python binlog2sql.py -uroot -h127.0.0.1 -proot -dxuanzhi --start-file='mysql-bin.000107' > xuanzhi.sql[root@db_server_xuanzhi ~]#cat xuanzhi.sqlINSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (3, 'cc'); #start 4 end 290 time 2022-01-23 10:41:34INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (4, 'dd'); #start 321 end 491 time 2022-01-23 10:41:38UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='new_aa' WHERE `id`=1 AND `name`='aa' LIMIT 1; #start 522 end 705 time 2022-01-23 10:41:42DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`='bb' LIMIT 1; #start 736 end 906 time 2022-01-23 10:41:50[root@db_server_xuanzhi ~]#
5）对xuanzhi这个库的所有操作生成反向SQL，这个时候需要在上面语句的基础上带一个-B参数，就是flashback闪回的意思：
[root@db_server_xuanzhi ~]#python binlog2sql.py -uroot -h127.0.0.1 -proot -dxuanzhi --start-file='mysql-bin.000107' -B > rollback_xuanzhi.sql[root@db_server_xuanzhi ~]#cat rollback_xuanzhi.sql INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (2, 'bb'); #start 736 end 906 time 2022-01-23 10:41:50UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`='aa' WHERE `id`=1 AND `name`='new_aa' LIMIT 1; #start 522 end 705 time 2022-01-23 10:41:42DELETE FROM `xuanzhi`.`tb1` WHERE `id`=4 AND `name`='dd' LIMIT 1; #start 321 end 491 time 2022-01-23 10:41:38DELETE FROM `xuanzhi`.`tb1` WHERE `id`=3 AND `name`='cc' LIMIT 1; #start 4 end 290 time 2022-01-23 10:41:34[root@db_server_xuanzhi ~]#
6）最后导入生成的回滚sql：
mysql>  use xuanzhiDatabase changedmysql>  source /tmp/rollback_xuanzhi.sql
可以看到正常恢复，但值得注意的是drop table 和truncate table 是无法生成反向SQL的,所以建议线上程序账号只给insert，upfate，select，delete权限。


本文作者：赵栋辉

本文来源：IT那活儿（上海新炬王翦团队）

mysql
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论