写点什么

一文掌握 MySQL 中的二进制日志,MySQL 优化学习第 10 天

作者:梦想橡皮擦
  • 2022 年 5 月 14 日
  • 本文字数:1268 字

    阅读完需:约 4 分钟

⛳️ 铺垫

本篇博客我们学习一下 MySQL 中的二进制日志,该日志记录了所有对 MySQL 数据库的 CRUD 事件,包括增删改查和对表的系列操作记录,对于失败、回滚或未提交的操作不会记录。通过 MySQL 的二进制日志增量,可以实现 MySQL 的复制和备份。


学习过程中检索到有资料提及,二进制日志(binlog)只会增加 1% 的 MySQL 负载,所以可以一直开启。

⛳️ 开启二进制日志

通过如下命令验证是否开启:


show variables like 'log_bin%';
复制代码



各个参数的含义如下所示:


  • log_bin:是否开启 binlog 日志;

  • log_bin_basename:binlog 存放在位置

  • log_bin_index:设置 binlog 日志的索引文件的存放路径及索引文件名;

⛳️ 查看目前数据库的二进制文件

使用如下命令:


show binary logs;
复制代码


得到如下结果,二进制文件名以 mysql-bin.000xxx 命名。



还可以使用如下命令查看


show master logs;
复制代码

⛳️ 二进制日志删除

删除某个日志之前的所有二进制文件,使用如下命令


purge binary logs to 'mysql-bin.000014';
复制代码


得到结果如下所示:



也可以清除某个时间点之前的二进制日志


purge binary logs before '2022-03-22 12:12:00';
复制代码


清除所有二进制日志文件


reset master;
复制代码

⛳️ 查看二进制日志

使用如下命令可以查阅日志


show binlog events in 'mysql-bin.000014' 
复制代码


该命令标准格式如下所示:


show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
复制代码


参数解析:


  • IN 'log_name':要查询的 binlog 文件名(默认第一个二进制日志);

  • FROM pos:指定从哪个 pos 起始点开始查起(默认文件起始点);

  • LIMIT [offset,]:偏移量(默认 0);

  • row_count:查询总条数(默认所有行)。


如果执行的时候出现如下错误


Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
复制代码


该原因是由于 pos 设置不正确,工具就会报 Wrong offset or I/O error 的错误,在查看日志内容之前一定要首先定位好。


查看二进制日志文件,建议使用 mysqlbinlog 工具,在 bin 目录可以查找到。


查询日志过程中如果出现如下错误


mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
复制代码


两个方法可以解决这个问题


  1. 在 MySQL 的配置中将 default-character-set=utf8 修改为 character-set-server = utf8 ,然后重启 MySQL 服务。

  2. mysqlbinlog --no-defaults mysql-bin.xxxx 命令打开。


第二种方法添加的参数是 --no-default ,表示不读取任何选项文件。



其中的语句被 Base64 加密了,可以增加如下参数


--base64-output=decode-rows –v
复制代码


此时完整的命令如下所示:


mysqlbinlog.exe --no-defaults --base64-output=decode-rows -v  D:\phpstudy_pro\Extensions\MySQL5.7.26\.000001
复制代码

⛳️ binlog 恢复数据的 2 种情况

二进制文件可以用于恢复文件,这部分内容你可以检索一下相关资料进行学习。


数据库丢失或者 drop


  1. 恢复系统自动备份

  2. 用 binlog 恢复系统备份时间到此刻的的数据


update 与 delete 误操作


  1. 恢复系统自动备份

  2. 从 binlog 日志中删除误操作的语句,然后执行 binlog 恢复其他的数据


但是有一点需要注意,如果删除了部分数据,但是没有提前备份数据库,或者二进制日志没有记录数据添加的语句,是无法还原的。


发布于: 刚刚阅读数: 2
用户头像

爬虫 100 例作者,蓝桥签约作者,博客专家 2021.02.06 加入

6 年产品经理+教学经验,3 年互联网项目管理经验; 互联网资深爱好者; 沉迷各种技术无法自拔,导致年龄被困在 25 岁; CSDN 爬虫 100 例作者。 个人公众号“梦想橡皮擦”。

评论

发布
暂无评论
一文掌握MySQL中的二进制日志,MySQL 优化学习第10天_5月月更_梦想橡皮擦_InfoQ写作社区