深入解析 MySQL binlog

1.概述

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录所有数据库表结构变更、以及数据修改的二进制文件，不会记录SELECT SHOW等操作，Binlog以"事务"的形式保存在磁盘中，还包含语句执行的消耗时间；主要应用于两种场景：
主从复制
数据恢复
Binlog的文件名默认为“主机名_binlog-序列号”格式，也可以在配置文件中指定名称。文件记录模式一种有三种：

ROW：日志中会记录每一行数据被修改的情况，然后再slave端对相同的数据进行修改。完全实现数据同步、数据恢复。但是会产生大量的日志（可靠但是消耗资源）。如：大批量的数据更新、尤其是alter table 会让日志暴涨。

STATMENT：每一条被修改数据的sql都会被记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行过的相同sql再次执行。简称sql复制。日志量少、减少了IO、提升了存储和恢复速度，在某些情况下，会导致主从数据的不一致。如：now() last_insert_id()。

MIXED：以上两种模式的混用，一般使用STATMENT模式保存binlog，对于STATMENT无法复制的操作使用ROW模式保存binlog，MYSQL会根据执行的sql语句写入模式。

2.Binlog文件结构

Mysql的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应不同的log event。比较常用的log event有：Query event 、Row event、 Xid event等。 binlog文件的内容就是各种log event的集合。

3.binlog的写入机制

根据记录模式和操作触发event事件生成log event (事件触发执行机制)，将事务执行过程中产生log event 写入缓冲区，每个事务线程都有一个缓冲区。log event 保存在一个bin_cache_mngr的数据结构中，在该结构中有两个缓冲区，一个是stmt_cache, 用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。事务提交阶段将会产生的log event 写入到外部binlog文件中，不同事务以串行的方式将log event 写入到binlog文件中，所以一个事务包含的log event 信息保存在binlog文件中是连续的，中间不会插入其他事务的log event。

4 命令操作-恢复数据

在/etc 目录下新建my.cnf 并添加如下内容：

➜  /Users/zhaoshuai11 cd /etc
➜  /etc cat my.cnf
[mysqld]
# log_bin
log-bin = mysql-bin #开启binlog
binlog-format = ROW #选择row模式
server_id = 1 #配置mysql replication需要定义，不能和canal的slaveId重复
1
2
3
4
5
6
7

show variables like '%log_bin%';
1

获取binlog文件列表：

show binary logs;
1

生成新的日志文件的条件：每当我们停止或重启服务器时，服务器会把日志文件记入下一个日志文件，MySQL会在重启时生成一个新的日志文件，文件序号递增。如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）日志被刷新时，新生成一个日志文件

flush logs;
1

执行下列的语句:

create database if not exists test_binlog;

use test_binlog;


DROP TABLE IF EXISTS `Websites`;
CREATE TABLE `Websites` (
  `id` int(11) NOT NULL,
  `name` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `access_log` (
  `aid` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `count` int(11) NOT NULL
);

INSERT INTO `Websites` VALUES ('1', 'Google');
INSERT INTO `Websites` VALUES('2', 'TaoBao');
INSERT INTO `Websites` VALUES('3', 'CaiNiao');

INSERT INTO `access_log` VALUES ('1', '1', '45');
INSERT INTO `access_log` VALUES('2', '3', '100');
INSERT INTO `access_log` VALUES('3', '1', '230');
INSERT INTO `access_log` VALUES('4', '2', '10');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

show binary logs 可以看到输出如下，说明我们刚才执行的操作已经写入了mysql-bin.000007中

我们可以使用如下的命令来查看当前正在写入的binlog文件：

show master status\G
1

使用mysqlbinlog进行查看还是以mysql-bin.000007日志为例，基本使用用法如下：

mysqlbinlog "/usr/local/mysql/data/mysql-bin.000007"
1

mysqlbinlog mysql-bin.000007

mysqlbinlog mysql-bin.000007 > 'test.sql'
1
2
3

使用mysqlbinlog恢复数据：

mysqlbinlog --start-datetime="2022-06-22 11:38:59" --stop-datetime="2022-06-22 11:40:01" 'mysql-bin.000007' | mysql -uroot -proot
1