MySQL 的 Binlog 日志是一种二进制格局的日志，Binlog 记录所有的 DDL 和 DML 语句(除了数据查问语句 SELECT、SHOW 等)，以 Event 的模式记录，同时记录语句执行工夫。

Binlog 的次要作用有两个：

数据恢复

因为 Binlog 具体记录了所有批改数据的 SQL，当某一时刻的数据误操作而导致出问题，或者数据库宕机数据失落，那么能够依据 Binlog 来回放历史数据。

主从复制

想要做多机备份的业务，能够去监听以后写库的 Binlog 日志，同步写库的所有更改。

Binlog 包含两类文件：

二进制日志索引文件 (.index)：记录所有的二进制文件。
二进制日志文件(.00000*)：记录所有 DDL 和 DML 语句事件。
Binlog 日志性能默认是开启的，线上状况下 Binlog 日志的增长速度是很快的，在 MySQL 的配置文件 my.cnf 中提供一些参数来对 Binlog 进行设置。

Copy
设置此参数示意启用 binlog 性能，并制订二进制日志的存储目录
log-bin=/home/mysql/binlog/

mysql-bin.* 日志文件最大字节（单位：字节）

设置最大 100MB

max_binlog_size=104857600

设置了只保留 7 天 BINLOG（单位：天）

expire_logs_days = 7

binlog 日志只记录指定库的更新

binlog-do-db=db_name

binlog 日志不记录指定库的更新

binlog-ignore-db=db_name

写缓冲多少次，刷一次磁盘，默认 0

sync_binlog=0
须要留神的是：

max_binlog_size：Binlog 最大和默认值是 1G，该设置并不能严格控制 Binlog 的大小，尤其是 Binlog 比拟凑近最大值而又遇到一个比拟大事务时，为了保障事务的完整性不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进以后日志直到事务完结。所以实在文件有时候会大于 max_binlog_size 设定值。
expire_logs_days：Binlog 过期删除不是服务定时执行，是须要借助事件触发才执行，事件包含：

服务器重启
服务器被更新
日志达到了最大日志长度 max_binlog_size
日志被刷新
二进制日志由配置文件的 log-bin 选项负责启用，MySQL 服务器将在数据根目录创立两个新文件 mysql-bin.000001 和 mysql-bin.index，若配置选项没有给出文件名，MySQL 将应用主机名称命名这两个文件，其中 .index 文件蕴含一份整体日志文件的清单。

sync_binlog：这个参数决定了 Binlog 日志的更新频率。默认 0，示意该操作由操作系统依据本身负载自行决定多久写一次磁盘。

sync_binlog = 1 示意每一条事务提交都会立即写盘。sync_binlog=n 示意 n 个事务提交才会写盘。

依据 MySQL 文档，写 Binlog 的机会是：SQL transaction 执行完，但任何相干的 Locks 还未开释或事务还未最终 commit 前。这样保障了 Binlog 记录的操作时序与数据库理论的数据变更程序统一。

查看 Binlog 文件是否已开启：

Copy

6 rows in set (0.00 sec)
MySQL 会把用户对所有数据库的内容和构造的批改状况记入 mysql-bin.n 文件，而不会记录 SELECT 和没有理论更新的 UPDATE 语句。

如果你不晓得当初有哪些 Binlog 文件，能够应用如下命令：

Copy
show binary logs; #查看 binlog 列表
show master status; #查看最新的 binlog

2 rows in set (0.00 sec)
Binlog 文件是二进制文件，强行关上看到的必然是乱码，MySQL 提供了命令行的形式来展现 Binlog 日志：

Copy
mysqlbinlog mysql-bin.000002 | more
mysqlbinlog 命令即可查看。

1

看起来凌乱其实也有迹可循。Binlog 通过事件的形式来治理日志信息，能够通过 show binlog events in 的语法来查看以后 Binlog 文件对应的具体事件信息。

Copy

3 rows in set (0.01 sec)
这是一份没有任何写入数据的 Binlog 日志文件。

Binlog 的版本是 V4，能够看到日志的完结工夫为 Stop。呈现 Stop event 有两种状况：

是 master shut down 的时候会在 Binlog 文件结尾呈现
是备机在敞开的时候会写入 relay log 结尾，或者执行 RESET SLAVE 命令执行
本文呈现的起因是我有手动进行过 MySQL 服务。

一般来说一份失常的 Binlog 日志文件会以 Rotate event 完结。当 Binlog 文件超过指定大小，Rotate event 会写在文件最初，指向下一个 Binlog 文件。

