MySQL 主从同步和binlog应用

MySQL 主从/主备架构的数据同步机制是什么样的？binlog 记录的内容是什么？Redis等组件怎么使得数据和MySQL保证最终一致性？

二进制日志（binary log）记录了执行修改（更新删除）操作，不包含select查询。他的一个主要作用就是同步数据给其他MySQL实例。

binlog 日志参数

列举三个值得说的重要参数：

mysql> show variables where variable_name in ('binlog_cache_size', 'binlog_format', 'sync_binlog');
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
| binlog_format     | ROW   |
| sync_binlog       | 1     |
+-------------------+-------+
3 rows in set (0.00 sec)

binlog_cache_size

事务未提交时，binlog会先写 write 到缓冲区。提交时再把缓冲区binlog写 fsync到磁盘。binlog_cache_size 参数就是这个缓冲区大小。默认32kb，线上根据硬件、业务量自定义设置，如1M等。

值得注意的是，这个参数是线程级别的，每个线程在事务处理时占用自己的缓冲区大小。binlog文件是所有线程共享的。
sync_binlog

这个参数指binlog 落盘时机：
1. sync_binlog=0，表示每次提交事务都只写到缓冲区，不落盘。MySQL异常重启时可能导致数据丢失。
2. sync_binlog=1，表示每次提交事务都会写磁盘。数据不丢，但影响IO吞吐。
3. sync_binlog=N，表示N次写到缓冲区，再执行fsync 刷到磁盘。兼顾数据丢失和IO吞吐。
binlog_format

这个参数指 binlog格式，分别是 STATEMENT、ROW、MIXED。下边细说。

binlog 格式

上边提到 binlog_format参数，分别是 STATEMENT、ROW、MIXED，它影响记录二进制日志的格式。

日志信息查看

介绍格式之前，我们先看下 binlog 日志文件的位置、binlog 日志文件查看工具。

文件目录查看

# 查看 binlog文件目录
mysql> show variables like '%datadir%';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+

# 查看目录下 binlog文件
mysql> system ls -l /usr/local/var/mysql/binlog*;
-rw-r-----  1 hansongda  admin   156  10 12 22:54 /usr/local/var/mysql/binlog.000013
-rw-r-----  1 hansongda  admin   200  10 12 22:55 /usr/local/var/mysql/binlog.000014
-rw-r-----  1 hansongda  admin  1734  10 13 10:33 /usr/local/var/mysql/binlog.000015
-rw-r-----  1 hansongda  admin    48  10 12 22:55 /usr/local/var/mysql/binlog.index

日志文件查看

查看binlog 基本信息：

# 查看binlog 文件写到什么逻辑位置
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000015 |     2061 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# binlog 事件类型、文件起始位置等信息
mysql> show binlog events in 'binlog.000015'\G
*************************** 29. row ***************************
   Log_name: binlog.000015
        Pos: 1813
 Event_type: Query
  Server_id: 1
End_log_pos: 1898
       Info: BEGIN
*************************** 30. row ***************************
   Log_name: binlog.000015
        Pos: 1898
 Event_type: Table_map
  Server_id: 1
End_log_pos: 1961
       Info: table_id: 107 (test1.t)
*************************** 31. row ***************************
   Log_name: binlog.000015
        Pos: 1961
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 2030
       Info: table_id: 107 flags: STMT_END_F
*************************** 32. row ***************************
   Log_name: binlog.000015
        Pos: 2030
 Event_type: Xid
  Server_id: 1
End_log_pos: 2061
       Info: COMMIT /* xid=165 */

binlog 文件解析查看：

MySQL 自带的解析binlog工具 mysqlbinlog

-vv 参数可以查看更详细信息
–start-position 表示要查看的binlog某个起始位置

如下：

~ mysqlbinlog -vv --start-position=1813 /usr/local/var/mysql/binlog.000015

STATEMENT 格式

STATEMENT 记录的是日志的逻辑SQL语句，把执行的更新语句记录了下来。

更改binlog_format：

# 默认 ROW
mysql> select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW                    |
+------------------------+
1 row in set (0.00 sec)

# 改成 STATEMENT
mysql> set GLOBAL binlog_format='STATEMENT';
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| STATEMENT              |
+------------------------+
1 row in set (0.00 sec)

修改某一行sql：

update t set name='yy' where id=92

解析binlog，文本省略了部分信息：

