MySQL 通过binlog恢复数据

必备技能 阅读量

日常开发，运维中，经常会出现误删数据的情况。误删数据的类型大致可分为以下几类：

  • 使用 delete 误删行
  • 使用 drop table 或 truncate table 误删表
  • 使用 drop database 语句误删数据库
  • 使用 rm 命令误删整个 MySQL 实例。

本文目的

通过了解 binlog 日志的相关配置，简单掌握通过 binlog 对数据库进行数据恢复操作；

mysql 日志文件

mysql 主要有以下几种日志：

  • 错误日志：记录 mysql 运行过程中的错误信息
  • 一般查询日志：记录 mysql 正在运行的语句，包括查询、修改、更新等的每条 sql
  • 慢查询日志：记录查询比较耗时的 SQL 语句
  • binlog 日志：记录数据修改记录，包括创建表、数据更新等

这些日志均需要在 my.cnf 文件进行配置，如果不知道 mysql 的配置文件路径，可以使用 mysql 命令进行查找，

1
mysql --verbose --help | grep -A 1 'Default options’ #该命令会罗列出my.cnf顺序查找的路径。

例如:

1
2
3
[WWW] mysql --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /Applications/MAMP/conf/my.cnf ~/.my.cnf

binlog 日志

binlog 就是 binary log，二进制日志文件，记录所有数据库更新语句，包括表更新和记录更新，即数据操纵语言 (DML)，binlog 主要用于数据恢复和配置主从复制等；

  • 数据恢复：当数据库误删或者发生不可描述的事情时，可以通过 binlog 恢复到某个时间点的数据。
  • 主从复制：当有数据库更新之后，主库通过 binlog 记录并通知从库进行更新，从而保证主从数据库数据一致；

mysql 按照功能分为服务层模块和存储引擎层模块，服务层负责客户端连接、SQL 语句处理优化等操作，存储引擎层负责数据的存储和查询；

binlog 属于服务层模块的日志，即引擎无关性，所有数据引擎的数据更改都会记录 binlog 日志。

当数据库发生崩溃时，如果使用 InnoDB 引擎，binlog 日志还可以检验 InnoDB 的 redo 日志的 commit 情况。

binlog 日志开启

日志开启方式

  1. 添加配置
1
2
3
log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index
  1. 仅仅设置log-bin参数
1
log-bin=/path/bin-log

当开启 binlog 日志之后，mysql 会创建一个 log_bin_index 指定的 .index 文件和多个二进制日志文件，index 中按顺序记录了 mysql 使用的所有 binlog 文件。binlog 日志则会以指定的名称 (或默认值) 加自增的数字作为后缀，ex：bin-log.000001，当发生下述三种情况时，binlog 日志便会进行重建

文件大小达到 max_binlog_size 参数的值
执行 flush logs 命令
重启 mysql 服务

实例:

  • 设置log-bin参数
1
2
log-bin = /Users/caoxl/WWW/mysqlbinlog/mysql-binlog
binlog_format = row
  • 查看log-bin是否开启
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.20 sec)
 
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

binlog日志格式

通过参数 binlog_format 参数的值，可以设置 binlog 的格式，可选值有 statement、row、mixed

  • statement 格式：记录数据库执行的原始 SQL 语句
  • row 格式：记录具体的行的修改，这个为目前默认值
  • mixed 格式：因为上边两种格式各有优缺点，所以就出现了 mixed 格式

binlog 日志查看工具：mysqlbinlog

因为 binlog 是二进制文件，不能像其他文件一样，直接打开查看。但 mysql 提供了 binlog 查看工具 mysqlbinlog，可以解析二进制文件。当然不同格式的日志解析结果是不一样的；

  1. statement 格式日志，执行 mysqlbinlog /path/bin-log.000001，可以直接看到原始执行的 SQL 语句
  2. row 格式日志，则可读性没有那么好，但仍可通过参数使文档更加可读 mysqlbinlog -v /path/bin-log.000001

mysqlbinlog 两对非常重要的参数

  1. --start-datetime & --stop-datetime 解析某一个时间段内的 binlog；
  2. --start-position & --stop-position 解析在两个 position 之间的 binlog；

使用 binlog 恢复数据

使用 binlog 恢复数据，本质上就是通过 binlog 找到所有 DML 操作，去掉错误的 SQL 语句，然后重走一遍长征路，就可以将数据恢复；

通过 MySQL Cli 查询 BINLOG 信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 查询 BINLOG 格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

# 查询当前数据库中 BINLOG 名称及大小
mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysql-binlog.000001 |       177 |
| mysql-binlog.000002 |       177 |
| mysql-binlog.000003 |      2120 |
| mysql-binlog.000004 |       154 |
+---------------------+-----------+
4 rows in set (0.00 sec)

# 查看 master 正在写入的 BINLOG 信息
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-binlog.000004
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

# 通过 offset 查看 BINLOG 信息
mysql> show BINLOG events in 'mysql-binlog.000004' limit 1,  10;
+---------------------+-----+----------------+-----------+-------------+------+
| Log_name            | Pos | Event_type     | Server_id | End_log_pos | Info |
+---------------------+-----+----------------+-----------+-------------+------+
| mysql-binlog.000004 | 123 | Previous_gtids |         1 |         154 |      |
+---------------------+-----+----------------+-----------+-------------+------+
1 row in set (0.00 sec)

