binlog浅析

一、基础知识

什么是binlog？

（图一）

全称：Binary Log （二进制日志）,包含描述数据库更改的“ 事件 ”，例如表创建操作或对表数据的更改。二进制日志不用于诸如select或 show不修改数据的语句。要记录所有语句（例如，标识问题查询），请使用常规查询日志。

在哪里产生的？

我们都知道MYSQL有两层结构，第一层：server层，里面包含连接器、查询缓存、解析器、优化器、执行器，第二次是存储引擎层，例如：InnoDB、MyISAM、Memory 等多个存储引擎

（图二）

binlog产生于mysql中的server层。

若是mysql采用的为innodb引擎（这里是经典的两阶段提交）：

（图三）

binlog文件都有什么？

binlog文件包含两种类型：

索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

1.索引文件大小：我们可以通过 max_binlog_size 参数设置binlog文件的大小。Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

2.索引文件删除：binlog的删除可以手工删除或自动删除。通过设置 expire_logs_days 实现自动删除

手动删除需登录mysql后执行如下命令：

mysql> reset master;        //删除master的binlog，即手动删除所有的binlog日志
mysql> reset slave;          //删除slave的中继日志
mysql> purge master logs before '2019-07-07 17:20:00';         //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000003';       //删除指定日志文件的日志索引中binlog日志文件

可以通过如下命令确认目前正在使用binlog文件：

-- 通过这句话查询到目前写入的是哪个binlog文件
show master status;

binlog的格式都有什么？

（图四）

binlog一共有三种格式：

statement格式，最后有commit，记录为基本语句
row格式，记录为基于行
MIXED,日志记录使用混合格式

format	定义	优点	缺点
statement	记录的是修改SQL语句	日志文件小，节约IO，提高性能	准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()、limit(由于mysql是自选索引，有可能master同salve选择的索引不同，导致更新的内容也不同)等在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
row	记录的是每行实际数据的变更	准确性强，能准确复制数据的变更	日志文件大，较大的网络IO和磁盘IO
mixed	statement和row模式的混合	准确性强，文件大小适中	当binlog format 设置为mixed时，普通复制不会有问题，但是级联复制在特殊情况下会binlog丢失。

statement

记录的是修改SQL语句

日志文件小，节约IO，提高性能

准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()、limit(由于mysql是自选索引，有可能master同salve选择的索引不同，导致更新的内容也不同)等

在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

row

记录的是每行实际数据的变更

准确性强，能准确复制数据的变更

日志文件大，较大的网络IO和磁盘IO

mixed

statement和row模式的混合

准确性强，文件大小适中

当binlog format 设置为mixed时，普通复制不会有问题，但是级联复制在特殊情况下会binlog丢失。

在MySQL 5.6中，默认的二进制日志记录格式是 STATEMENT。

日志格式也可以在运行时切换。存在两种更改方式：

mysql> SET GLOBAL binlog_format = 'STATEMENT'; 基于当前启动的mysql的，若是当前mysql服务重启，则日志格式又恢复为默认。
mysql> SET SESSION binlog_format = 'STATEMENT'; 基于当前请求session的

优先级：session > global > default

我们测试一下日志格式有什么区别：

　　首先我们先确认下系统是否开启了binlog ，系统为windows10系统，其中安装的mysql版本是：mysql-5.7.20-winx64

　　然后登录mysql 查看binlog是否开启：show variables like 'log_bin';

我们本地安装的mysql的默认的binlog都是关闭的，我们怎么打开它呢？

　找到mysql安装目录下的my.ini文件，然后使用notepad++ 打开（可以使用其他编辑器），（这里注意了，我也使用txt文档打开过，由于txt文档打开的默认格式不是utf-8的，导致txt保存之后存在问题）。

增加如下内容：

# Binary Logging
#MySQL 5.7.3 及以后版本,如果没有设置server-id, 那么设置binlog后无法开启MySQL服务
log-bin=E:/software/tool/environment/mysql/5.7.20/mysql-5.7.20-winx64/logs/binlogs
#binlog日志格式，默认为STATEMENT：每一条SQL语句都会被记录；ROW：仅记录哪条数据被修改并且修改成什么样子，是binlog开启并且能恢复数据的关键；
binlog-format=Row
#binlog过期清理时间；
expire_logs_days=7
#binlog每个日志文件大小；
max_binlog_size=100m
#binlog缓存大小；
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m

#Server ID
server-id=201609

然后重启mysql服务

确认下mysql是否开启show variables like 'log_bin'; 对应的值是on就可以
show variables like 'binlog_format' 看看目前我们的binlog日志是什么，因为我们在my.ini文件中设置了默认的binlog格式，这个时候我们看到的应该是row格式
执行
```
SET GLOBAL BINLOG_FORMAT = 'STATEMENT';
```
把更改后的客户端的连接的 binlog_format都更改为 STATEMENT 格式的
然后打开一个新的客户端连接，确认下binlog_format是否更改，然后我们后面的操作会在这个连接中进行

创建一个表 test

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(10) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

并插入几条数据

insert into test value(4,22);

然后我们查看下现在写入的binlog日志文件是哪个
```
-- 然后看一下现在写入的binlog文件
show master status;
```
我这里写入的是binlogs.000003 ,如果是第一次使用的话，写入的应该是binlogs.000001 。
然后可以跟踪下日志的内容了
```
-- 然后看下记录的内容
show binlog events in 'binlogs.000003';
```
我们看这里的info，从begin到commit，中间是真实执行的语句，我们实际上只是执行了insert操作，在这之前，还有use ...操作，这个命令不是我们主动执行的，而是mysql根据当前操作的数据表所在的库，自动添加的

