一、基于binlog的主从复制

实现灾难恢复、水平扩展、统计分析、远程数据分发等功能。

每个写(Insert、Update、Delete，不包括Select)都对应一个事件。从库从主库拉取binlog：

第一步：master提交事务前，改变记录二进制日志(binary log)中（二进制日志事件，binary log event，简称event)

第二步：slave启动I/O线程读取主库binary log中事件，记录到slave自己中继日志(relay log)中。

第三步：启动SQL线程，从relay log中读取事件，备库执行，实现更新。

二、binlog的应用场景

2.1 读写分离

每个Slave都连Master上，获取binlog本地复制。

slave之间做负载均衡。tddl、sharding-jdbc完成读写分离

2.2 数据恢复

2.3 数据最终一致性

数据库与redis缓存：只更新数据库

产生binlog。组件模拟slave，拉binlog异步更新缓存、索引或者发送MQ消息。如linkedin的databus，阿里canal，美团puma。

(1)增量索引

全量、增量索引。增量监听binlog变化，转换成es语法，实时索引更新。

(2)可靠消息

保证本地事务与发送消息到MQ一致。用本地事务表或者独立消息服务保证，或RocketMQ事务消息。这两种方案，都侵入性，对业务不透明。订阅binlog发可靠消息，解耦、无侵入。可靠消息一致性的奇淫技巧。

(3)缓存一致性

只更新数据库，binlog异步更新缓存(删除，让业务回源到数据库)。更新失败，没有binlog，实现最终一致性。

问题：多个slave管理开销，流量大

优化：多slave获同一份binlog，本质上：一份binlog数据，不同场景用，互不影响。

消息中间件解决方案。支持consumer group概念，如kafka、rocketmq等。同一topic中数据，不同consumer group来消费，不同consumer group相互隔离，将binlog统一发送MQ的Topic中（用MQ高级特性。削峰、消息回溯功能）

2.4 异地多活

多个数据中心都写入数据，往对方同步。

数据冲突：双方同时插入相同主键值，同步时冲突

数据回环：库A中插入的数据，binlog同步到B中，产生binlog。B同步回库A，死循环。

阿里开源otter，美团)的DRC解决。异地多活场景下的数据同步之道

三、 Binlog事件详解

3.1 多文件存储

数据量多，分配多个文件存储。"show binary logs"查看当前有多少个binlog文件，每个大小

max_binlog_size，控制大小，默认1G

expire_logs_days，控制保留天数，默认0，永久保留。

生产环境，一条记录变更多次，记一条，对应binlog事件多个。无法保留所有

max_binlog_files（mysql的percona分支上），设置保留binlog数量，精确控制binlog文件占磁盘空间。非常有用，10分钟产生binlog文件，1G这种增长速度，1天144G，可控制binlog最多占用50G

3.2 Binlog管理事件

"show binlog events"看空binlog文件，只包含(部分)管理事件

三个事件类型：

Format_desc：binlog第一个事件。Mysql Server版本5.7.10，Binlog版本是4。

Previous_gtids：完整名，PREVIOUS_GTIDS_LOG_EVENT。Mysql 基于GTID复制，这binlog已执行过GTID。开启GTID选项才会有值

Rotate：binlog结束事件。指定文件名称mysql-bin.000004。

关于"show binlog events"语法显示的每一列的作用说明如下：

Log_name：binlog文件名

Pos：开始位置，占字节大小，结束位置(End_log_position)减去Pos，第一个事件位置从4开始。Mysql通过前4个字节，判断这是不是binlog文件。如pdf、doc、jpg都用前几个特定字符判断是否合法文件。

Server_id：事件mysql server_id，my.cnf中的server-id配置。

End_log_position：下事件开始位置

Info：当前事件描述信息

3.3 Statement模式下的事件

逻辑复制binary log文件

insert intouser(name) values("tianbowen");  show binlog events看binary log内容：

       红色框架中Event，是我们执行上面Insert语句产生的4个Event。下面进行详细的说明：

(划重点)首先，需要说明的是，每个事务都是以Query Event作为开始，其INFO列内容为"BEGIN"，以Xid Event表示结束，其INFO列内容为COMMIT。即使对于单条更新SQL我们没有开启事务，Mysql也会默认的帮我们开启事务。因此在上面的红色框中，尽管我们只是执行了一个INSERT语句，没有开启事务，但是Mysql 默认帮我们开启了事务，所以第一个Event是Query Event，最后一个是Xid Event。

接着，是一个Intvar Event，因为我们的Insert语句插入的表中，主键是自增的(AUTO_INCREMENT)列，Mysql首先会自增一个值，这就是Intvar Event的作用，这里我们看到INFO列的值为INSERT_ID=1，也就是说，这次的自增主键id为1。需要注意的是，这个事件，只会在Statement模式下出现。

然后，还是一个Query Event，这里记录的就是我们插入的SQL。这也体现了Statement模式的作用，就是记录我们执行的SQL。

