>데이터 베이스 >MySQL 튜토리얼 >MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

WBOY
WBOY앞으로
2022-01-12 17:35:542464검색

이 기사에서는 MySQL 데이터베이스 삭제 및 binlog에 대한 지식을 제공합니다. MySQL에서 더 중요한 로그에는 binlog, redo 로그 및 undo 로그가 포함됩니다. 따라서 이 기사와 관련된 주요 내용은 binlog입니다.

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

MySQL에서 더 중요한 로그는 binlog(아카이브 로그), redo 로그(redo 로그), undo 로그이므로 우리 기사와 관련된 주요 로그는 binlog입니다.

1.binlog

binlog는 일반적으로 중국어로 아카이브 로그라고 합니다. 이전에 송 형제가 게시한 MySQL 마스터-슬레이브 설정을 본 적이 있다면 MySQL을 빌드할 때 이 로그를 본 적이 있을 것입니다. 마스터-슬레이브, binlog와 분리 불가능.

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

  • binlog는 일종의 논리적 로그입니다. 여기에 기록되는 것은 특정 필드에 대한 +1과 같은 SQL 문의 원래 논리입니다. 이는 REDO 로그의 물리적 로그(특정 데이터 페이지에서 어떤 수정이 이루어졌는지)와는 다르다는 점에 유의하세요.

  • binlog 파일이 가득 차면 이전 로그를 덮어쓰지 않고 계속 쓰기 위해 자동으로 다음 로그 파일로 전환됩니다. 이는 Redo 로그가 루프로 작성되는 것과도 다릅니다. 나중에 쓸 수 있습니다. 이전에 쓴 것을 덮어씁니다.

  • 일반적으로 binlog를 구성할 때 binlog 파일의 유효 기간을 지정할 수 있으므로 만료 후 로그 파일이 자동으로 삭제되어 더 많은 저장 공간을 차지하지 않습니다.

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

  • MySQL Main에서 복사할 때 슬레이브: 호스트에서 binlog를 활성화하면 호스트는 binlog를 슬레이브에 동기화하고 슬레이브는 binlog를 통해 데이터를 동기화하여 호스트와 슬레이브 간의 데이터 동기화를 달성합니다.

  • MySQL 데이터 복구는 binlog 파일과 결합된 mysqlbinlog 도구를 사용하여 과거 특정 시점으로 데이터를 복원할 수 있습니다.

2. binlog 켜기

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

이 구성 파일에 대해 다음과 같이 수정했습니다.

# 这个参数表示启用 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 binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

docker restart mysql1

다시 시작한 후 'log_bin%'와 같은 show 변수를 실행합니다. ; 다시 binlog가 켜져 있는지 확인합니다.

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

log_bin 변수 외에도 주목할 만한 두 가지 변수 이름이 있습니다.

  • log_bin_basename: 이는 향후 생성되는 binlog 로그 파일의 이름 접두사입니다. 지금까지 살펴본 내용에 따르면 향후 생성되는 binlog 로그 파일의 이름은 javaboy_logbin.xxx로 지정됩니다. 이 파일은 모든 DDL 및 DML 문 이벤트를 기록하는 데 사용됩니다.

  • log_bin_index: binlog가 여러 개 있을 수 있으므로 모든 binlog의 디렉터리를 저장하는 binlog 인덱스 파일입니다. 현재 javaboy_logbin.index 파일을 살펴볼 수 있습니다.

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

보시다시피 현재는 하나의 logbin 파일만 있습니다.

3. 일반적인 binlog 작업

다음으로 몇 가지 일반적인 binlog 작업 명령을 소개하겠습니다.

모든 binlog 로그 보기

다음 방법을 통해 binlog 로그 목록을 볼 수 있습니다.

show master logs;

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

查看 master 状态

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

刷新 binlog

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

重置 binlog

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

查看 binlog

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

虽然看起来乱糟糟的,不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库,里边只是创建了一个名为 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';

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

  • 在 Pos 219-322 之间创建了一个库。

  • 在 Pos 387-537 之间创建了一张表。

  • 在 Pos 677-780 之间添加了一条记录。

4. 数据恢复实战

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

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

  • -u、-p 这两个就不用说了。

  • --flush-logs:这个表示在导出之前先刷新 binlog,刷新 binlog 之后将会产生新的 binlog 文件,后续的操作都存在新的 binlog 中。

  • --lock-tables:这个表示开始导出前,锁定所有表。需要注意的是当导出多个数据库时,--lock-tables 分别为每个数据库锁定表,因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性,不同数据库表的导出状态可以完全不同。

  • -B:这个表示指定导出的数据库名称,如果使用 --all-databases 或者 -A 代替 -B 表示导出所有的数据库。

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

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

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

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

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

show binlog events in 'javaboy_logbin.000002';

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

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

可以看到,在 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,不指定的话表示从头开始数据恢复。

好啦,弄完之后,再来查看数据库:

MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.

数据恢复啦~

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

5. 小结

好啦,今天这篇文章主要是和小伙伴们分享了 MySQL 的 binlog 日志,并通过一个小案例来演示如何通过 binlog 实现数据库的删库恢复。好啦,感兴趣的小伙伴可以试试哦(别在生产库上试哦)~

推荐学习:mysql视频教程

위 내용은 MySQL binlog를 사용하는 방법을 단계별로 가르쳐주세요.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 juejin.im에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제