作者信息

长期闲置

长风破浪会有时,直挂云帆济沧海。

最近文章
mysql5.7怎么修改root密码224
mysql怎么修改definer510
mysql怎么转换为sqlite660
视频教程分类
推荐视频教程
  • 千万级数据并发解决方案(理论+实战)千万级数据并发解决方案(理论+实战)
  • Laravel基础与实战Laravel基础与实战
  • UNI-APP开发(仿饿了么)UNI-APP开发(仿饿了么)
  • ThinkPHP6开发CMS系统ThinkPHP6开发CMS系统
  • 首页 >数据库 >mysql教程 > 正文

    手把手教你把MySQL的binlog玩溜

    转载2022-01-12 17:35:54778 关注公众号:每天精选资源文章推送
    本篇文章给大家带来了MySQL删库和binlog的相关知识,MySQL 中的日志比较重要的有 binlog、redo log以及undo log,那么跟我们本文相关的主要是binlog,希望对大家有帮助。

    MySQL 中的日志比较重要的有 binlog(归档日志)、redo log(重做日志)以及 undo log,那么跟我们本文相关的主要是 binlog。

    1. binlog

    binlog 我们中文一般称作归档日志,如果大家看过松哥之前发的 MySQL 主从搭建,应该对这个日志有印象,当我们搭建 MySQL 主从的时候就离不开 binlog。

    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 是否开启:

    01.png

    这个 OFF 就表示 binlog 是一个关闭状态,没有开启,接下来我们来开启 binlog。

    开启 binlog 主要是修改 MySQL 的配置文件 mysqld.cnf,该文件在容器的 /etc/mysql/mysql.conf.d 目录下。

    02.png

    针对该配置文件,我们做如下修改:

    # 这个参数表示启用 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

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

    03.png

    配置完成后,执行如下命令重启 mysql 容器(mysql1 是我这里容器的名字):

    docker restart mysql1

    重启之后,再次执行 show variables like 'log_bin%'; 即可看到 binlog 已经开启了。

    04.png

    这里除了 log_bin 变量外,还有两个变量名也值得我们关注:

    • log_bin_basename:这个是将来产生的 binlog 日志文件的名称前缀,换句话说,根据大家目前所看到的配置,将来产生的 binlog 日志文件名为 javaboy_logbin.xxx,这个文件中将会用来记录所有的 DDL 和 DML 语句事件。

    • log_bin_index:这个是 binlog 的索引文件,保存了所有 binlog 的目录,因为 binlog 可能会有多个。我们可以来查看一下现在的 javaboy_logbin.index 文件:

    05.png

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

    3. 常见 binlog 操作

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

    查看所有 binlog 日志

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

    show master logs;

    06.png

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

    查看 master 状态

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

    07.png

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

    刷新 binlog

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

    08.png

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

    重置 binlog

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

    09.png

    查看 binlog

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

    10.png

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

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

    11.png

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

    12.png

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

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

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

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

    4. 数据恢复实战

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

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

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

    13.png

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

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

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

    14.png

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

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

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

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

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

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

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

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

    15.png

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

    16.png

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

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

    17.png

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

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

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

    show binlog events in 'javaboy_logbin.000002';

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

    18.png

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

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

    19.png

    数据恢复啦~

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

    5. 小结

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

    推荐学习:mysql视频教程

    以上就是手把手教你把MySQL的binlog玩溜的详细内容,更多请关注php中文网其它相关文章!

    高并发千万级数据库系统解决方案

    声明:本文转载于:掘金,如有侵犯,请联系admin@php.cn删除

  • 相关标签:mysql
  • 相关文章

    相关视频


    专题推荐