Binlog数据恢复实战，删库不跑路-51CTO.COM

一、binLog恢复数据

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：

MySQL主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。

MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

1.开启 binlog

我将使用docker演示，配置和配置位置都是一样的，没啥区别。

(1)配置/etc/mysql/mysql.conf.d/mysqld.cnf ，编辑以下参数：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

在其中，server-id 是服务器的唯一标识符，log_bin 是binlog文件的路径和名称。你可以根据需要更改这些值。

(2)保存更改后的配置文件，并重新启动MySQL服务，使更改生效。

(3)确认binlog已经成功开启，可以使用以下命令登录MySQL并执行：

SHOW MASTER STATUS;

如果输出类似如下信息，则表示binlog已经成功开启：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107      | test         |                  |
+------------------+----------+--------------+------------

或者以下命令：可以看到log_bin的状态是ON。

show variables like 'log_bin%';

mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.00 sec)

2.编辑配置

#这个参数用来启用binlog，并指定了binlog的文件名前缀。在这个例子中，binlog文件会以 dx_logbin 开头命名。binlog文件记录了数据库的所有更改操作，包括增删改等。
log-bin=adx_logbin
#这个参数指定了单个binlog文件的最大大小，单位是字节。当binlog文件大小达到这个值时，MySQL会自动创建一个新的binlog文件来继续记录日志。
max_binlog_size=104857600

#这个参数指定了binlog文件的过期时间，单位是天。超过指定天数的binlog文件会被自动删除。这个设置有助于控制磁盘空间的使用。
expire_logs_days=7

#这个参数用来指定需要记录binlog的数据库名称。在这个例子中，adx_db 是需要记录binlog的数据库，
#binlog-do-db=adx_db
#这个参数用来指定不需要记录binlog的数据库名称。在这个例子中，javaboy_no_db 是不需要记录binlog的数据库，但是由于前面有#注释了，所以实际上是被注释掉了，不会生效。
#binlog-ignore-db=javaboy_no_db

#这个参数用来控制binlog的写入方式。当设置为0时，表示不强制立即将binlog日志写入磁盘。这样会提高性能，但在数据库宕机时可能会丢失一部分数据。
sync_binlog=0

#这个参数指定了MySQL服务器的唯一标识符。在复制和多主模式下，每个服务器都需要有一个唯一的ID来标识自己。
server-id=1

配置完成后，执行如下命令重启 mysql 容器（mysql是你的容器名称）。

docker restart mysql

在看一下是否开启binlog。

图片

其中我们还要关注两个属性。

log_bin_basename: /var/lib/mysql/adx_logbin

这个配置指定了二进制日志文件的基本名字为adx_logbin，不包括文件扩展名。实际的二进制日志文件会以这个基本名字开头，后面紧跟一个数字标识，再加上文件扩展名（通常是.log）。例如，可能生成的二进制日志文件包括adx_logbin.000001、adx_logbin.000002等。
这个设置意味着生成的二进制日志文件将以adx_logbin作为基本名字。
log_bin_index: /var/lib/mysql/adx_logbin.index

这个配置指定了二进制日志索引文件的名字为二进制日志索引文件记录了所有的二进制日志文件名字及其对应的位置信息，通常以.index作为文件扩展名。通过这个索引文件，MySQL可以快速地定位到各个二进制日志文件，并进行相应的操作，比如数据库恢复、复制等。

查看一下现在的 adx_logbin.index 文件：

图片

3.常用binlog相关命令

3.1 查看所有 binlog 日志列表

show master logs;

图片

3.2 查看 master 状态

用于查看当前主服务器的二进制日志（binlog）信息。执行这个命令可以获取以下信息，File：当前正在写入的二进制日志文件名，Position：在当前二进制日志文件中的位置，即已经写入的字节数。

show master status;

图片

3.3 刷新 binlog

用于关闭当前的二进制日志文件，将当前的日志文件重命名为一个旧的日志文件，例如通过添加一个序号或时间戳。并创建一个新的空的二进制日志文件，用于接收后续的二进制日志事件。

flush logs

这个命令在进行数据库备份时特别有用，因为它可以确保备份操作可以在一个一致的时间点开始，并且不会受到正在写入的二进制日志的影响。另外，当你希望重新开始二进制日志的记录时，也可以使用这个命令来关闭当前的日志文件并开启一个新的日志文件。

图片

需要注意的是，执行 FLUSH LOGS; 会导致当前的二进制日志文件被关闭，这可能会影响到主从复制的正常运行。因此，在执行这个命令之前，需要谨慎考虑是否会对数据库的其他操作产生影响。

3.4 重置 binlog

reset master