注意在最后有一个xid event，xid是把binlog和redolog关联起来的关键，binlog和redolog都有一个共同的字段xid，当系统崩溃进行恢复的时候，会按照顺序扫描binlog，若是碰到既有prepare又有commit的redolog，就直接提交；若是碰到只有prepare，而没有commit的redolog，就直接拿xid去binlog查询对应的事务。

这里简单说下 redo Log，以顺序的方式写入文件，当全部文件写满的时候则回到第一个文件相应的起始位置进行覆盖写（但在做redo checkpoint时，也会更新第一个日志文件的头部checkpoint标记，所以严格来讲也不算顺序写），在InnoDB内部，逻辑上Redo Log被看作一个文件，对应一个space id (InnoDB通过space的概念来组织物理存储，包括不同的表，数据字典，redo，undo等)。
来自于 https://blog.51cto.com/wangwei007/2287431、https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html
那么我们再把binlog的日志格式更改为 row，看下效果。

可以看到这种同 binlog格式为statement相比，前后的begin、commit是相同的，但是row格式中的binlog没有了sql语句的原文，而是替换成了两个envent，Table_map和Write_rows ，table_map标识的是操作的表名，另外的ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。

我们在这里完全看不出来具体操作内容是什么。我们需要辅助下mysqlbinlog工具。用这个命令解析和查看binlog中的内容。

我们可以看到这个事务是从459开始。

在这里我们可以看到server_id 是 201609 以及对应的xid = 182 以及对应的更新的值比如（@1=5,@2=22）最后的xid envent用于表示事务被正确的提交了。里面有一些参数暂未清楚。
再说下binlog的格式为mixed
出现的原因：
1. 由于statement可能会导致主备不一致。
2. row格式的缺点是比较占用空间，若是使用一个delete删掉10万数据，使用statement就是一个sql语句，但是row格式的话，就要把10万条记录都写到binlog中。这种不仅占用空间还耗费IO资源。　　
所以mysql就采取了个折中方案，也就是有了mixed的格式，mysql自己会判断这条sql语句是否可能引起主备不一致，如果有可能，就用row格式，否则用statement格式。

为什么要写binlog日志？

二进制日志有两个重要目的：

主从复制，对于复制，主复制服务器上的二进制日志提供要发送到从属服务器的数据更改的记录。主服务器将其二进制日志中包含的事件发送到其从属服务器，这些服务器执行这些事件以对主服务器上的数据进行相同的更改。

　　　　MySQL复制功能使用三个线程实现，一个在主服务器上，两个在从服务器上：

- Binlog转储线程。 主设备创建一个线程，以便在从设备连接时将二进制日志内容发送到从设备。这个线程就是Binlog Dump线程。
  
  二进制日志转储线程获取主机二进制日志上的锁，用于读取要发送到从机的每个事件。一旦读取了事件，即使在事件发送到从站之前，锁也会被释放。
- 从属I / O线程。 在从属服务器上发出语句时，从属服务器会创建一个I / O线程，该线程连接到主服务器并要求它发送记录在其二进制日志中的更新。
  
  从属I / O线程读取主Binlog Dump线程发送的更新（请参阅上一项）并将它们复制到包含从属中继日志的本地文件。
- 从属SQL线程。 从属设备创建一个SQL线程来读取由从属I / O线程写入的中继日志，并执行其中包含的事件。

某些数据恢复操作需要使用二进制日志。还原备份后，将重新执行备份后记录的二进制日志中的事件。这些事件使数据库从备份点更新。

1.假设执行的是delete语句，row格式下，binlog也会把delete的数据行保存起来，所以当执行完delete之后，发现删错了数据，可以直接把binlog中的delete转为insert，被误删的数据就可以恢复了

2.如果是执行了insert语句，row格式下，binlog也会记录insert的每一个字段，以及精确到刚刚插入的那一行，这个时候直接把insert转为delete，删掉这行数据就可以了

3.如果执行了update语句，row格式下，binlog里面会记录修改前整行数据和修改后的整行数据，恢复update的话，只需要把这个envent前后的两行信息对调一下，再去执行，就可以恢复了。

这里拿一个例子举例：

若是我们线上数据在早晨8点做了全量备份，到了中午12点时，数据库崩溃了（或者删库跑路了），这个时候我们应该怎么做呢？

首先我们要定位到数据库当时记录的binlog文件
其次定位到8点到中午十二点的binlog记录
把数据库的全量备份还原，并把binlog记录导出到sql文件
执行binlog导出的sql文件，即能够恢复到当时的数据状态。

-- 通过这句话查询到目前写入的是哪个binlog文件
show master status;
-- 查看binlog的日志记录
show binlog events in 'binlogs.000002';
-- 在binlogs目录下 导出update记录
-- mysqlbinlog --start-position=2388 --stop-position=2699 binlogs.000002 > e:\\update2.sql
-- 登录mysql  执行数据恢复
--  source e://update.sql

关于binlog的简单介绍就先说到这里了。加油！

参考文章：

图一：https://dev.mysql.com/doc/refman/5.6/en/binary-log.html

图二：https://zhaodengfeng1989.iteye.com/blog/2419768

图三：https://www.jianshu.com/p/4bcfffb27ed5

https://www.cnblogs.com/rjzheng/p/9721765.html

https://dev.mysql.com/doc/refman/5.6/en/binary-log.html

https://www.cnblogs.com/ivictor/p/5780617.html

https://blog.csdn.net/bohu83/article/details/81568341

https://www.cnblogs.com/lv1572407/p/10499274.html

https://www.cnblogs.com/lixigang/articles/5057113.html

posted @ 2019-06-30 22:17 冯廷鑫阅读(4666) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

冯廷鑫

加油

binlog浅析

一、基础知识

公告