Fork me on GitHub

binlog2sql之MySQL数据闪回实战

字数统计: 1.8k字   |   阅读时长≈ 8分

MySQL闪回特性最早由阿里彭立勋开发，彭在2012年给官方提交了一个patch，并对闪回设计思路做了说明。但是因为种种原因，业内安装这个patch的团队至今还是少数，真正应用到线上的更是少之又少。

而binlog2sql则是借鉴的这种思路，它由美团点评DBA团队(上海)出品，多次在线上环境做快速回滚。

适用场景

某天，同事A误删了大批线上用户MySQL表的数据……

他急忙找到公司DBA请求帮助，“客服电话已被打爆，大量用户投诉无法登陆，领导非常恼火。请问多久能恢复数据？”DBA一脸懵逼，沉默十秒后，伸出一根手指。“你的意思是一分钟就能恢复？太好了。”小明终于有些放松，露出了一丝笑容。“不，我们中有个人将会离开公司……” DBA沉痛的说道。

参考：GitHub MySQL闪回

MySQL binlog概述

(1) MySQL binlog已event的形式，记录了MySQL Server从启用binlog以来所有的变更信息，能够帮助重现这之间的所有变化。

(2) MySQL 引入binlog主要有两个目的：

  • 为了主从复制
  • 某些备份还原操作后需要重新应用binlog

(3) binlog格式共分为3种，各有优缺点:

  • statement: 基于SQL语句的模式，binlog数据量小，但是某些语句和函数在复制过程中可能导致数据不一致，甚至报错。

  • row: 基于行的模式，记录的是行的完整变化。很安全，但是binlog文件会比其他两种模式大很多。

  • mixed: 混合模式，根据语句来选用是statement还是row模式。

MySQL binlog闪回原理

利用binlog闪回，需要将binlog格式设置为row

既然binlog以event的形式记录了所有变更的信息，那么我们需要回滚event事务，从后往前回滚即可。

单个event的回滚原理

这里以表test.user来演示原理，同时需要使用binlog2sql将原始binlog转化成可读SQL

1
2
3
4
5
6
7
8
mysql> show create table test.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
  • 对于delete操作，我们从binlog提取出delete信息，生成回滚语句的insert。
1
2
原始：DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='小赵';
回滚：INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '小赵');
  • 对于insert操作，回滚SQL是delete
1
2
原始：INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '小钱');
回滚：DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='小钱';
  • 对于update操作，回滚SQL应该交换SET和WHERE的值
1
2
原始：UPDATE `test`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孙';
回滚：UPDATE `test`.`user` SET `id`=3, `name`='小孙' WHERE `id`=3 AND `name`='小李';

MySQL数据闪回实战

真实的闪回场景中，最关键的是能快速筛选出真正需要回滚的SQL

安装binlog2sql，参考：binlog分析工具之binlog2sql

背景

同事A，在11:44时误删了test库bigdata表的大批数据，需要紧急回滚。

1
2
3
4
5
6
7
8
#test库bigdata表原有数据

mysql> select * from bigdata;
+----------+------------------+-------------+---------+---------------------+
| class_id | class_name       | class_month | teacher | last_mod_ts         |
+----------+------------------+------------+----------+---------------------+
|        1 | bigdata intro.   |           8 | Mars    | 2019-04-04 10:41:42 |
|        2 | hadoop intro.    |           8 | Mars    | 2019-04-04 10:41:42 |
1
2
3
4
5
6
7
8
9
10
11
# 同事A在下午2019-04-04 14:47左右删除了表中大部分数据，此时，正常业务数据是在继续写入的。
mysql> delete from bigdata where last_mod_ts >'2019-04-04 10:45:00';
Query OK, 2 rows affected (0.07 sec)

mysql> select count(*) from bigdata;
+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)
恢复数据步骤
  • 登陆mysql，查看目前的binlog文件
