Loading...
墨滴

eZy

2021/10/19  阅读:39  主题:默认主题

(坑)MySQL 使用临时表给复制操作带来的风险

(坑)MySQL 使用临时表给复制操作带来的风险

目录


背景:入坑经历

关注此问题触发点

我想大部分公司线上 MySQL 实例都保持着 【binlog_format = row】 + 【transaction_isolation = read-committed】标准配置,包括本人最近新接触到的环境。对于 transaction_isolation 的配置没什么异议,配置为 RC 可以大大降低 next-key lock 出现机率,对 OLTP 型业务再合适不过。但对于 binlog_format 配置我还是有一定的疑虑:这么多年过去了 MySQL 为什么对这个参数的默认值设置越来越保守?从 5.6 的 statement 到 8.0 的 row:

5.6 默认配置

5.7 默认配置

8.0 默认配置

事出必有因,促使我关注 binlog_format 参数的触发点是因为我建议已有线上环境的 binlog_format 全部修改为 mixed。理由是线上偶尔会发生 delete 全表操作,且目标表无任何索引,表的数据量在 10w 级以上就有可能导致从库出现长时间延迟情况。而 binlog_format 修改为 mixed,将会话级事务隔离级别修改为 RR,binlog 中生成的就是 statement 格式的 delete event,这样主备对这个操作的处理效率几乎是一致的,可以大大降低备库出现延迟的概率。但于组内讨论期间,有同事提出了 binlog_format 修改为 mixed 的一个风险点:



但从其描述和提供的网页截图,没有任何有价值的信息。进一步到 MySQL 官网查看相关资料,也确实看到在复制环境中使用临时表的一些建议,这里我们重点截取 5.7 及 8.0 的说明:

5.6

图略过。

5.7

8.0

以上涉及 MySQL 5.7 及 8.0 版本相关官方文档注解:如果 binlog_format 配置为 row,则临时表相关的操作都不会记录到 binlog 中,未在截图中的内容大致总结为以下两点:

  • 如果 binlog_format 未配置为 row,那么在关闭从库前确保 Slave_open_temp_tables global status 为 0(即没有打开的临时表,注意:create temporary table... 后也会自动打开临时表)。
  • 如果主库确实有使用临时表的需求,那么建议相关表使用统一前缀,在备库使用 ignore 选项忽略这些临时表的复制操作。如:临时表统一使用 norep_ 前缀,备库配置:
    --replicate-wild-ignore-table=norep_%

至此,还是没有告诉我们使用临时表到底会出现什么问题(只是告诉我们 How to do!没有道明 Why?)。但据我对临时表的特性了解,大概能猜测出两个可能导致复制异常的点,接下来我们看一下临时表的特殊性。

临时表特殊性

相关测试涉及两套 MySQL 主从环境,版本分别为 5.7.9 及 5.7.34,binlog_format 默认配置为 mixed。

特殊性 1:命名空间为会话级,不同会话间可存在相同的表名称。

-- session A
mysql> use testdb;

mysql> select connection_id(),@@server_id;
+-----------------+-------------+
| connection_id() | @@server_id |
+-----------------+-------------+
|          192558 |        1001 |
+-----------------+-------------+
1 row in set (0.00 sec)

