MySQL · 误操作场景下数据快速恢复

背景

线上环境核心业务数据，delete/update 操作一张表，忘加 where 限制条件，如何快速恢复？

方案

• 备份恢复：先用全量备份恢复实例，再利用增量 binlog 备份恢复到误操作之前的状态，费时费力！
• 第三方工具：binlog2sql（支持 DML Delete/Update 等语句回滚，不支持 DDL Drop 等语句回滚）

binlog2sql 工具实战

支持

• MySQL 5.6/5.7
• Delete、Update

要求

• MySQL 配置要求

[mysqld]
server_id = 1
log_bin = /data/mysql/mysql-bin.log
binlog_format = row
binlog_row_image = full

mysql> show variables like 'server_id';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| server_id     | 3306100 |
+---------------+---------+

mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /data/mysql/mysql-binlog       |                        
+---------------------------------+--------------------------------+

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

• MySQL 权限要求：SELECT、REPLICATION SLAVE、REPLICATION CLIENT

mysql> show grants for 'cosmo_t'\G
*************************** 1. row ***************************
Grants for cosmo_t@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'cosmo_t'@'%'
1 row in set (0.00 sec)

• 申请特权账号，登录服务器

操作步骤

• 安装 pip

• 安装 binlog2sql

$ cd /usr/local/src
$ git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
$ pip install -r requirements.txt

• 根据误操作时间，确定 binlog 文件

mysql> show master status;
+---------------------+-----------+--------------+------------------+-------------------+
| File                | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000020    | 576640842 |              |                  |                   |
+---------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

$ ll /data/mysql | grep mysql-bin
-rw-r----- 1 mysql mysql 1.1G Oct 23 03:03 mysql-binlog.000224
-rw-r----- 1 mysql mysql 1.1G Oct 24 12:25 mysql-binlog.000225
-rw-r----- 1 mysql mysql 1.1G Oct 25 18:32 mysql-binlog.000226
-rw-r----- 1 mysql mysql 1.1G Oct 27 00:21 mysql-binlog.000227
-rw-r----- 1 mysql mysql 1.1G Oct 28 05:45 mysql-binlog.000228
-rw-r----- 1 mysql mysql 1.1G Oct 29 12:32 mysql-binlog.000229
-rw-r----- 1 mysql mysql 1.1G Oct 30 19:00 mysql-binlog.000230
-rw-r----- 1 mysql mysql 1.1G Nov  1 16:30 mysql-binlog.000231
-rw-r----- 1 mysql mysql 1.1G Nov  3 00:20 mysql-binlog.000232
-rw-r----- 1 mysql mysql 1.1G Nov  4 16:47 mysql-binlog.000233
-rw-r----- 1 mysql mysql 1.1G Nov  6 00:21 mysql-binlog.000234
-rw-r----- 1 mysql mysql 473M Nov  6 14:48 mysql-binlog.000235
-rw-r----- 1 mysql mysql  384 Nov  6 00:21 mysql-binlog.index

• 定位误执行 SQL

$ python binlog2sql.py -h'x.x.x.x' -uroot -p'xx' -dtest -temployees --start-file='mysql-bin.000020' --sql-type=DELETE > result.sql

DELETE FROM `test`.`employees` WHERE `name`='xujinliang' AND `gmt_modified`='2019-09-24 17:13:00' AND `age`=32 AND `gmt_create`='2019-09-24 17:13:00' AND `address`='山东省青岛市' AND `id`=1 LIMIT 1; #start 66374969 end 66375406 time 2019-10-21 15:38:49
DELETE FROM `test`.`employees` WHERE `name`='maofang' AND `gmt_modified`='2019-09-24 17:40:49' AND `age`=32 AND `gmt_create`='2019-09-24 17:32:00' AND `address`='山东省青岛市' AND `id`=2 LIMIT 1; #start 66374969 end 66375406 time 2019-10-21 15:38:49
DELETE FROM `test`.`employees` WHERE `name`='raohui' AND `gmt_modified`='2019-09-24 17:39:28' AND `age`=29 AND `gmt_create`='2019-09-24 17:39:28' AND `address`='陕西省西安市' AND `id`=3 LIMIT 1; #start 66374969 end 66375406 time 2019-10-21 15:38:49
DELETE FROM `test`.`employees` WHERE `name`='yangnan' AND `gmt_modified`='2019-09-24 17:39:52' AND `age`=22 AND `gmt_create`='2019-09-24 17:39:52' AND `address` IS NULL AND `id`=4 LIMIT 1; #start 66374969 end 66375406 time 2019-10-21 15:38:49
DELETE FROM `test`.`employees` WHERE `name`='yuzhiqiang' AND `gmt_modified`='2019-09-24 17:40:03' AND `age`=29 AND `gmt_create`='2019-09-24 17:40:03' AND `address` IS NULL AND `id`=5 LIMIT 1; #start 66374969 end 66375406 time 2019-10-21 15:38:49