1
2
3
4
5
6
7
8
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      6763 |
+------------------+-----------+
2 rows in set (0.00 sec)

可以看到，最新的binlog文件是 mysql-bin.000002。我们的目标是筛选出需要回滚的SQL。

由于同事A只知道大致误操作的时间，故首先根据时间做一次过滤，只需要解析test库bigdata表。

注：如果涉及多个sql误操作，则生成的binlog可能分布在多个文件，需解析多个文件

  • 解析binlog为标准SQL
1
# python binlog2sql/binlog2sql.py -h127.0.0.1 -uroot -p -P3306 -dtest -tbigdata --start-file='mysql-bin.000002' --start-datetime='2019-04-04 14:45:00' --stop-datetime='2019-04-04 14:50:00' >/tmp/raw.sql
1
2
3
4
5
# cat /tmp/raw.sql 

DELETE FROM `test`.`bigdata` WHERE `class_id`=19 AND `class_name`='Other' AND `class_month`=1704 AND `teacher`='GaryWu' AND `last_mod_ts`='2019-04-04 10:52:10' LIMIT 1; #start 6438 end 6732 time 2019-04-04 14:47:43

DELETE FROM `test`.`bigdata` WHERE `class_id`=20 AND `class_name`='Other' AND `class_month`=1705 AND `teacher`='wuyanteng' AND `last_mod_ts`='2019-04-04 10:52:25' LIMIT 1; #start 6438 end 6732 time 2019-04-04 14:47:43

根据位置信息，我们确定了误操作sql来自同一个事务，准确位置在6438-6732之间（binlog2sql对于同一个事务会输出同样的start position）。

  • 使用 -B 选项生成回滚SQL，并检查回滚SQL是否正确
1
# python binlog2sql/binlog2sql.py -h127.0.0.1 -uroot -p -P3306 -dtest -tbigdata --start-file='mysql-bin.000002' --start-datetime='2019-04-04 14:45:00' --stop-datetime='2019-04-04 14:50:00' -B >/tmp/rollback.sql

解析出的回滚SQL经常会需要进一步筛选并且需要与业务方确认SQL语句是否存在问题。

1
2
3
4
# cat /tmp/rollback.sql

INSERT INTO `test`.`bigdata`(`class_id`, `class_name`, `class_month`, `teacher`, `last_mod_ts`) VALUES (20, 'Other', 1705, 'wuyanteng', '2019-04-04 10:52:25'); #start 6438 end 6732 time 2019-04-04 14:47:43
INSERT INTO `test`.`bigdata`(`class_id`, `class_name`, `class_month`, `teacher`, `last_mod_ts`) VALUES (19, 'Other', 1704, 'GaryWu', '2019-04-04 10:52:10'); #start 6438 end 6732 time 2019-04-04 14:47:43
  • 如果一切准确无误，则执行回滚语句
1
2
3
4
5
6
7
8
9
mysql -h127.0.0.1 -P3306 -uroot -p < /tmp/rollback.sql

mysql> select count(*) from bigdata;
+----------+
| count(*) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)
MySQL闪回思想
  • 闪回的目标：快速筛选出真正需要回滚的数据。
  • 先根据库、表、时间做一次过滤，再根据位置做更准确的过滤。
  • 由于数据一直在写入，要确保回滚sql中不包含其他数据。可根据是否是同一事务、误操作行数、字段值的特征等等来帮助判断。
  • 执行回滚sql时如有报错，需要查实具体原因，一般是因为对应的数据已发生变化。由于是严格的行模式，只要有唯一键(包括主键)存在，就只会报某条数据不存在的错，不必担心会更新不该操作的数据。业务如果有特殊逻辑，数据回滚可能会带来影响。
  • 如果只回滚某张表，并且该表有关联表，关联表并不会被回滚，需与业务方沟通清楚。

总之，哪些数据需要回滚，让业务方来判断！

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true


不论我写了什么...
其实都是错的......