MySQL 通过binlog恢复数据

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

  • 使用 delete 误删行
  • 使用 drop tabletruncate 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 的格式,可选值有 statementrowmixed

  • 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.

访客数 : | 访问量 :