mysql> create temporary table tmp01(x intengine=innodb;
Query OK, 0 rows affected (0.00 sec)

-- session B
mysql> use testdb;

mysql> select connection_id(),@@server_id;
+-----------------+-------------+
| connection_id() | @@server_id |
+-----------------+-------------+
|          192550 |        1001 |
+-----------------+-------------+
1 row in set (0.00 sec)

-- 成功创建与 session A 同名临时表
mysql> create temporary table tmp01(x intengine=innodb;
Query OK, 0 rows affected (0.00 sec)

特殊性 2:默认生命周期与连接会话一致,连接断开后表自动 drop。

-- session A
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into tmp01 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> ^DBye

-- session C

root@84c936cfccad:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 192581
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into tmp01 values(1);
ERROR 1146 (42S02): Table 'testdb.tmp01' doesn't exist

就临时表以上两个特殊性表现,使用临时表到底会给复制环境带来哪些风险呢? 聪明的你可能已经想到了,但请接着阅读。相信我,可能和你预期的可些不一样。

复制环境中的风险演示

对于临时表会话级命名空间这个特性,大部分人可能认为:主库不同会话如果创建了同名的临时表,从库应用到对应 binlog position 时肯定会抛出表已存在的错误,从而导致复制异常中断,包括前面的官方文档建议,即:【在从库 ignore 临时表】多少也会造成一定的误导。实际情况真的如此吗?请看以下测试过程:

MySQL 版本:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.34-log |
+------------+
1 row in set (0.00 sec)
-- session C on slave
mysql> show full processlist;
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db     | Command | Time | State                                                  | Info                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
|  6 | root        | localhost | testdb | Query   |    0 | starting                                               | show full processlist |
|  7 | system user |           | NULL   | Connect |   19 | Slave has read all relay log; waiting for more updates | NULL                  |
|  8 | system user |           | NULL   | Connect |  176 | Waiting for master to send event                       | NULL                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

-- session A on master
mysql> create temporary table tmp01(x intengine=innodb;
Query OK, 0 rows affected (0.00 sec)


-- session C on slave
mysql> show full processlist;
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db     | Command | Time | State                                                  | Info                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
|  6 | root        | localhost | testdb | Query   |    0 | starting                                               | show full processlist |
|  7 | system user |           | NULL   | Connect |    2 | Slave has read all relay log; waiting for more updates | NULL                  |
|  8 | system user |           | NULL   | Connect |  180 | Waiting for master to send event                       | NULL                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

-- 注解:可以看到 sql_thread 对应会话状态的改变,即:Command 保持 Connect,Time 重新从 0 开始计时。


-- session C on slave
mysql> show full processlist;
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db     | Command | Time | State                                                  | Info                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
|  6 | root        | localhost | testdb | Query   |    0 | starting                                               | show full processlist |
|  7 | system user |           | NULL   | Connect |   32 | Slave has read all relay log; waiting for more updates | NULL                  |
|  8 | system user |           | NULL   | Connect |  210 | Waiting for master to send event                       | NULL                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)


-- session B on master
mysql> create temporary table tmp01(x intengine=innodb;
Query OK, 0 rows affected (0.00 sec)


-- session C on slave
mysql> show full processlist;
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db     | Command | Time | State                                                  | Info                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
|  6 | root        | localhost | testdb | Query   |    0 | starting                                               | show full processlist |
|  7 | system user |           | NULL   | Connect |    2 | Slave has read all relay log; waiting for more updates | NULL                  |
|  8 | system user |           | NULL   | Connect |  218 | Waiting for master to send event                       | NULL                  |
+----+-------------+-----------+--------+---------+------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)


-- 注解:sql_thread 对应会话状态变更同上一个场景。


-- session D on master
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create TableCREATE TABLE `t` (
  `x` int(11DEFAULT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> insert into t values(111);
Query OK, 1 row affected (0.00 sec)


-- session C on slave
mysql> select * from t;
.
.
.
|      111 |
+----------+
26 rows in set (0.00 sec)

-- 注解:主从同步状态正常。



-- session C on slave
mysql> show global status like '%temp%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Slave_open_temp_tables | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

-- session A on master
mysql> drop temporary table tmp01;
Query OK, 0 rows affected (0.00 sec)

-- session C on slave
mysql> show global status like '%temp%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Slave_open_temp_tables | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

-- 注解:针对 temporary table ddl binlog event 在从库正常 apply。

测试结果小结:从测试结果表现看,sql_thread 每应用完 binlog 中的一个 event 后,部分状态会重置,即:Command 保持为 Connect,Time 重新从 0 开始计数,ID 却不变。这说明 sql_thread 是一个长连接,但是同一个会话中 apply 同表名的 create temporary table sql 却没有报错,且无论是在 master 查看对应的 binlog event 还是查看从库的 relaylog events,都没有做类似 create temporary table if not exists 语句转换,令人很迷惑。


对于临时表另外一个特性,即:默认生命周期与连接会话一致,连接断开后自动 drop

试想以下流程:

step 1: 主库 - 创建临时表 -> 启动事务 -> 向临时表中写入数据 -> 提交。

step 2:从库 - 应用 relaylog 至创建临时表阶段 -> 临时表创建成功 -> 异常重启。

step 3:从库 - 继续应用 relaylog 中对临时表的写入 event。

按临时表特性及官方文档说明,step 3 必定会报错,真的是这样吗?我们接着看以下测试流程:

MySQL 版本:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.34-log |
+------------+
1 row in set (0.00 sec)
-- session C on slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- session A on master
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp02(x intengine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmp02 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             Fileon.000010
         Position386
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: e77be03a-d232-11eb-b90f-0242ac110002:1-163
1 row in set (0.00 sec)


mysql> show binlog events in 'on.000010';
+-----------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name  | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
+-----------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
| on.000010 |   4 | Format_desc    |      1001 |         123 | Server ver: 5.7.34-log, Binlog ver: 4                               |
| on.000010 | 123 | Previous_gtids |      1001 |         194 | e77be03a-d232-11eb-b90f-0242ac110002:1-162                          |
| on.000010 | 194 | Gtid           |      1001 |         259 | SET @@SESSION.GTID_NEXT= 'e77be03a-d232-11eb-b90f-0242ac110002:163' |
on.000010 | 259 | Query          |      1001 |         386 | use `testdb`create temporary table tmp02(x intengine=innodb     |
+-----------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)

-- 注解:mysql 5.7.34 版本中,压根不会记录对临时表的 dml 操作。

MySQL 版本:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)

操作主流程参考 MySQL 5.7.34 版本,区别在 slave 端:

-- session A on master
mysql> show binlog events in 'on.000007';
+-----------+-----+----------------+-----------+-------------+-----------------------------------------------------------------+
| Log_name  | Pos | Event_type     | Server_id | End_log_pos | Info                                                            |
+-----------+-----+----------------+-----------+-------------+-----------------------------------------------------------------+
| on.000007 |   4 | Format_desc    |      1001 |         123 | Server ver: 5.7.9-log, Binlog ver: 4                            |
| on.000007 | 123 | Previous_gtids |      1001 |         194 | b054174d-1c11-11ec-ab56-0242ac110003:1-7                        |
| on.000007 | 194 | Anonymous_Gtid |      1001 |         259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
on.000007 | 259 | Query          |      1001 |         386 | use `testdb`create temporary table tmp02(x intengine=innodb |
on.000007 | 386 | Anonymous_Gtid |      1001 |         451 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                            |
on.000007 | 451 | Query          |      1001 |         534 | BEGIN                                                           |
on.000007 | 534 | Query          |      1001 |         639 | use `testdb`insert into tmp02 values(1)                       |
on.000007 | 639 | Query          |      1001 |         714 | COMMIT                                                          |
+-----------+-----+----------------+-----------+-------------+-----------------------------------------------------------------+
8 rows in set (0.00 sec)

-- 注解:mysql 5.7.9 会记录对临时表 dml 操作。

-- session C on slave
-- 操作注解:启动复制,并指定截止 binlog position,这样复制流程正好停止在临时表创建后,
-- 对临时表 dml 操作前的中间点位(mysql 从库目前处于高危状态)。
mysql> start slave until MASTER_LOG_FILE = 'on.000007', MASTER_LOG_POS = 386;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show global status like '%temp%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Slave_open_temp_tables | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.206.67.198
                  Master_User: repl
                  Master_Port: 4406
                Connect_Retry: 60
              Master_Log_File: on.000007
          Read_Master_Log_Pos: 714
               Relay_Log_File: 060aa8a707db-relay-bin.000007
                Relay_Log_Pos: 545
        Relay_Master_Log_File: on.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 386
              Relay_Log_Space: 1500
              Until_Condition: Master
               Until_Log_File: on.000007
                Until_Log_Pos: 386
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1001
                  Master_UUID: b054174d-1c11-11ec-ab56-0242ac110003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: b054174d-1c11-11ec-ab56-0242ac110003:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)


-- 模拟从库异常重启:从库重启后执行以下操作:

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.206.67.198
                  Master_User: repl
                  Master_Port: 4406
                Connect_Retry: 60
              Master_Log_File: on.000007
          Read_Master_Log_Pos: 714
               Relay_Log_File: 060aa8a707db-relay-bin.000007
                Relay_Log_Pos: 545
        Relay_Master_Log_File: on.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'testdb.tmp02' doesn't exist' on query. Default database: 'testdb'. Query: 'insert into tmp02 values(1)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 386
              Relay_Log_Space: 2050
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error '
Table 'testdb.tmp02' doesn't exist' on query. Default database'testdb'Query'insert into tmp02 values(1)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1001
                  Master_UUID: b054174d-1c11-11ec-ab56-0242ac110003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 0
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 210927 08:26:08
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: b054174d-1c11-11ec-ab56-0242ac110003:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

-- 注解:抛出表不存在的错误,主从同步异常中断。

测试结果小结:MySQL 5.7.34 binlog 内不会记录对临时表的 dml 操作,MySQL 5.7.6 则会记录。所以,使用临时表时,可能会造成从库同步异常中断(特别是在低版本 MySQL 中)。

至此,我们看到临时表的使用确实会导致主从异常出现,那是不是可以不使用临时表呢?或者说临时表有什么优缺点?有没有必要去使用它?请接着往下看。

临时表有什么优缺点

临时表的大部分用途是用来存放中间结果集,使用场景是优化大查询为多个小查询,以提升查询效率(MySQL 的优化器 - CBO 目前还很弱)。

优点

  • 会话级命名空间,多个会话可以创建同名临时表,维护成本较低。
  • 不用显式删除,进一步降低维护成本,会话断开后自动 drop(oracle 数据库甚至有事务级控制子句 - on commit preserve/delete rows)。不过目前大部分应用都使用长链接,这个优点不突出。

缺点

  • 可能导致从库同步异常 - 很影响高可用性(高权重)。
  • 读写分离异常风险概率提升 - 很影响扩展性:会话级命名空间会导致 SQL 中涉及临时表的请求异常(高权重)。

应该使用临时表吗?

综合以上优缺点分析,现阶段在 MySQL 中使用临时表的做法,相当于丢了西瓜,捡了芝麻的行为,不建议这么做。

即使在 soar(小米开源 MySQL SQL 优化助手)中也不建议使用临时表:



综上分析,我们再回归到 binlog_format 这个参数。 是不是强制禁用临时表,binlog_format 就可以配置为 mixed 了呢?目前看未必,请看以下分析。

那 binlog_format 到底如何配置?

从以上测试结果看,只要不使用临时表或部署 MySQL 5.7.34 及以上版本,binlog_format 配置为 mixed,复制环境就会很健壮了吗?不!只能说排除了已知风险,未知的风险可能还有很多。为什么这么说?翻看官方文档,本人暂未找到 binlog_format 配置为 mixed 时的风险说明(Restrictions or Risks)。目前最稳妥的只有一条抄作业的路,即,不妨看看各公有云厂商的默认相关配置:

厂商 binlog_format 默认值 备注
阿里云 Row 详见 [参考资料] 对应链接
腾讯云 Row 详见 [参考资料] 对应链接
华为云 Row 详见 [参考资料] 对应链接
Google Cloud Row 详见 [参考资料] 对应链接
AWS Mixed? 详见 [参考资料] 对应链接

综上分析,binlog_format 现阶段还是老老实实的配置为 row 较稳妥。

当然,也可以区分对待,对于线上非核心系统我们可以配置为 mixed,踩坑用,也算为 MySQL ”公测”出一份力,这话别直接跟老板说哈😁。 对老板那边就说配置为 mixed 是为了使 binlog event 生成最小化,节省 IO 资源,省出的 IO 资源给核心系统用(好钢用在刀刃上),一切是为了成本及性能方面考量😁。

结论(包含避坑指南)

运维规范

  • 核心 MySQL 实例,binlog_format 必须指定为 row。

  • DML 涉及数据量较大时,可在 session 级别将事务隔离级别配置为 RR,binlog_format 配置为 mixed,这样 DML 对应 binlog event 格式大概率会是 statement,主从执行 DML 效率基本一致,大大降低从库出现高延迟概率,操作示例如下:

    mysql> set session transaction_isolation='REPEATABLE-READ';
    Query OK, 0 rows affected (0.00 sec)

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

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    -- big DML

开发规范

  • 尽量不要在复制环境中使用临时表,否则可能造成从库同步异常。有需要存放中间结果集的优化场景,可以创建普通表,这些表的创建和清理可以统一规范,会带来一定的维护成本。
  • 创建表时尽量指定主键,最坏的情况也要创建选择性较高的索引。否则在进行大批量 DML 操作时,大概率造成从库同步长时间延迟,如果业务为读写分离架构,那将对业务造成严重影响。
  • DML 涉及数据量较大时,必须提前咨询 DBA,以确认影响。

其他

  • binlog_format 非 row 配置时,MySQL 中使用临时表可能造成从库同步异常(MySQL 5.7 早期版本中)。
  • 多个会话同时创建同名临时表并不会造成备库同步异常(翻了 5.7 早期的几个版本对应 release notes,还是没有找到从库具体的处理机制)。

碎碎念(可跳过)

槽点

目前看 binlog_format 的两个可选值:statement、mixed 犹如摆放式的存在,除非对数据同步准确性要求不高,基本没有其他适配场景。依稀记得 MySQL 5.6 的一个小版本此参数对应默认值为 mixed,更早期版本默认值为 statement。从 binlog_format 这个参数默认值变迁,也可以看出 MySQL 在“公测”期间踩的坑越来越多,所以导致这个参数默认配置越来越保守,官方文档某些部分的说明有时也是有果而无因,让人一头雾水。

往好了想:

其实 binlog_format = mixed 的设计还是很前卫的(相较于 Oracle 的 redo log 物理日志实现)。初衷是:在不影响数据同步准确性的前提下(仅靠 MySQL Server 层无法保证,还需引擎层配合,如:InnoDB 引擎中 Gap lock 锁实现,具体说明会在后期文章中给出),使 binlog event 最小化生成,但是从目前看还是需要大家多贡献(踩坑),毕竟是白嫖,就算有坑也认了😁。我们最希望看到的是 binlog_format 参数的消失,binlog_format hard code 为 mixed(你见过 Oracle 类似 binlog_format 的参数吗?)。

至此,正文结束,MySQL 加油!!!

参考资料

- mysql 各版本 binlog_format 默认值
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format

- mysql 各版本复制与临时表说明
https://dev.mysql.com/doc/refman/5.6/en/replication-features-temptables.html
https://dev.mysql.com/doc/refman/5.7/en/replication-features-temptables.html
https://dev.mysql.com/doc/refman/8.0/en/replication-features-temptables.html

- 和公有云厂商 binlog_format 参数默认配置(间接反应)
https://help.aliyun.com/document_detail/67809.html
https://intl.cloud.tencent.com/zh/document/product/571/40035
https://support.huaweicloud.com/usermanual-rds/rds_05_0037.html
https://cloud.google.com/sql/docs/mysql/flags
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.MySQL.BinaryFormat.html

-- mysql 5.7 binlog_format 配置说明
https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

给自己挖坑(伏笔/预告)

  • Flink 同步 MySQL 锁问题分析案例(已填坑)。
  • 为什么劝你远离 InnoDB gap-lock(未填坑)。

作者:eZy_1990(ixdba/linora)

来源:公众号 - 悟空的数橘窟私房菜(ID:wkDB007)

关于作者:DBA 一枚,09 年开始接触数据库,早年间从事 Oracle DBA 一职,目前专注于开源数据库领域。混过很多家公司,也玩过很多种数据库。

其他说明:不保证全文没有任何错漏之处,如有不妥不吝赐教。

eZy

2021/10/19  阅读:39  主题:默认主题

作者介绍

eZy