MySQL binlog에서 데이터를 복구하는 방법을 분석해 보겠습니다.-MySQL 튜토리얼-php.cn

이 글은 binlog와 관련된 이슈를 주로 소개하는 mysql 관련 지식을 제공합니다. Binlog는 일반적으로 archive log라고 불리며, mysql에서 데이터를 복원하는 방법에 대해 살펴보겠습니다. . 모두가 도움이 됩니다.

MySQL binlog에서 데이터를 복구하는 방법을 분석해 보겠습니다.

추천 학습: mysql 학습 튜토리얼

우리는 사람들이 원하기만 하면 반달 또는 심지어 한 달 안에 MySQL을 어떤 상태로든 복원할 수 있다는 말을 자주 듣습니다. 인터넷에는 데이터베이스를 삭제하고 도망가는 농담도 많이 있습니다. . .

그래서 오늘 송 형제는 MySQL의 binlog에 대해 이야기하고 binlog를 사용하여 MySQL에서 데이터를 복원하는 방법을 단계별로 가르쳐주려고 합니다. 도망치려고.

MySQL에서 더 중요한 로그에는 binlog(아카이브 로그), redo 로그(redo 로그) 및 undo 로그가 있습니다. 따라서 우리 기사와 관련된 주요 로그는 binlog입니다. Songge가 무료로 제공되면 공유됩니다. 미래를 자세히 소개합니다.

1.binlog

binlog는 일반적으로 중국어로 아카이브 로그라고 합니다. 이전에 송 형제님이 게시한 MySQL 마스터-슬레이브 설정을 본 적이 있다면 MySQL 마스터-슬레이브를 구축할 때 이 로그를 본 적이 있을 것입니다. binlog 없이는 할 수 없습니다. (포털: MySQL8 마스터-슬레이브 복제 함정 가이드)

binlog는 스토리지 엔진과 함께 제공되는 로그가 아닌 MySQL Server 계층의 로그입니다. 모든 DDL 및 DML(데이터 쿼리 문 제외) 문을 기록하며 이벤트 형식으로 기록되며 소비도 포함됩니다. 주의할 점은

binlog는 일종의 논리적 로그이며, 여기에 기록되는 것은 특정 필드에 대한 +1과 같은 SQL 문의 원본 논리입니다. 이는 redo log의 물리적 로그(특정 데이터 페이지에서 어떤 수정이 이루어졌는지)와는 다르다는 것입니다.
binlog 파일이 가득 차면 자동으로 다음 로그 파일로 전환하여 이전 로그를 덮어쓰지 않고 계속 기록합니다. 이는 Redo 로그가 루프로 기록되는, 즉 나중에 기록되는 것과도 다릅니다. 이전에 덮어쓸 수 있습니다.
일반적으로 binlog를 구성할 때 binlog 파일의 유효 기간을 지정할 수 있으므로 만료 후 로그 파일이 자동으로 삭제되어 더 많은 저장 공간을 차지하지 않습니다.

공식 MySQL 문서에 따르면 binlog를 켠 후 약 1%의 성능 손실이 발생하지만 이는 여전히 허용 가능합니다. 일반적으로 binlog에는 두 가지 중요한 사용 시나리오가 있습니다.

MySQL 마스터-슬레이브 복제 시기: 호스트에서 binlog를 활성화하면 호스트는 binlog를 슬레이브에 동기화하고 슬레이브는 binlog를 통해 데이터를 동기화하여 호스트와 슬레이브 간의 데이터 동기화를 달성합니다.
MySQL 데이터 복구, binlog 파일과 결합된 mysqlbinlog 도구를 사용하면 과거 특정 시점으로 데이터를 복원할 수 있습니다.

2. binlog 켜기

데모의 편의를 위해 송 형제는 Docker에 MySQL을 설치한 것을 예로 들어 오늘의 데모를 시작하겠습니다. 친구들이 여전히 docker 사용법을 이해하지 못한다면 공식 계정 백그라운드에서 docker에 답글을 달 수 있습니다. 송 형제님이 작성한 튜토리얼이 있습니다.