Statement模式下还有一些不常用的Event，如USER_VAR_EVENT，这是用于记录用户设置的变量，仅仅在Statement模式起作用。如：

执行以下SQL：

set@name='tianshouzhi';insertintouser(name)values(@name);

这里，我们插入sql的时候，通过引用一个变量。此时查看binlog变化，这里为了易于观察，在执行show binlog events时，指定了binlog文件和from的位置，即只查看指定binlog文件中从指定位置开始的event。如下：

可以看到，依然符合我们所说的，对于这个插入语句，依然默认开启了事务。主键自曾值INSERT_ID=2。

当然，我们也看到了User var这个事件，其记录了我们的设置的变量值，只不过以16进制显示。

3.4 Row模式下的事件

某条sql影响的所有行记录变更前和变更后的值。主要10个事件：

很直观的，我们看到了INSERT、DELETE、UPDATE操作都有3个版本(v0、v1、v2)，v0和v1已经过时，我们只需要关注V2版本。

此外，还有一个TABLE_MAP_EVENT，这个event我们需要特别关注，可以理解其作用就是记录了INSERT、DELETE、UPDATE操作的表结构。

下面，我们通过案例演示，ROW模式是如何记录变更前后记录的值，而不是记录SQL。这里只演示UPDATE，INSERT和DELETE也是类似。

在前面的操作步骤中，我们已经插入了2条记录，如下：

现在需要从Statement模式切换到Row模式，重启Mysql之后，执行以下SQL更新这两条记录：

updateusersetname='wangxiaoxiao';

在binary log中，会把这2条记录变更前后的值都记录下来，以下是一个逻辑示意图：

该逻辑示意图显示了，在默认情况下，受到影响的记录行，每个字段变更前的和变更后的值，都会被记录下来，即使这个字段的值没有发生变化。

接着，我们还是通过"show binlog events"语法来验证：

首先我们可以看到的是，在Row模式下，单条SQL依然会默认开启事务，通过Query Event(值为BEGIN)开始，以Xid Event结束。

接着，我们看到了一个Table_map 事件，就是前面提到的TABLE_MAP_EVENT，在INFO列，我们可以看到其记录table_id为108，操作的是test库中user表。

最后，是一个Update_rows事件，然而其INFO，并没有像Statement模式那样，显示一条SQL，我们无法直接看到其变更前后的值是什么。

由于存储的都是二进制内容，直接vim无法查看，我们需要借助另外一个工具mysqlbinlog来查看其内容。如下：

截图中显示了2个event，第一个红色框就是Table_map事件，第二个是Update_rows事件。

在第二个红色框架中，显示了两个Update sql，这是只是mysqlbinlog工具为了方便我们查看，反解成SQL而已。我们看到了WHERE以及SET子句中，并没有直接列出字段名，而是以@1、@2这样的表示字段位于数据库表中的顺序。事实上，这里显示的内容，WHERE部分就是每个字段修改前的值，而SET部分，则是每个字段修改后的值，也就是变更前后的值都会记录。

这里我们思考以下mysqlbinlog工具的工作原理，其可以将二进制数据反解成SQL进行展示。那么，如果我们可以自己解析binlog，就可以做数据恢复，这并非是什么难事。例如用户误删除的数据，执行的是DETELE语句，由于Row模式下会记录变更之前的字段的值，我们可以将其反解成一个INSERT语句，重新插入，从而实现数据恢复。

3.4.1 binlog_row_image参数

我们经常会看到一些Row模式和Statement模式的比较。ROW模式下，即使我们只更新了一条记录的其中某个字段，也会记录每个字段变更前后的值，binlog日志就会变大，带来磁盘IO上的开销，以及网络开销。

事实上，这个行为可以通过binlog_row_image控制其有3个值，默认为FULL： 

FULL : 记录列的所有修改，即使字段没有发生变更也会记录。 

MINIMAL ：只记录修改的列。 

NOBLOB :如果是text类型或clob字段，不记录这些日志。 

我们可以将其修改为MINIMAL，则可以只记录修改的列的值。

3.4.2 binlog_rows_query_log_events参数

在Statement模式下，直接记录SQL比较直观，事实上，在Row模式下，也可以记录。mysql提供了一个binlog_rows_query_log_events参数，默认为值为FALSE，如果为true的情况下，会通过Rows Query Event来记录SQL。

my.cnf开启row模式原始sql记录(需重启)添加：binlog-rows-query-log_events=1

insert into user(name)values("maoxinyi");  binlog中看到Rows Query Event

3.5 GTID相关事件

GTID复制。修改my.cnf开启GTID：gtid-mode=onenforce-gtid-consistency=true

执行事务之前，都记录GTID Event  insert into user("name")values("zhuyihan");binlog内容：

执行sql手工切换下个binlog：mysql> flush logs;Query OK, 0 rows affected (0.00 sec)

之前执行过GTID在下一个文件中出现。