咱们来看看有过数据操作的 Binlog 日志文件是什么样子的。

Copy

2 rows in set (0.00 sec)
下面是没有任何数据操作且没有被截断的 Binlog。接下来咱们插入一条数据，再看看 Binlog 事件。

Copy

7 rows in set (0.00 sec)
这是退出一条数据之后的 Binlog 事件。

咱们对 event 查问的数据行关键字段来解释一下：

Pos：以后事件的开始地位，每个事件都占用固定的字节大小，完结地位 (End_log_position) 减去 Pos，就是这个事件占用的字节数。

下面的日志中咱们能看到，第一个事件地位并不是从 0 开始，而是从 4。MySQL 通过文件中的前 4 个字节，来判断这是不是一个 Binlog 文件。这种形式很常见，很多格局的文件，如 pdf、doc、jpg 等，都会通常前几个特定字符判断是否是非法文件。

Event_type：示意事件的类型

Server_id：示意产生这个事件的 MySQL server_id，通过设置 my.cnf 中的 server-id 选项进行配置

End_log_position：下一个事件的开始地位

Info：蕴含事件的具体信息

Binlog 日志格局 #
针对不同的应用场景，Binlog 也提供了可定制化的服务，提供了三种模式来提供不同具体水平的日志内容。

Statement 模式：基于 SQL 语句的复制 (statement-based replication-SBR)
Row 模式：基于行的复制(row-based replication-RBR)
Mixed 模式：混合模式复制(mixed-based replication-MBR)
Statement 模式
保留每一条批改数据的 SQL。

该模式只保留一条一般的 SQL 语句，不波及到执行的上下文信息。

因为每台 MySQL 数据库的本地环境可能不一样，那么对于依赖到本地环境的函数或者上下文解决的逻辑 SQL 去解决的时候可能同样的语句在不同的机器上执行进去的成果不统一。

比方像 sleep()函数，last_insert_id()函数，等等，这些都跟特定工夫的本地环境无关。

Row 模式
MySQL V5.1.5 版本开始反对 Row 模式的 Binlog，它与 Statement 模式的区别在于它不保留具体的 SQL 语句，而是记录具体被批改的信息。

比方一条 update 语句更新 10 条数据，如果是 Statement 模式那就保留一条 SQL 就够，然而 Row 模式会保留每一行别离更新了什么，有 10 条数据。

Row 模式的优缺点就很显著了。保留每一个更改的详细信息必然会带来存储空间的疾速收缩，换来的是事件操作的具体记录。所以要求越高代价越高。

Mixed 模式
Mixed 模式即以上两种模式的综合体。既然下面两种模式别离走了极简和精打细算的极其，那是否能够辨别应用场景的状况下将这两种模式综合起来呢？

在 Mixed 模式中，个别的更新语句应用 Statement 模式来保留 Binlog，然而遇到一些函数操作，可能会影响数据准确性的操作则应用 Row 模式来保留。这种形式须要依据每一条具体的 SQL 语句来辨别抉择哪种模式。

MySQL 从 V5.1.8 开始提供 Mixed 模式，V5.7.7 之前的版本默认是 Statement 模式，之后默认应用 Row 模式，然而在 8.0 以上版本曾经默认应用 Mixed 模式了。

查问以后 Binlog 日志应用格局：

Copy

5 rows in set (0.01 sec)
如何通过 mysqlbinlog 命令手动复原数据 #
下面说过每一条 event 都有位点信息，如果咱们以后的 MySQL 库被无操作或者误删除了，那么该如何通过 Binlog 来复原到删除之前的数据状态呢？

首先发现误操作之后，先进行 MySQL 服务，避免持续更新。

接着通过 mysqlbinlog 命令对二进制文件进行剖析，查看误操作之前的位点信息在哪里。

接下来必定就是复原数据，以后数据库的数据曾经是错的，那么就从开始地位到误操作之前位点的数据必定的都是正确的；如果误操作之后也有失常的数据进来，这一段时间的位点数据也要备份。

比如说：

误操作的位点开始值为 501，误操作完结的地位为 705，之后到 800 的位点都是正确数据。

那么从 0 – 500，706 – 800 都是无效数据，接着咱们就能够进行数据恢复了。

先将数据库备份并清空。

接着应用 mysqlbinlog 来复原数据：

0 – 500 的数据：