먼저 docker에 MySQL을 설치한 후 컨테이너에 들어갑니다. 다음 명령을 실행하면 binlog가 켜져 있는지 확인할 수 있습니다.

이 OFF는 binlog가 닫히고 켜지지 않음을 의미합니다. 빈로그를 켜세요.

binlog를 열면 주로 컨테이너의 /etc/mysql/mysql.conf.d 디렉터리에 있는 MySQL 구성 파일 mysqld.cnf가 수정됩니다. /etc/mysql/mysql.conf.d 目录下。

针对该配置文件，我们做如下修改：

# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=javaboy_logbin

# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600

# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7

# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=javaboy_db

# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=javaboy_no_db

# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1

로그인 후 복사

各项配置的含义松哥已经在注视中说明了。截图如下：

配置完成后，执行如下命令重启 mysql 容器（mysql1 是我这里容器的名字）：

docker restart mysql1

로그인 후 복사

重启之后，再次执行 show variables like 'log_bin%';

이 구성 파일에 대해 다음을 만듭니다. 수정 :

show master logs;

로그인 후 복사

각 구성의 의미를 송형님께서 시청하시면서 설명해 주셨습니다. 스크린샷은 다음과 같습니다.

🎜

🎜🎜구성 후 완료되면 다음 명령을 실행하여 mysql 컨테이너를 다시 시작합니다(여기서 mysql1은 내 컨테이너의 이름입니다). 🎜

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

로그인 후 복사

🎜다시 시작한 후 show Variables like 'log_bin%';를 다시 실행하여 해당 binlog를 확인하세요. 켜져 있습니다. 🎜🎜🎜🎜🎜log_bin 변수 외에도 주목할 만한 두 가지 변수 이름이 있습니다. 🎜

log_bin_basename：这个是将来产生的 binlog 日志文件的名称前缀，换句话说，根据大家目前所看到的配置，将来产生的 binlog 日志文件名为 javaboy_logbin.xxx，这个文件中将会用来记录所有的 DDL 和 DML 语句事件。
log_bin_index：这个是 binlog 的索引文件，保存了所有 binlog 的目录，因为 binlog 可能会有多个。我们可以来查看一下现在的 javaboy_logbin.index 文件：

可以看到，目前只有一个 logbin 文件。

3. 常见 binlog 操作

接下来我们再来介绍几个常见的 binlog 操作命令。

查看所有 binlog 日志

通过如下方式我们可以查看 binlog 日志列表：

show master logs;

로그인 후 복사

可以看到，我这里目前只有一个日志文件，文件名为 javaboy_logbin.000001，File_size 表示这个文件占用的字节大小是 154。

查看 master 状态

这个命令我们在搭建 MySQL 主从的时候经常会用到，如下：

这个时候可以看到最新的 binlog 日志文件名称以及最后一个操作事件的 Position 值（这个值有啥用，我们后面会给大家详细介绍）。

刷新 binlog

正常来说，一个 binlog 写满之后，会自动切换到下一个 binlog 开始写，不过我们也可以执行一个 flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：

由上图可以看到，我们刷新日志之后，再通过 show master logs 去查看日志，发现日志文件已经多了一个新产生的了，然后再通过 show master status 去查看最新的日志文件信息，发现也已经变为 javaboy_logbin.000002。

重置 binlog

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

查看 binlog

由于 binlog 是二进制日志文件，所以要是直接打开，那肯定是看不了的：

没有看到任何有用的信息。

为了查看 binlog，MySQL 为我们提供了两个官方工具，我们一个一个来看，首先是 mysqlbinlog 命令，如下：

虽然看起来乱糟糟的，不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库，里边只是创建了一个名为 javaboy 的库，然后创建了一个名为 user 的表加了两条数据，其他什么事情都没做，所以创建库的脚本我们其实能够从纷杂的文件中找到。

产生的日志文件中有一个 end_log_pos 是日志文件的 pos 点，这个将来在数据恢复的时候有用。

不过这种查看方式不够人性化，我们说 binlog 是按照事件来记录日志的，所以如果我们能够按照事件的方式查看日志，就会好很多，我们再来看看如下一个命令：

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

