MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MariaDB 中 binlog 格式选择的策略

2022-08-311.4k 阅读

MariaDB 中 binlog 格式简介

在 MariaDB 数据库中，二进制日志（binlog）起着至关重要的作用。它记录了数据库的更改操作，用于数据备份、恢复以及主从复制等场景。而 binlog 有多种格式，每种格式都有其特点和适用场景。

目前 MariaDB 支持的 binlog 格式主要有以下几种：

  • Statement 格式：在这种格式下，binlog 记录的是实际执行的 SQL 语句。例如，如果执行 UPDATE users SET age = age + 1 WHERE city = 'Beijing';，binlog 就会记录这条 SQL 语句。
  • Row 格式：Row 格式下，binlog 记录的是数据行的变化。假设上述 UPDATE 语句，它会记录每一行 users 表中符合条件的行在更新前后的具体数据。
  • Mixed 格式：这是一种混合了 Statement 和 Row 格式的方式。MariaDB 会根据具体的 SQL 语句来自动选择使用 Statement 还是 Row 格式记录到 binlog 中。

Statement 格式的特点

  1. 优点
    • 日志量小：因为只记录 SQL 语句，相比于记录每一行数据变化的 Row 格式，日志量会显著减少。这对于存储空间有限或者网络带宽受限的场景非常友好。例如，一个批量插入 1000 条数据的 INSERT 语句，在 Statement 格式下，binlog 只需要记录这一条 INSERT 语句，而在 Row 格式下，需要记录 1000 条数据行的插入信息。
    • 兼容性好：对于一些不涉及数据行具体修改逻辑，只涉及数据库结构变更等操作，如 CREATE TABLE、ALTER TABLE 等语句，Statement 格式能够很好地记录和重现。而且，这种格式对于不同版本的 MariaDB 兼容性较高，不会因为版本差异导致日志记录和回放出现问题。
  2. 缺点
    • 数据一致性风险：由于记录的是 SQL 语句，在主从复制场景下，如果主库和从库的环境稍有差异，比如函数版本、系统变量设置不同等，可能会导致从库执行相同的 SQL 语句得到与主库不同的结果。例如，主库和从库的时区设置不同，而 SQL 语句中又涉及到日期时间函数，就可能出现数据不一致的情况。
    • 不确定性函数问题：一些具有不确定性的函数，如 NOW()、RAND() 等，在主从复制时可能会出现问题。假设主库执行 INSERT INTO logs (time) VALUES (NOW());，记录到 binlog 中的是这条语句。当从库回放时，NOW() 函数返回的是从库当前的时间，而不是主库执行时的时间，这就导致数据不一致。

Statement 格式示例

下面通过实际的代码示例来展示 Statement 格式下 binlog 的记录情况。 首先，确保 MariaDB 配置为使用 Statement 格式。在 my.cnf 文件中添加或修改以下配置：

[mysqld]
binlog_format = STATEMENT

重启 MariaDB 服务使配置生效。

创建一个测试表并插入数据：

CREATE TABLE test_statement (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO test_statement (name) VALUES ('Alice'), ('Bob');

查看 binlog 文件内容（可以使用 mysqlbinlog 工具）：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

在 binlog 文件中，会看到类似如下记录：

# at 4
#190920 15:22:06 server id 1  end_log_pos 123 CRC32 0x7f08567c  Start: binlog v 4, server v 10.4.8-MariaDB-log created 190920 15:22:06
ROLLBACK/*!*/;
# at 123
#190920 15:22:06 server id 1  end_log_pos 219 CRC32 0x89f79c52  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1568964126/*!*/;
CREATE TABLE test_statement (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
)/*!*/;
# at 219
#190920 15:22:06 server id 1  end_log_pos 309 CRC32 0x2c376686  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1568964126/*!*/;
INSERT INTO test_statement (name) VALUES ('Alice'), ('Bob')/*!*/;

可以清晰地看到，binlog 记录的是实际执行的 SQL 语句。

Row 格式的特点

  1. 优点
    • 数据一致性高：由于记录的是数据行的具体变化，在主从复制时，从库直接应用这些数据行的变化，避免了因为环境差异导致的执行结果不一致问题。无论主从库的函数版本、系统变量等如何设置，只要数据行变化记录准确，从库就能重现主库的修改。
    • 适合复杂数据操作：对于一些复杂的 SQL 操作，如涉及到触发器、存储过程等对数据行进行复杂处理的场景，Row 格式能够准确记录数据的变化，确保主从复制的准确性。
  2. 缺点
    • 日志量大：每一行数据的变化都要记录，导致 binlog 文件会迅速增大。特别是在高并发写入的场景下，大量的数据行变化记录会占用大量的存储空间，同时也会增加网络传输负担，影响主从复制的性能。
    • 恢复性能影响：在进行数据恢复时，由于需要应用每一行数据的变化记录，恢复过程可能会比 Statement 格式慢。而且如果 binlog 文件过大，恢复所需的时间和资源也会相应增加。

Row 格式示例

同样，先配置 MariaDB 使用 Row 格式。在 my.cnf 文件中修改配置：

[mysqld]
binlog_format = ROW

重启 MariaDB 服务。

执行与 Statement 格式示例相同的创建表和插入数据操作：

CREATE TABLE test_row (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO test_row (name) VALUES ('Charlie'), ('David');

查看 binlog 文件：

mysqlbinlog /var/lib/mysql/mysql-bin.000002

在 binlog 文件中会看到如下类似记录（这里简化展示，实际会包含更多详细的行数据变化信息）：

# at 4
#190920 15:30:00 server id 1  end_log_pos 123 CRC32 0x7f08567c  Start: binlog v 4, server v 10.4.8-MariaDB-log created 190920 15:30:00
ROLLBACK/*!*/;
# at 123
#190920 15:30:00 server id 1  end_log_pos 234 CRC32 0x89f79c52  Table_map: `test`.`test_row` mapped to number 123
# at 234
#190920 15:30:00 server id 1  end_log_pos 345 CRC32 0x2c376686  Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `test`.`test_row`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='Charlie' /* STRING(50) meta=50 nullable=1 is_null=0 */
# at 345
#190920 15:30:00 server id 1  end_log_pos 456 CRC32 0x3c476686  Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `test`.`test_row`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='David' /* STRING(50) meta=50 nullable=1 is_null=0 */

可以看到，binlog 详细记录了每一行数据的插入变化。

Mixed 格式的特点

  1. 优点
    • 灵活性高：它结合了 Statement 和 Row 格式的优点，对于大部分不涉及不确定性函数和环境敏感操作的 SQL 语句，使用 Statement 格式记录，以减少日志量；而对于可能导致主从数据不一致的操作，如包含不确定性函数的语句、涉及触发器等复杂操作，自动切换到 Row 格式记录。这样既保证了一定的日志精简性，又确保了数据一致性。
    • 适应性强：能够根据不同的 SQL 操作类型，动态选择最合适的记录格式，对于各种复杂的数据库应用场景都有较好的适应性。在既有简单的结构变更操作，又有复杂的数据处理操作的混合场景下，Mixed 格式能够较好地平衡日志量和数据一致性。
  2. 缺点
    • 选择逻辑复杂：虽然 MariaDB 会自动选择记录格式，但这种选择逻辑相对复杂。对于开发和运维人员来说，在排查问题时，理解和分析 binlog 记录可能会有一定难度，因为需要同时考虑两种格式的记录特点和切换规则。
    • 潜在的兼容性问题：在不同版本的 MariaDB 中，混合格式的自动选择逻辑可能会有所变化。这可能导致在版本升级或降级过程中，出现 binlog 记录和回放的兼容性问题，需要特别关注和测试。

Mixed 格式示例

配置 MariaDB 使用 Mixed 格式，在 my.cnf 文件中设置：

[mysqld]
binlog_format = MIXED

重启服务后，执行如下操作：

CREATE TABLE test_mixed (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT
);
INSERT INTO test_mixed (value) VALUES (FLOOR(RAND() * 10));

查看 binlog 文件：

mysqlbinlog /var/lib/mysql/mysql-bin.000003

在 binlog 文件中，对于 CREATE TABLE 语句，可能以 Statement 格式记录：

# at 4
#190920 15:35:00 server id 1  end_log_pos 123 CRC32 0x7f08567c  Start: binlog v 4, server v 10.4.8-MariaDB-log created 190920 15:35:00
ROLLBACK/*!*/;
# at 123
#190920 15:35:00 server id 1  end_log_pos 219 CRC32 0x89f79c52  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1568964900/*!*/;
CREATE TABLE test_mixed (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT
)/*!*/;

而对于包含 RAND() 不确定性函数的 INSERT 语句，可能以 Row 格式记录（简化展示）：

# at 219
#190920 15:35:00 server id 1  end_log_pos 324 CRC32 0x2c376686  Table_map: `test`.`test_mixed` mapped to number 124
# at 324
#190920 15:35:00 server id 1  end_log_pos 435 CRC32 0x3c476686  Write_rows: table id 124 flags: STMT_END_F
### INSERT INTO `test`.`test_mixed`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=5 /* INT meta=0 nullable=0 is_null=0 */

binlog 格式选择策略

  1. 考虑应用场景

    • 简单业务场景：如果应用主要进行简单的增删改查操作，且对主从复制的一致性要求不是特别高，如一些小型的网站后台数据库，Statement 格式可能是一个不错的选择。它能够有效减少日志量，降低存储和网络传输压力，提高数据库的整体性能。
    • 复杂业务场景：对于涉及复杂业务逻辑，如大量使用触发器、存储过程，以及对数据一致性要求极高的金融、电商等应用场景，Row 格式更为合适。虽然日志量较大，但能确保主从复制的数据准确性，避免因数据不一致导致的业务问题。
    • 混合业务场景：当应用既有简单的结构变更操作，又有复杂的数据处理操作时，Mixed 格式可以很好地适应。它根据 SQL 语句的特点自动选择合适的记录格式，在保证数据一致性的同时，尽量减少日志量。

  2. 结合性能需求

    • 性能优先：如果数据库服务器的存储空间充足，网络带宽较高，且对数据库写入性能要求极高，Row 格式可能会因为日志记录开销较大而影响性能。此时，Statement 格式或 Mixed 格式可能更有利于提高写入性能，因为它们的日志记录开销相对较小。
    • 数据一致性优先：如果数据的准确性和一致性是首要考虑因素，即使在性能略有牺牲的情况下，也应选择 Row 格式或 Mixed 格式（对于可能导致不一致的操作会切换到 Row 格式）。在一些对数据质量要求严格的行业，如医疗、财务等，数据一致性的重要性远远高于性能的微小提升。

  3. 关注版本兼容性 在进行 binlog 格式选择时，还需要考虑 MariaDB 的版本兼容性。不同版本的 MariaDB 在 binlog 格式的实现和特性上可能会有差异。特别是在进行版本升级或降级操作时，要确保选择的 binlog 格式在目标版本中能够正常工作。例如，某些旧版本的 MariaDB 对 Mixed 格式的支持可能存在一些限制或 bug，在升级到新版本后，可能需要重新评估 binlog 格式的选择。同时，对于一些新特性相关的 binlog 格式改进，如在新版本中对 Row 格式日志压缩的优化，也可以根据实际需求进行考虑和选择。

  4. 监控与调整 无论选择哪种 binlog 格式，都需要对数据库进行持续的监控。通过监控 binlog 文件的大小增长速度、主从复制的延迟情况、数据库的性能指标等，来判断当前选择的 binlog 格式是否合适。如果发现 binlog 文件增长过快，导致存储空间不足，而应用对数据一致性要求并非绝对严格，可以考虑切换到 Statement 格式或调整 Mixed 格式的相关参数，减少 Row 格式记录的比例。反之，如果发现主从复制出现数据不一致问题，可能需要将 binlog 格式切换为 Row 格式或检查 Mixed 格式下自动选择逻辑是否正确。

总结 binlog 格式选择要点

在 MariaDB 中选择合适的 binlog 格式是一个综合考虑多方面因素的过程。要充分了解应用场景的特点、性能需求、版本兼容性等，通过合理的选择和持续的监控调整，确保数据库既能高效运行，又能保证数据的一致性和可靠性。同时，随着业务的发展和数据库环境的变化，可能需要适时重新评估和调整 binlog 格式，以适应新的需求。

希望通过以上对 MariaDB 中 binlog 格式的详细介绍和选择策略的分析，能帮助开发和运维人员在实际工作中做出更明智的决策，优化数据库的性能和数据管理。在实际应用中，还需要结合具体的业务场景和数据库环境进行深入的测试和验证，以确保选择的 binlog 格式能够满足长期稳定运行的需求。