• 解析出回滚 SQL，并检查回滚 SQL 是否正确

$ python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'xx' -dtest -temployees --start-file='mysql-bin.000020' --start-pos=66374969 --end-pos=66375406 -B > rollback.sql

INSERT INTO `test`.`employees`(`name`, `gmt_modified`, `age`, `gmt_create`, `address`, `id`) VALUES ('yuzhiqiang', '2019-09-24 17:40:03', 29, '2019-09-24 17:40:03', NULL, 5); #start 66374969 end 66375406 time 2019-10-21 15:38:49
INSERT INTO `test`.`employees`(`name`, `gmt_modified`, `age`, `gmt_create`, `address`, `id`) VALUES ('yangnan', '2019-09-24 17:39:52', 22, '2019-09-24 17:39:52', NULL, 4); #start 66374969 end 66375406 time 2019-10-21 15:38:49
INSERT INTO `test`.`employees`(`name`, `gmt_modified`, `age`, `gmt_create`, `address`, `id`) VALUES ('raohui', '2019-09-24 17:39:28', 29, '2019-09-24 17:39:28', '陕西省西安市', 3); #start 66374969 end 66375406 time 2019-10-21 15:38:49
INSERT INTO `test`.`employees`(`name`, `gmt_modified`, `age`, `gmt_create`, `address`, `id`) VALUES ('maofang', '2019-09-24 17:40:49', 32, '2019-09-24 17:32:00', '山东省青岛市', 2); #start 66374969 end 66375406 time 2019-10-21 15:38:49
INSERT INTO `test`.`employees`(`name`, `gmt_modified`, `age`, `gmt_create`, `address`, `id`) VALUES ('xujinliang', '2019-09-24 17:13:00', 32, '2019-09-24 17:13:00', '山东省青岛市', 1); #start 66374969 end 66375406 time 2019-10-21 15:38:49

Tips：回滚条数可以使用 %s/pattern/&/g 进行统计，pattern 是要搜索的关键字。

• 确认回滚 SQL 正确，执行回滚语句

$ mysql -h127.0.0.1 -P3306 -uroot -p'xx' < rollback.sql

mysql> select * from employees;
+----+------------+--------------------+-----+---------------------+---------------------+
| id | name       | address            | age | gmt_create          | gmt_modified        |
+----+------------+--------------------+-----+---------------------+---------------------+
|  1 | xujinliang | 山东省青岛市         |  32 | 2019-09-24 17:13:00 | 2019-09-24 17:13:00 |
|  2 | maofang    | 山东省青岛市         |  32 | 2019-09-24 17:32:00 | 2019-09-24 17:40:49 |
|  3 | raohui     | 陕西省西安市         |  29 | 2019-09-24 17:39:28 | 2019-09-24 17:39:28 |
|  4 | yangnan    | NULL               |  22 | 2019-09-24 17:39:52 | 2019-09-24 17:39:52 |
|  5 | yuzhiqiang | NULL               |  29 | 2019-09-24 17:40:03 | 2019-09-24 17:40:03 |
+----+------------+--------------------+-----+---------------------+---------------------+
5 rows in set (0.00 sec)

工具

# 连接配置
-h host; -P port; -u user; -p password

# 解析模式
-B, --flashback              # 生成回滚SQL
--start-file                 # 起始解析文件
--start-position/--start-pos # 起始解析位置
--stop-file/--end-file       # 终止解析文件
--stop-position/--end-pos    # 终止解析位置
--start-datetime             # 起始解析时间
--stop-datetime              # 终止解析时间，格式'%Y-%m-%d %H:%M:%S'
-d, --databases              # 只解析目标DB的SQL，多个库用空格隔开
-t, --tables                 # 只解析目标表的SQL，多张表用空格隔开
--only-dml                   # 只解析DML，忽略DDL
--sql-type                   # 只解析指定类型，支持INSERT, UPDATE, DELETE，多个类型用空格隔开

注意事项

• 还是先恢复再去面壁思过吧！
• 手工执行 delete、update 语句前，先执行 selete 语句。
• drop 或者 truncate 语句造成的误操作，通过备份来恢复。