# 通过 position 查看 binlog 信息
mysql> show BINLOG events in 'mysql-binlog.000004' from 1 limit 10;
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name            | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-binlog.000004 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-binlog.000004 | 123 | Previous_gtids |         1 |         154 |                                       |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

通过 mysqlbinlog 查询 BINLOG 信息

在介绍 mysqlbinlog 工具使用前，先来看下 BINLOG 文件的内容：

1
2
# 查询 BINLOG 的信息
mysqlbinlog --no-defaults mysql-binlog.000004 | less  
1
2
# at 219
#210325 15:43:01 server id 1  end_log_pos 293 CRC32 0x1ccf5f28 	Query	thread_id=149	exec_time=0	error_code=0
  • at 表示 offset 或者说事件开始的起始位置
  • 210325 15:43:01 server id 1 表示 server 1 开始执行事件的日期
  • end_log_pos 293 表示事件的结束位置 + 1，或者说是下一个事件的起始位置。
  • exec_time 表示在 master 上花费的时间，在 salve 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。
  • error_code=0 表示没有错误发生。

导出 BINLOG 日志，用于分析和排查 sql 语句：

1
2
3
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2021-03-25 14:00:00" \
 mysql-binlog.000004 > /path/binlog_raw.sql

数据实操

恢复的大致流程如下:

  1. 创建数据库和表，并插入数据。
  2. 误删一条数据
  3. 继续插入数据。
  4. 误删表。
  5. 最后将原来以及之后插入的数据进行恢复。

准备数据

  • 准备数据库，表及数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 创建临时数据库
CREATE DATABASE IF NOT EXISTS test_binlog \
default charset utf8 COLLATE utf8_general_ci; 

# 创建临时表
CREATE TABLE `sync_test` (`id` int(11) \
NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL,  \
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加数据
insert into sync_test (id, name) values (null, 'xiaoa');
insert into sync_test (id, name) values (null, 'xiaob');
insert into sync_test (id, name) values (null, 'xiaoc');

# 查看添加的数据
select * from sync_test;

删除表或者数据

  • 误删操作
1
2
3
4
5
6
7
8
9
10
# 删除 name=xiaoc 的数据
delete from sync_test where id=3;

# 插入几条数据
insert into sync_test (id, name) values (null, 'xiaod');
insert into sync_test (id, name) values (null, 'xiaoe');
insert into sync_test (id, name) values (null, 'xiaof');

# 删除表
DROP TABLE sync_test;

数据的恢复

在执行数据恢复前，如果操作的是生产环境，会有如下的建议：

  • 使用 flush logs 命令，替换当前主库中正在使用的 binlog 文件，好处如下：
    • 可将误删操作，定位在一个 BINLOG 文件中，便于之后的数据分析和恢复。
    • 避免操作正在被使用的 BINLOG 文件，防止发生意外情况。
  • 数据的恢复不要在生产库中执行，先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。

通常来说，恢复主要有两个步骤：

  1. 在临时库中，恢复定期执行的全量备份数据。
  2. 然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据。

使用 BINLOG 做数据恢复前

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 查看正在使用的 Binlog 文件
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-binlog.000004
         Position: 3325
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
# 显示结果是: mysql-binlog.000004

# 执行 flush logs 操作，生成新的 BINLOG
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

# 查看正在使用的 Binlog 文件
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-binlog.000005
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
# 显示结果是: mysql-binlog.000005

确定恢复数据的步骤

这里主要是有两条误删的操作，数据行的误删和表的误删。有两种方式进行恢复。

  • 方式一：首先恢复到删除表操作之前的位置，然后再单独恢复误删的数据行。
  • 方式二：首先恢复到误删数据行的之前的位置，然后跳过误删事件再恢复数据表操作之前的位置。

这里采用方式一的方案进行演示，由于是演示，就不额外找一个临时库进行全量恢复了，直接进行操作。

查询创建表的事件位置和删除表的事件位置

1
2
3
4
5
6
7
8
9
# 根据时间确定位置信息
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2021-03-25 14:00:00" \
 --database test_binlog  mysql-binlog.000005 | less

# 导出SQL,方便分析
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2021-03-25 14:00:00" \
 mysql-binlog.000001 > /your-path/binlog_raw.sql

创建表的开始位置:

创建表的开始位置

创建表的结束位置:

创建表的结束位置

插入 name='xiaoc' 的位置：

插入 `name='xiaoc'` 的位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 根据位置导出 SQL 文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "398" --stop-position "2699" \
 --database test_binlog  mysql-binlog.000005 \
 > /your-path/test_binlog_step1.sql
 
 
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "1279" --stop-position "1563" \
 --database test_binlog  mysql-binlog.000005 \
 > /your-path/test_binlog_step2.sql
 

# 使用 mysql 进行恢复
mysql -u root -p < /your-path//test_binlog_step1.sql
mysql -u root -p < /your-path//test_binlog_step2.sql

MySQL 5.7 中无论是否打开 GTID 的配置，在每次事务开启时，都首先会出 GTID 的一个事务，用于并行复制。所以在确定导出开始事务位置时，要算上这个事件。
在使用 –stop-position 导出时，会导出在指定位置的前一个事件，所以这里要推后一个事务。
对于 DML 的语句，主要结束位置要算上 COMMIT 的位置。

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :