mysql中binlog_format模式与配置详细分析

这篇文章主要了MySQL中的binlog_format模式及其配置相关内容。MySQL的复制功能基于三种主要方式：基于SQL语句的复制（SBR）、基于行的复制（RBR）以及混合模式复制（MBR）。与此对应，binlog有三种格式：STATEMENT、ROW和MIXED。

我们来了解一下三种复制方式及其优缺点。

① SBR（Statement-Based Replication）：这种方式记录的是修改数据的SQL语句。它的优点在于节省了IO并提高了性能，因为不需要记录每一条SQL语句和每一行的数据变化。在某些特定情况下，如使用sleep()函数、last_insert_id()以及user-defined functions（udf）等，可能导致主从服务器间的数据不一致。

② RBR（Row-Based Replication）：这种模式下，不记录每条SQL语句的上下文信息，而是记录哪些数据被修改了以及修改后的样子。它不会出现某些特定情况下的存储过程、函数或触发器的调用和触发无法被正确复制的问题。它会产生大量的日志，尤其是在执行alter table操作时。

③ MBR（Mixed-Based Replication）：这是上述两种模式的结合。MySQL会根据执行的SQL语句选择日志保存方式。通常情况下，它使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作则使用ROW模式保存binlog。

接下来，我们来看一下如何配置binlog。在MySQL的配置文件myf中，可以通过以下选项进行配置：

binlog_format = MIXED：指定binlog的日志格式为MIXED。

log_bin = /data/mysql/mysql-bin.log：指定binlog日志文件的路径。

expire_logs_days = 7：设置binlog的过期清理时间为7天。

max_binlog_size = 100m：设置每个binlog日志文件的大小上限为100MB。

binlog_cache_size = 4m：设置binlog缓存的大小。

max_binlog_cache_size = 512m：设置最大binlog缓存大小为512MB。

对于执行的SQL语句中包含now()这样的时间函数，MYSQL在处理时会将其转化为unix_timestamp()的形式，并保证slave在同步时数据的准确性。对于功能性函数，slave能够完成相应的数据同步。但对于某些特定函数，如UDF函数，如果导致Slave无法识别，则会采用ROW格式存储这些Binlog以确保数据同步。

SBR模式有着其独特的优点如历史悠久、技术成熟、binlog文件较小等，并且binlog可以用于实时的还原和数据审核。它并非完美无缺，某些UPDATE语句特别是包含不确定操作的可能无法被完全复制。而RBR模式虽然解决了某些特定问题，但在日志量方面可能存在挑战。选择哪种模式需要根据具体的应用场景和需求进行权衡。MIXED模式则是一种折中方案，能够根据实际需求进行灵活配置。 MySQL 中的 binlog_format 模式与配置：深入理解复制的挑战与解决方案

在 MySQL 中，复制是一项关键功能，允许数据从一个服务器（主服务器）同步到一个或多个服务器（从服务器）。而 binlog_format 的设置对复制过程具有重要影响。本文将深入 MySQL 中的 binlog_format 模式及其配置，揭示其如何影响复制过程，并指出在特定情境下的最佳实践。

一、不确定因素 UDF 与复制问题

在使用一些特定的函数时，如 LOAD_FILE()、UUID()、USER()、FOUND_ROWS() 等，复制过程可能会遇到问题。这些函数在某些情况下可能导致复制的不一致性。特别是当调用具有不确定因素的 UDF（用户定义函数）时，复制过程可能出现问题。

二、RBR（行级复制）的优势与劣势

RBR（行级复制）是 MySQL 复制的一种模式，其优点和缺点如下：

优势：

1. 任何情况都可以被复制，对复制来说是最安全可靠的。

2. 和其他大多数数据库系统的复制技术一样，具有广泛兼容性。

3. 有主键的从表复制速度更快。

4. 执行 INSERT、UPDATE、DELETE 语句时锁更少。

5. 从服务器上采用多线程执行复制成为可能。

劣势：

1. binlog 体积大了很多。

2. 复杂的回滚时 binlog 中会包含大量数据。

3. 主服务器上执行 UPDATE 语句时，所有变化的记录都会写入 binlog 中，导致频繁发生 binlog 的并发写问题。

4. UDF 产生的大 BLOB 值会导致复制变慢。

5. 无法从 binlog 中直接看到复制的 SQL 语句。

值得注意的是，采用 RBR 模式后，可以解决很多原先出现的主键重复问题。这是因为 RBR 能够更精确地记录每一行的变化，从而避免了因部分语句的丢失或重复而导致的主键冲突。

三、配置建议与最佳实践

1. 数据表结构：确保主从服务器的数据表结构几乎一致，以避免复制过程中的错误。

2. 复杂语句的执行：执行复杂的语句时，需特别注意资源消耗和可能引发的错误。尽量避免在从服务器上执行复杂的语句，以减少资源消耗和潜在的错误风险。

3. binlog_format 的选择：根据实际需求选择合适的 binlog 格式。如果需要更精确的复制，并且可以接受较大的 binlog 体积，可以选择 RBR 模式。否则，可以考虑使用 SBR（语句级复制）模式。

4. UDF 的使用：在使用具有不确定性的 UDF 时，务必谨慎，确保其在复制过程中的稳定性和一致性。

5. 系统库 mysql 的变化处理：对于系统库 mysql 里的表变化，应根据具体情况处理。如果是直接操作表的情况，根据 binlog_format 的设定记录日志；如果是管理语句，则采用 SBR 模式记录。

本文详细分析了 MySQL 中的 binlog_format 模式与配置，了其在复制过程中的影响及最佳实践。深入了解这些模式和配置对于确保 MySQL 复制的准确性和效率至关重要。希望本文能对大家有所帮助，如有更多问题，欢迎留言交流讨论。