Copy
mysqlbinlog –start-position=0 –stop-position=500 bin-log.000003 > /root/back.sql;
下面命令的作用就是将 0 -500 位点的数据恢复到自定义的 SQL 文件中。同理 706 – 800 的数据也是一样操作。之后咱们执行这两个 SQL 文件就行了。

Binlog 事件类型 #
下面咱们说到了 Binlog 日志中的事件，不同的操作会对应着不同的事件类型，且不同的 Binlog 日志模式同一个操作的事件类型也不同，上面咱们一起看看常见的事件类型。

首先咱们看看源码中的事件类型定义：

源码地位：/libbinlogevents/include/binlog_event.h

Copy
enum Log_event_type
{
/**

Every time you update this enum (when you add a type), you have to
fix Format_description_event::Format_description_event().

*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/**

NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same class (Load_event)

*/
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,

TABLE_MAP_EVENT = 19,

/**

The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.

*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,

/**

The V1 event numbers are used from 5.1.16 until mysql-trunk-xx

*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,

/**

Something out of the ordinary happened on the master

*/
INCIDENT_EVENT= 26,

/**

Heartbeat event to be send by master at its idle time
to ensure master's online status to slave

*/
HEARTBEAT_LOG_EVENT= 27,

/**

In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.

*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,

/* Version 2 of the Row events /
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,

GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,

PREVIOUS_GTIDS_LOG_EVENT= 35,

TRANSACTION_CONTEXT_EVENT= 36,

VIEW_CHANGE_EVENT= 37,

/ Prepared XA transaction terminal event similar to Xid /
XA_PREPARE_LOG_EVENT= 38,
/**

Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers

*/
ENUM_END_EVENT / end marker /
};
这么多的事件类型咱们就不一一介绍，挑出来一些罕用的来看看。

FORMAT_DESCRIPTION_EVENT

FORMAT_DESCRIPTION_EVENT 是 Binlog V4 中为了取代之前版本中的 START_EVENT_V3 事件而引入的。它是 Binlog 文件中的第一个事件，而且，该事件只会在 Binlog 中呈现一次。MySQL 依据 FORMAT_DESCRIPTION_EVENT 的定义来解析其它事件。

它通常指定了 MySQL 的版本，Binlog 的版本，该 Binlog 文件的创立工夫。

QUERY_EVENT

QUERY_EVENT 类型的事件通常在以下几种状况下应用：

事务开始时，执行的 BEGIN 操作
STATEMENT 格局中的 DML 操作
ROW 格局中的 DDL 操作
比方上文咱们插入一条数据之后的 Binlog 日志：

Copy

7 rows in set (0.00 sec)
XID_EVENT

在事务提交时，不论是 STATEMENT 还 是 ROW 格局的 Binlog，都会在开端增加一个 XID_EVENT 事件代表事务的完结。该事件记录了该事务的 ID，在 MySQL 进行解体复原时，依据事务在 Binlog 中的提交状况来决定是否提交存储引擎中状态为 prepared 的事务。

ROWS_EVENT

对于 ROW 格局的 Binlog，所有的 DML 语句都是记录在 ROWS_EVENT 中。

ROWS_EVENT 分为三种：

WRITE_ROWS_EVENT

UPDATE_ROWS_EVENT

DELETE_ROWS_EVENT

别离对应 insert，update 和 delete 操作。

对于 insert 操作，WRITE_ROWS_EVENT 蕴含了要插入的数据。

对于 update 操作，UPDATE_ROWS_EVENT 不仅蕴含了批改后的数据，还蕴含了批改前的值。

对于 delete 操作，仅仅须要指定删除的主键（在没有主键的状况下，会给定所有列）。

比照 QUERY_EVENT 事件，是以文本模式记录 DML 操作的。而对于 ROWS_EVENT 事件，并不是文本模式，所以在通过 mysqlbinlog 查看基于 ROW 格局的 Binlog 时，须要指定 -vv –base64-output=decode-rows。

咱们来测试一下，首先将日志格局改为 Rows：

Copy
mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

而后刷新一下日志文件，从新开始一个 Binlog 日志。咱们插入一条数据之后看一下日志：

Copy

7 rows in set (0.01 sec)
总结 #
这一篇咱们详解理解 Binlog 日志是什么，外面都有什么内容，Binlog 事件，如何通过 Binlog 来复原数据。Binlog 目前最重要的利用就是用于主从同步，那么下一篇咱们讲来讲讲如何通过 Binlog 实现主从同步。