reset master 可以重置 binlog 日志文件，让日志重新从 000001 开始记录，不过如果当前主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的错误）。

执行 RESET MASTER; 可以确保清除所有的旧的日志文件，防止日志文件过多占用磁盘空间，并从头开始记录新的二进制日志事件。

图片

3.5 查看 binlog

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

该命令用于显示指定二进制日志文件中的二进制日志事件。这个命令可以提供对二进制日志中存储的操作和更改的详细信息。

下面是各参数的含义：

IN 'log_name': 可选参数，指定要查看的特定二进制日志文件名。

FROM pos: 可选参数，指定从日志文件中的特定位置开始显示日志事件。

LIMIT [offset,] row_count: 可选参数，限制要显示的日志事件的数量，并可以设置偏移量。

show binlog events in 'adx_logbin.000001';

图片

通过这个工具就能查看你执行过什么sql，例如327行我执行了创建库操作，586行我执行了创建表操作。

4.基于binLog恢复数据

通常数据库会做定时备份，假设每天凌晨1:00点定时备份全量数据库，如果第二天数据丢失了，可以先通过备份，先将数据恢复到当天的凌晨1:00的数据，再基于binlog恢复凌晨一点到数据丢失的那一刻的数据，这样就完全找回数据了。

备份命令：

mysqldump -uroot -p --flush-logs --lock-tables -B student2>/root/student2.bak.sql

mysqldump: 这是用于备份数据库的命令。
-uroot: 表示使用 root 用户身份连接到数据库进行备份。
-p: 是一个选项，表示在输入密码之前会提示用户输入密码。
--flush-logs: 这个选项表示在备份完成后将刷新日志文件，确保备份过程中的日志都被记录下来。
--lock-tables: 这个选项表示在备份时对数据库表进行锁定，以确保备份的一致性。
-B student2: 表示备份名为 student2 的数据库。
> /root/student2.sql: 这部分表示将备份内容导出到 /root 目录下的 student2.sql 文件中。

图片

可以通过cat命令查看导出的sql，老铁没毛病。

图片

假设我现在删除了student2这张表。

图片

如何恢复？先查询最新的binlog（最后一个binlog）。

图片

show binlog events in 'adx_logbin.000002';

图片

可以看到，在 1403-1507 这个 Pos 中发生了删库跑路事件，那么我们只需要回放该文件将数据恢复到 1403 这个位置即可。

由于 adx_logbin.000002 文件是在当前凌晨1:00备份之后产生的新文件，因此这个文件从起始到 1403 这个 Pos 之间的操作，就是凌晨1:00到删库之前的操作了。

那么我们来看下通过 binlog 来恢复数据的命令：（没有这个student2库的话先手动建立一个）

mysqlbinlog /var/lib/mysql/adx_logbin.000002 --stop-positinotallow=1403 --database=student2 | mysql -uroot -p

命令解释：

mysqlbinlog: 是用于解析 MySQL 二进制日志文件的工具。通过这个命令，你可以查看和分析二进制日志中的内容。

/var/lib/mysql/adx_logbin.000002: 这是指定的二进制日志文件路径，即要解析的二进制日志文件。

--stop-positinotallow=1403: 这个选项指定了解析二进制日志文件时要停止的位置。在这种情况下，命令会解析从文件开头到指定位置（1403）之间的内容。

--database=student2: 这个选项指定了只解析属于数据库 'student2' 的相关操作。这意味着命令只会处理涉及 'student2' 数据库的内容。

|: 这是管道符号，用于将 mysqlbinlog 命令的输出传递给后面的 mysql 命令。

mysql -uroot -p: 这是执行实际恢复操作的部分。它使用 mysql 命令以 root 用户身份连接到 MySQL 数据库，并执行从 mysqlbinlog 命令得到的结果。
mysqlbinlog: 是用于解析 MySQL 二进制日志文件的工具。通过这个命令，你可以查看和分析二进制日志中的内容。

/var/lib/mysql/adx_logbin.000002: 这是指定的二进制日志文件路径，即要解析的二进制日志文件。

--stop-positinotallow=1403: 这个选项指定了解析二进制日志文件时要停止的位置。在这种情况下，命令会解析从文件开头到指定位置（1403）之间的内容。

--database=student2: 这个选项指定了只解析属于数据库 'student2' 的相关操作。这意味着命令只会处理涉及 'student2' 数据库的内容。

|: 这是管道符号，用于将 mysqlbinlog 命令的输出传递给后面的 mysql 命令。

mysql -uroot -p: 这是执行实际恢复操作的部分。它使用 mysql 命令以 root 用户身份连接到 MySQL 数据库，并执行从 mysqlbinlog 命令得到的结果。

图片