MySQL之binlog

一、Binlog 简介

MySQL中一般有以下几种日志：

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

二进制日志记录了对MySQL数据库执行更改的所有操作，也就是不包括SELETE和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变成，那么该操作可能也会写入二进制日志，比如修改的新值与旧值相等。

1.1 Binlog日志的两个最重要的使用场景

MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

数据恢复：通过使用 mysqlbinlog工具来使恢复数据。

1.2 如何启用Binlog

一般来说开启binlog日志大概会有1%的性能损耗。

启用binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：

在配置文件中加入 log-bin 配置，表示启用binlog，如果没有给定值，写成 log-bin=，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）

[mysqld]
log-bin=my-binlog-name

也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog，通过 SET SQL_LOG_BIN=0 命令停用 binlog。启用 binlog 之后须重启MySQL才能生效。

1.3常用的Binlog操作命令

# 是否启用binlog日志
show variables like 'log_bin';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position
show master status;


# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容
show binlog events;

# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';

# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3; 

# 删除当前的binlog文件
reset master; 

# 删除slave的中继日志
reset slave;

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件
purge master logs to 'master.000003';

1.4写 Binlog 的时机

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓存中的二进制日志写入二进制日志文件中。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

1.5 Binlog 文件以及扩展

binlog日志包括两类文件:

二进制日志索引文件（文件名后缀为.index）用于记录所有有效的二进制文件
二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件
binlog是一个二进制文件集合，每个binlog文件以一个4字节的魔数开头，接着是一组Events:

魔数：0xfe62696e对应的是0xfebin；
Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；
第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；
其余的Event按照第一个Event的格式版本写入；
最后一个Event用于说明下一个binlog文件；
binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表

当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

1. MySQL服务器停止或重启时。
2. 使用 flush logs 命令。
3. 当 binlog 文件大小超过 max_binlog_size 变量的值时。

max_binlog_size 的最小值是4096字节，最大值和默认值是 1GB (1073741824字节)。事务被写入到binlog的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，你可能会看到binlog文件大于 max_binlog_size 的情况。

1.6 Binlog 的日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

• STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
• ROW：基于行的复制（row-based replication, RBR）
• MIXED：混合模式复制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

Statement

每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

Row

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

1.7 Binlog 事件的结构

一个事件对象分为事件头和事件体，事件的结构如下：

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

如果事件头的长度是 x 字节，那么事件体的长度为 (event_length - x) 字节；设事件体中 fixed part 的长度为 y 字节，那么 variable part 的长度为 (event_length - (x + y)) 字节

二、使用Binlog进行复制

复制是mysql最重要的功能之一，mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的；从5.6开始复制有两种实现方式，基于binlog和基于GTID（全局事务标示符）；本文接下来将介绍基于binlog的一主一从复制；其复制的基本过程如下：

a.Master将数据改变记录到二进制日志(binary log)中
b.Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
c.Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。
返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
d.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的
文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
e.Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行

接下来使用实例演示基于binlog的主从复制：

a.配置master
主要包括设置复制账号，并授予REPLICATION SLAVE权限，具体信息会存储在于master.info文件中，及开启binlog；
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON . TO 'test'@'%';
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
查看master当前binlogmysql状态：mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
建表插入数据：
CREATE TABLE tb_person (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(36) NOT NULL,
address varchar(36) NOT NULL DEFAULT '',
sex varchar(12) NOT NULL DEFAULT 'Man' ,
other varchar(256) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
insert into tb_person set name="name1", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name2", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name3", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name4", address="beijing", sex="man", other="nothing";
b.配置slave
Slave的配置类似master，需额外设置relay_log参数，slave没有必要开启二进制日志，如果slave为其它slave的master，须设置bin_log
c.连接master
mysql> CHANGE MASTER TO
MASTER_HOST='10.108.111.14',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=120;
d.show slave status;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ---------------------------- slave io状态，表示还未启动
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60 ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
Master_Log_File: mysql-bin.000003 ------------ 当前读取master binlog文件
Read_Master_Log_Pos: 120 ------------------------- slave读取master binlog文件位置
Relay_Log_File: relay-bin.000001 ------------ 回放binlog
Relay_Log_Pos: 4 -------------------------- 回放relay log位置
Relay_Master_Log_File: mysql-bin.000003 ------------ 回放log对应maser binlog文件
Slave_IO_Running: No
Slave_SQL_Running: No
Exec_Master_Log_Pos: 0 --------------------------- 相对于master从库的sql线程执行到的位置
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。
e.启动复制
start slave
f.再次观察slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event -- 等待master新的event
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3469 ---------------------------- 3469 等于Exec_Master_Log_Pos，已完成回放
Relay_Log_File: relay-bin.000002 ||
Relay_Log_Pos: 1423 ||
Relay_Master_Log_File: mysql-bin.000003 ||
Slave_IO_Running: Yes ||
Slave_SQL_Running: Yes ||
Exec_Master_Log_Pos: 3469 -----------------------------3469 等于slave读取master binlog位置，已完成回放
Seconds_Behind_Master: 0
可看到slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master=0。Relay_Log_Pos增加，意味着一些事件被获取并执行了。
最后看下如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
4、如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
g.查询slave数据，主从一致
mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name | address | sex | other |
+----+-------+---------+-----+---------+
| 5 | name4 | beijing | man | nothing |
| 6 | name2 | beijing | man | nothing |
| 7 | name1 | beijing | man | nothing |
| 8 | name3 | beijing | man | nothing |
+----+-------+---------+-----+---------+
关于mysql复制的内容还有很多，比如不同的同步方式、复制格式情况下有什么区别，有什么特点，应该在什么情况下使用....这里不再一一介绍。

三、使用Binlog进行数据恢复

恢复是binlog的两大主要作用之一，接下来通过实例演示如何利用binlog恢复数据：
a.首先，看下当前binlog位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1847 | | | |
+------------------+----------+--------------+------------------+-------------------+
b.向表tb_person中插入两条记录：
insert into tb_person set name="person_1", address="beijing", sex="man", other="test-1";
insert into tb_person set name="person_2", address="beijing", sex="man", other="test-2";
c.记录当前binlog位置：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 2585 | | | |
+------------------+----------+--------------+------------------+-------------------+
d.查询数据
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.删除一条: delete from tb_person where name ="person_2";
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
+----+----------+---------+-----+--------+
f. binlog恢复（指定pos点恢复/部分恢复）
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql
d.数据恢复完成
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.总结
恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已

转载文章
MySQL Binlog 介绍
腾讯工程师带你深入解析 MySQL binlog