로그인 후 복사

这个表示以事件的方式来查看 binlog，这里涉及到几个参数：

log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
offset：这是是偏移量，不指定默认就是 0。
row_count：查看多少行记录，不指定就是查看所有。

我们来看一个简单的例子：

show binlog events in 'javaboy_logbin.000001';

로그인 후 복사

这下就清晰多了，我们可以看到之前的所有操作，例如：

在 Pos 219-322 之间创建了一个库。
在 Pos 387-537 之间创建了一张表。
在 Pos 677-780 之间添加了一条记录。
…

4. 数据恢复实战

好啦，有了前面的基础知识准备，接下来松哥来给大家手把手演示一个删库/恢复的场景。

我先来说说我这个数据库目前的情况。

这是一个新安装的数据库，里边我新建了一个数据库名为 javaboy，javaboy 库中新建了一张表名为 user，user 中有两条记录，如下：

现在假设我们定期（每周三凌晨三点）对数据库进行备份。

现在凌晨三点了，数据库自动备份开始了，我们通过如下命令将数据库备份成 SQL 脚本，如下：

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

로그인 후 복사

这里有几个参数跟大家解释下：

-u、-p 这两个就不用说了。
–flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。
–lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，–lock-tables 分别为每个数据库锁定表，因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。
-B：这个表示指定导出的数据库名称，如果使用 --all-databases 或者 -A 代替 -B 表示导出所有的数据库。

以上命令执行完成后，会在 /root 目录下生成一个 javaboy.bak.sql 文件，该文件就是备份的 sql 文件了。

这是星期三凌晨三点发生的事情。

接下来到了星期四早上，来上班了，一顿操作后，往数据库中又添加了两条操作，如下：

接下来，小 X 今天跟领导吵架了很不爽，决定删除跑路：

领导发现了大惊，当即要求立马恢复数据。这时候该你表现了。

首先，我们有星期三凌晨的备份文件，先用那个文件进行数据恢复：

恢复之后，现在到星期三早上凌晨三点的数据有了。

从星期三早上凌晨三点到星期四的数据现在没了。

这个时候我们就要借助于 binlog 来恢复了。大家还记得，我们星期三凌晨三点执行备份的时候，用了一个参数叫做 --flush-logs，使用了该参数表示从备份那一刻起，新的 binlog 将产生在一个新的日志文件中，对于我们这里来说，新的 binlog 文件当然就是 javaboy_logbin.000002 了，我们去查看一下该文件：

show binlog events in 'javaboy_logbin.000002';

로그인 후 복사

我这里生成的该文件比较长，我截取其中一部分：

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

由于 javaboy_logbin.000002 文件是在星期三凌晨三点备份之后产生的新文件，因此这个文件从起始到 764 这个 Pos 之间的操作，就是星期三凌晨三点到删库之前的操作了。

那么我们来看下通过 binlog 来恢复数据的命令：

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p

로그인 후 복사

那么这里涉及到两个参数：

–stop-position=764 表示恢复到 764 这个 Pos，不指定的话就把按整个文件恢复了，如果按当前文件恢复的话，由于这个 binlog 文件中有删除数据库的语句，那么就会导致执行完该 binlog 之后，javaboy 库又被删除了。
–database=javaboy 表示恢复 javaboy 这个库。

另外还有一个我们这里没用到的参数叫做 --start-position，这个表示起始的 Pos，不指定的话表示从头开始数据恢复。

好啦，弄完之后，再来查看数据库：

数据恢复啦～

注意：所有操作之前，记得该备份就备份（防止你操作错了又回不去），松哥为了省事上面省略了一些备份操作。

5. 요약

좋습니다. 오늘의 기사에서는 주로 MySQL binlog 로그를 친구들과 공유하고 작은 사례를 사용하여 binlog를 통해 데이터베이스 삭제 및 복구를 수행하는 방법을 보여줍니다. 좋아요, 관심 있는 친구들은 시도해 볼 수 있습니다(프로덕션 라이브러리에서는 시도하지 마세요)~