[大数据] MySQL日志篇，MySQL日志之binlog日志，binlog日志详解

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL日志篇，MySQL日志之binlog日志，binlog日志详解 -> 正文阅读

[大数据]MySQL日志篇，MySQL日志之binlog日志，binlog日志详解

视频地址 https://www.bilibili.com/video/BV1oT4y1o7TW

一、描述

binlog 二进制日志文件，这个文件记录了MySQL所有的DML操作。通过binlog日志我们可以做数据恢复，增量备份，主主复制和主从复制等等。

二、常用操作

2-1、docker里面无法使用 vim、常用命令问题解决

https://blog.csdn.net/Tomwildboar/article/details/120710690

https://blog.csdn.net/KwaiSZ/article/details/106937983

2-2、开启 binlog

查看是否开启binlog日志 show variables like 'log_%';

在这里插入图片描述
在 my.cnf 里面加上配置如下配置，重启mysql。

一般来说 my.cnf 在：/etc/mysql/my.cnf 或 /etc/my.cnf

# binlog存储的位置
log-bin=/var/lib/mysql/mysql-bin
# 日志过期时间
expire_logs_days=30
# 不加这个启动会报错
server-id=123454

如果你启动的时候因为配置文件修改错了，导致无法启动，可以参考这个解决： Mac下docker里面的MySQL无法启动,Mac进入docker（修改配置文件导致无法启动）

查看binlog其它的配置文件 show cc like 'binlog%';
在这里插入图片描述

2-3、binlog查看乱码的问题

在这里插入图片描述
上面框起来的就是binlog日志（你可以先对表进行一些增删改操作），但是我们使用 vim打开发现全是乱码

其中标记的部分并非是乱码，而是经由 base64 编码之后的结果，可以在通过 mysqlbinlog 查看 binlog 日志时添加参数进行解码

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000007

在这里插入图片描述

2-4、binlog 编码格式

我们可以使用 show variables like 'binlog_format'; 来查看默认的编码格式

注：不同版本的MySQL，binlog默认的编码格式可能不同
在这里插入图片描述
binlog有三种编码格式分别是 row ? statement ? mixed

2-4-1、statement 基于SQL语句的复制(statement-based replication, SBR)

每一条会修改数据的sql语句都会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。

在配置文件里面加入配置 binlog_format=statement，然后重启服务。

执行下面的语句

CREATE TABLE `my_test`.`xdx_test`  (
  `name` varchar(255) NOT NULL,
  `age` int(0) NOT NULL,
  `birthday` datetime NOT NULL
)

insert into xdx_test (name,age,birthday) values ('xdx',18, now());

然后我们在打开最新的binlog日志，可以在里面找到上面的语句。

但就如同上面的 insert 语句，我使用了 now()，这个函数，如果用这个binlog语句去进行备份、同步那么时间字段就对不上了。

2-4-2、row 基于行的复制(row-based replication, RBR)

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发或 now() 无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

修改配置文件为 binlog_format=row ，然后重启服务器即可开启。

2-4-3、mixed 混合模式复制(mixed-based replication, MBR)

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

修改配置文件为 binlog_format=mixed ，然后重启服务器即可开启。

2-5、通过binlog恢复数据

2-5-1、准备数据

在操作之前，我们先执行 flush logs; 开启一个新的 binlog日志，方便后面处理。

建表

CREATE TABLE `xdx_test` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  `birthday` datetime NOT NULL COMMENT '生日' ,
   PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

添加数据

insert into xdx_test (name, age, birthday) values ('111',111,now());
insert into xdx_test (name, age, birthday) values ('222',222,now());

查看数据

在这里插入图片描述

这里我们删掉 id=1 的数据 delete from xdx_test where id = 1;，用来模拟异常情况数据丢失，然后我们再来恢复它。

2-5-2、恢复数据

2-5-2-1、开启新的日志

执行命令 flush logs 开启新的日志记录，这样我们就不会收到后面操作的干扰。

2-5-2-2、找到我们要操作的日志

这里我要操作的日志是 mysql-bin.000013

执行命令查看日志结构 show binlog events in 'mysql-bin.000013';

在这里插入图片描述

2-5-2-3、使用 pos 恢复数据

从上面的图，我们可以看到pos的开始和结束的位置，我们可恢复此阶段的数据

# 进入到 binlog 目录下
cd /var/lib/mysql
# 数据恢复命令
mysqlbinlog --start-position=开始的pos --stop-position=结束的pos --database=要操作的数据库 binlog的名称 | mysql -u登陆名 -p登陆密码 -v 要操作的数据库

# 最终执行命令（我的）
mysqlbinlog --start-position=669 --stop-position=980 --database=my_test mysql-bin.000013 | mysql -uroot -p123456 -v my_test

再次进入数据库，查询，发现数据有了

2-5-2-4、使用时间恢复数据

使用命令查看binlog日志 mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000013

数据比较多，这里我只截图需要的部分

在这里插入图片描述

mysqlbinlog --start-datetime='2021-10-14 12:47:12' --stop-datetime='2021-10-14 12:52:40' --database=my_test mysql-bin.000013 | mysql -uroot -p123456 -v my_test