乐趣区

关于java:手把手教你玩-MySQL-删库不跑路直接把-MySQL-的-binlog-玩溜

咱们经常听人说,只有你违心,MySQL 能够复原至半个月甚至一个月以内的任何一个状态。网上也有很多删库跑路的段子。。。

那么明天松哥想和大家来聊一聊 MySQL 中的 binlog,来手把手教大家如何利用 binlog 来复原 MySQL 中的数据,这样,当前要是不小心删库了,那也不必跑路了。

MySQL 中的日志比拟重要的有 binlog(归档日志)、redo log(重做日志)以及 undo log,那么跟咱们本文相干的次要是 binlog,另外两个日志松哥未来有空了再和大家具体介绍。

1. binlog

binlog 咱们中文个别称作归档日志,如果大家看过松哥之前发的 MySQL 主从搭建,应该对这个日志有印象,当咱们搭建 MySQL 主从的时候就离不开 binlog(传送门:MySQL8 主从复制踩坑指南)。

binlog 是 MySQL Server 层的日志,而不是存储引擎自带的日志,它记录了所有的 DDL 和 DML(不蕴含数据查问语句) 语句,而且是以事件模式记录,还蕴含语句所执行的耗费的工夫等,须要留神的是:

  • binlog 是一种逻辑日志,他里边所记录的是一条 SQL 语句的原始逻辑,例如给某一个字段 +1,留神这个区别于 redo log 的物理日志(在某个数据页上做了什么批改)。
  • binlog 文件写满后,会主动切换到下一个日志文件持续写,而不会笼罩以前的日志,这个也区别于 redo log,redo log 是循环写入的,即前面写入的可能会笼罩后面写入的。
  • 一般来说,咱们在配置 binlog 的时候,能够指定 binlog 文件的有效期,这样在到期后,日志文件会主动删除,这样防止占用较多存储空间。

依据 MySQL 官网文档的介绍,开启 binlog 之后,大略会有 1% 的性能损耗,不过这还是能够承受的,一般来说,binlog 有两个重要的应用场景:

  • MySQL 主从复制时:在主机上开启 binlog,主机将 binlog 同步给从机,从机通过 binlog 来同步数据,进而实现主机和从机的数据同步。
  • MySQL 数据恢复,通过应用 mysqlbinlog 工具再联合 binlog 文件,能够将数据恢复到过来的某一时刻。

2. 开启 binlog

为了演示不便,松哥这里在 Docker 中装置了 MySQL,咱们以此为例来开始明天的演示。如果小伙伴们还不懂 docker 的应用,能够在公众号后盾回复 docker,有松哥写的教程。

首先咱们在 docker 中装置好 MySQL,而后进入到容器中,通过如下命令能够查看 binlog 是否开启:

这个 OFF 就示意 binlog 是一个敞开状态,没有开启,接下来咱们来开启 binlog。

开启 binlog 次要是批改 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%'; 即可看到 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 操作命令。

  1. 查看所有 binlog 日志

通过如下形式咱们能够查看 binlog 日志列表:

show master logs;

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

  1. 查看 master 状态

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

这个时候能够看到最新的 binlog 日志文件名称以及最初一个操作事件的 Position 值(这个值有啥用,咱们前面会给大家具体介绍)。

  1. 刷新 binlog

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

由上图能够看到,咱们刷新日志之后,再通过 show master logs 去查看日志,发现日志文件曾经多了一个新产生的了,而后再通过 show master status 去查看最新的日志文件信息,发现也曾经变为 javaboy_logbin.000002

  1. 重置 binlog

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

  1. 查看 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 实现数据库的删库复原。好啦,感兴趣的小伙伴能够试试哦(别在生产库上试哦)~

退出移动版