资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/MySQL数据闪回

MySQL数据闪回

IT那活儿 / 1327人阅读
MySQL数据闪回

点击上方“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 = 1
log_bin = /data/mysql/log/binary/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_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: 0

mysql>  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.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (3, cc); #start 4 end 290 time 2022-01-23 10:41:34
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (4, dd); #start 321 end 491 time 2022-01-23 10:41:38
UPDATE `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:42
DELETE 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:50
UPDATE `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:42
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=4 AND `name`=dd LIMIT 1; #start 321 end 491 time 2022-01-23 10:41:38
DELETE 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 xuanzhi
Database changed
mysql>  source /tmp/rollback_xuanzhi.sql
可以看到正常恢复，但值得注意的是drop table 和truncate table 是无法生成反向SQL的,所以建议线上程序账号只给insert，upfate，select，delete权限。



本文作者：赵栋辉

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

GPU云服务器 云服务器 不用闪回怎么恢复数据库 闪回 闪回技术 mysql 大数据

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129634.html

相关文章

  • MySQL误操作后如何快速恢复数据

    摘要：基本上每个跟数据库打交道的程序员当然也可能是你同事都会碰一个问题，误操作后如何快速回滚比如，一张表，忘加限制条件，整张表都没了。误操作后，能快速回滚数据是非常重要的。登录确认，数据回滚成功。所以，误操作的话一般只能通过备份来恢复。 基本上每个跟数据库打交道的程序员（当然也可能是你同事）都会碰一个问题，MySQL误操作后如何快速回滚？比如，delete一张表，忘加限制条件，整张表都没了。...

    taohonghui 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<