~ mysqlbinlog -vv --start-position=2061 /usr/local/var/mysql/binlog.000015
....
....
#191014 14:46:34 server id 1  end_log_pos 2562 CRC32 0xb1e9d264     Query    thread_id=31    exec_time=0    error_code=0
SET TIMESTAMP=1571035594/*!*/;
BEGIN
/*!*/;
# at 2562
#191014 14:46:34 server id 1  end_log_pos 2684 CRC32 0x9427f81c     Query    thread_id=31    exec_time=0    error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1571035594/*!*/;
update t set name='yy' where id=92
/*!*/;
# at 2684
#191014 14:46:34 server id 1  end_log_pos 2715 CRC32 0x1b76967e     Xid = 201
COMMIT/*!*/;
....
....

可以看到 STATEMENT 格式下，binlog 内部会把原始修改sql 记录下来。

这其实并不足够安全，比如同样一个update sql（带多个查询条件，不带唯一键），如果 A库和 B库执行时命中了不同索引时，那么更新的数据也就不同了。

ROW 格式

ROW 格式会记录什么表，什么行做了什么变更，包括变更前这个行各字段信息，变更后各字段信息。

修改某一行sql：

update t set name='xx' where id=92;

解析binlog，文本省略了部分信息：

~ mysqlbinlog -vv --start-position=1813 /usr/local/var/mysql/binlog.000015
....
....
BEGIN
/*!*/;
# at 1898
#191014 12:57:59 server id 1  end_log_pos 1961 CRC32 0x6a9d9881     Table_map: `test1`.`t` mapped to number 107
# at 1961
#191014 12:57:59 server id 1  end_log_pos 2030 CRC32 0xd53baa80     Update_rows: table id 107 flags: STMT_END_F

BINLOG '
14s3ZBMBAAAAPwAAAKkHAAAAAGsAAAAAAAEABXRlc3QxAAF0AAUDDw8DDwZAAEAAAAIQAQEAAgP8
/wCBmJ1q
14s3ZB8BAAAARQAAAO4HAAAAAGsAAAAAAAEAAgAF//8AXAAAAAF4AXgBAAAAAQB4AFwAAAABeAJ4
eAEAAAABAHiAqjvV
'/*!*/;
### UPDATE `test1`.`t`
### WHERE
###   @1=92 /* INT meta=0 nullable=0 is_null=0 */
###   @2='x' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='x' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5='x' /* VARSTRING(512) meta=512 nullable=1 is_null=0 */
### SET
###   @1=92 /* INT meta=0 nullable=0 is_null=0 */
###   @2='x' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='xx' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5='x' /* VARSTRING(512) meta=512 nullable=1 is_null=0 */
# at 2030
#191014 12:57:59 server id 1  end_log_pos 2061 CRC32 0x094213f5     Xid = 165
COMMIT/*!*/;
....
....

可以看到 binlog里记录了『修改前这一行各个字段内容和修改后各个字段内容』。这个特点也会被某些同步MySQL数据的组件利用，下边再介绍。

显然，ROW 格式相比 STATEMENT 记录的信息更加丰富，但也会增大磁盘存储等。

主从同步机制

详细流程

备库B 跟主库A 之间维持了一个长连接。主库A 内部有一个线程，专门用于服务备库B 的这个长连接。一个事务日志同步的完整过程是这样的：

在备库B 上通过change master命令，设置主库A 的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
在备库B 上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。
主库A 校验完用户名、密码后，开始按照备库B 传过来的位置，从本地读取binlog，发给B。
备库B 拿到binlog后，写到本地文件，称为中转日志（relay log）。
sql_thread读取中转日志，解析出日志里的命令，并执行。

同步机制

这个图表达同步机制更简化：

半同步机制

上述的同步，是从库异步拉binlog。那有这样一个问题，当主库某个事务binlog写完宕机，未来得及同步到从库，当换主时就发生事务丢失。

解决的办法就是半同步机制：所谓半同步复制，是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后，才返回Commit操作成功给客户端；

如此半同步就保证了事务成功提交后至少有两份日志记录，一份在主库Binlog上，另一份在从库的Relay Log上，从而进一步保证数据完整性；

半同步复制很大程度取决于主从网络RTT（往返时延），以插件 semisync_master/semisync_slave 形式存在。

binlog 应用

一个很常见的分布式组件场景，MySQL 数据库的数据如何和Redis 缓存中的数据保持一致？

在应用程序中实现，可能存在MySQL 写成功，Redis写失败。当然重试或者异步任务等实现方式是可以的，有一个方案就是利用消费binlog方式。

如上介绍，当binlog是 Row 格式时，会记录一行变更的前后信息，这就是我们需要的。

思路

MySQL主实例采用ROW 格式binlog
起一个服务充当是MySQL 从节点，这样它能消费同步过来的binlog
解析ROW格式，获取到每一行的前后变化的数据。
把数据封装起来，使用。

阿里开源了canal 组件就是做这个的。

图示

参考

《MySQL 技术内幕 InnoDB存储引擎》
《MySQL 45讲》
alibaba/canal