二进制日志包含描述数据库更改的“事件”，例如表创建操作或表数据的更改。它还包含可能导致更改的语句的事件（例如，匹配不到任何行的DELETE），除非使用基于行的日志记录。二进制日志还包含每条更新数据的语句花费时间的信息。二进制日志有两个重要用途：The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

二进制日志不用于像 SELECT 或 SHOW 这样不修改数据的语句。要记录所有语句（例如，为了识别问题查询），请使用通用查询日志。参见 第 7.4.3 节，“通用查询日志”。The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 7.4.3, “The General Query Log”.

启用二进制日志的服务器运行会使性能略有下降。然而，二进制日志在设置复制和进行恢复操作方面的好处通常超过了这一小幅度的性能下降。Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

二进制日志对意外中断具有弹性。只有完整的事件或事务会被记录或读回。The binary log is resilient to unexpected halts. Only complete events or transactions are logged or read back.

写入二进制日志的语句中的密码会被服务器重写，以防止其以明文形式出现。另请参见 第 8.1.2.3 节，“密码与日志记录”。Passwords in statements written to the binary log are rewritten by the server not to occur literally in plain text. See also Section 8.1.2.3, “Passwords and Logging”.

MySQL 二进制日志文件和中继日志文件可以被加密，这有助于保护这些文件及其中可能包含的敏感数据，防止被外部攻击者滥用，同时也防止存储它们的操作系统用户未授权查看。您可以通过将 MySQL 服务器的 binlog_encryption 系统变量设置为 ON 来启用加密。更多信息，请参见 第 19.3.2 节，“加密二进制日志文件和中继日志文件”。MySQL binary log files and relay log files can be encrypted, helping to protect these files and the potentially sensitive data contained in them from being misused by outside attackers, and also from unauthorized viewing by users of the operating system where they are stored. You enable encryption on a MySQL server by setting the binlog_encryption system variable to ON. For more information, see Section 19.3.2, “Encrypting Binary Log Files and Relay Log Files”.

下面的讨论描述了一些影响二进制日志操作的服务器选项和变量。有关完整列表，请参见 第 19.1.6.4 节，“二进制日志选项和变量”。The following discussion describes some of the server options and variables that affect the operation of binary logging. For a complete list, see Section 19.1.6.4, “Binary Logging Options and Variables”.

二进制日志默认启用（log_bin 系统变量设置为 ON）。例外情况是，如果您使用 mysqld 通过调用 --initialize 或 --initialize-insecure 选项手动初始化数据目录，此时二进制日志默认是禁用的，但可以通过指定 --log-bin 选项来启用。Binary logging is enabled by default (the log_bin system variable is set to ON). The exception is if you use mysqld to initialize the data directory manually by invoking it with the --initialize or --initialize-insecure option, when binary logging is disabled by default, but can be enabled by specifying the --log-bin option.

要禁用二进制日志，可以在启动时指定--skip-log-bin或--disable-log-bin选项。如果指定了这些选项中的任何一个，同时也指定了--log-bin，则后面指定的选项优先。To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup. If either of these options is specified and --log-bin is also specified, the option specified later takes precedence.

--log-replica-updates 和 --replica-preserve-commit-order 选项需要二进制日志。如果禁用二进制日志，则要么省略这些选项，要么指定 --log-replica-updates=OFF 和 --skip-replica-preserve-commit-order。当指定 --skip-log-bin 或 --disable-log-bin 时，MySQL 默认禁用这些选项。如果将 --log-replica-updates 或 --replica-preserve-commit-order 与 --skip-log-bin 或 --disable-log-bin 一起指定，会发出警告或错误信息。The --log-replica-updates and --replica-preserve-commit-order options require binary logging. If you disable binary logging, either omit these options, or specify --log-replica-updates=OFF and --skip-replica-preserve-commit-order. MySQL disables these options by default when --skip-log-bin or --disable-log-bin is specified. If you specify --log-replica-updates or --replica-preserve-commit-order together with --skip-log-bin or --disable-log-bin, a warning or error message is issued.

--log-bin[=base_name] 选项用于指定二进制日志文件的基本名称。如果您没有提供 --log-bin 选项，MySQL 会使用 binlog 作为二进制日志文件的默认基本名称。为了与早期版本兼容，如果您提供了没有字符串或为空字符串的 --log-bin 选项，则基本名称默认为 host_name-bin，使用主机名。建议您指定一个基本名称，以便如果主机名更改，您可以轻松继续使用相同的二进制日志文件名（参见 B.3.7 节，“MySQL 中的已知问题”）。如果您在日志名称中提供扩展名（例如 --log-bin=base_name.extension），扩展名会被静默删除并忽略。The --log-bin[=base_name] option is used to specify the base name for binary log files. If you do not supply the --log-bin option, MySQL uses binlog as the default base name for the binary log files. For compatibility with earlier releases, if you supply the --log-bin option with no string or with an empty string, the base name defaults to host_name-bin, using the name of the host machine. It is recommended that you specify a base name, so that if the host name changes, you can easily continue to use the same binary log file names (see Section B.3.7, “Known Issues in MySQL”). If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored.

mysqld 会在二进制日志基名后附加一个数字扩展名，以生成二进制日志文件名。每次服务器创建新日志文件时，数字都会增加，从而创建一个有序的文件系列。每当发生以下任何事件时，服务器都会在该系列中创建一个新文件：mysqld appends a numeric extension to the binary log base name to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time any of the following events occurs:

如果您使用大事务，二进制日志文件可能会超过 max_binlog_size，因为事务会一次性写入文件，从不在文件之间拆分。A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files.

为了跟踪哪些二进制日志文件已被使用，mysqld 还会创建一个二进制日志索引文件，其中包含二进制日志文件的名称。默认情况下，该索引文件与二进制日志文件具有相同的基本名称，扩展名为 '.index'。您可以使用 --log-bin-index[=file_name] 选项更改二进制日志索引文件的名称。当 mysqld 正在运行时，不应手动编辑此文件；这样做会使 mysqld 混淆。To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of the binary log files. By default, this has the same base name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

术语 “二进制日志文件” 通常指包含数据库事件的单个编号文件。术语 “二进制日志” 统称为编号的二进制日志文件集合以及索引文件。The term “binary log file” generally denotes an individual numbered file containing database events. The term “binary log” collectively denotes the set of numbered binary log files plus the index file.

二进制日志文件和二进制日志索引文件的默认位置是数据目录。你可以使用 --log-bin 选项指定一个备用位置，通过在基本名称前添加绝对路径名来指定不同的目录。当服务器从跟踪已使用二进制日志文件的二进制日志索引文件中读取条目时，它会检查条目是否包含相对路径。如果包含，相对路径部分将被使用 --log-bin 选项设置的绝对路径替换。在二进制日志索引文件中记录的绝对路径保持不变；在这种情况下，必须手动编辑索引文件以启用新路径或多条路径。二进制日志文件的基本名称和任何指定的路径可通过 log_bin_basename 系统变量访问。The default location for binary log files and the binary log index file is the data directory. You can use the --log-bin option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory. When the server reads an entry from the binary log index file, which tracks the binary log files that have been used, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the --log-bin option. An absolute path recorded in the binary log index file remains unchanged; in such a case, the index file must be edited manually to enable a new path or paths to be used. The binary log file base name and any specified path are available as the log_bin_basename system variable.

当启用二进制日志时，可以使用默认的服务器 ID 启动服务器，但如果未使用 server_id 系统变量显式指定服务器 ID，将会显示一条信息性消息。对于用于复制拓扑的服务器，必须为每个服务器指定一个唯一的非零服务器 ID。The server can be started with the default server ID when binary logging is enabled, but an informational message is issued if you do not specify a server ID explicitly using the server_id system variable. For servers that are used in a replication topology, you must specify a unique nonzero server ID for each server.

具有足够权限设置受限制会话系统变量的客户端（参见 第 7.1.9.1 节，“系统变量权限”）可以使用 SET sql_log_bin=OFF 语句禁用自身语句的二进制日志记录。A client that has privileges sufficient to set restricted session system variables (see Section 7.1.9.1, “System Variable Privileges”) can disable binary logging of its own statements by using a SET sql_log_bin=OFF statement.

默认情况下，服务器会记录事件的长度以及事件本身，并使用这些信息来验证事件是否正确写入。您还可以通过设置 binlog_checksum 系统变量来让服务器为事件写入校验和。在从二进制日志读取时，源默认使用事件长度，但可以通过启用 source_verify_checksum 来使用可用的校验和。副本上的复制 I/O（接收器）线程也会验证从源接收到的事件。您可以通过启用 replica_sql_verify_checksum 来使复制 SQL（应用程序）线程在从中继日志读取时使用可用的校验和。By default, the server logs the length of the event as well as the event itself and uses this to verify that the event was written correctly. You can also cause the server to write checksums for the events by setting the binlog_checksum system variable. When reading back from the binary log, the source uses the event length by default, but can be made to use checksums if available by enabling source_verify_checksum. The replication I/O (receiver) thread on the replica also verifies events received from the source. You can cause the replication SQL (applier) thread to use checksums if available when reading from the relay log by enabling replica_sql_verify_checksum.

二进制日志中记录事件的格式取决于二进制日志格式。支持三种格式类型：基于行的日志记录、基于语句的日志记录和混合日志记录。使用的二进制日志格式取决于 MySQL 版本。有关日志格式的说明，请参见 第 7.4.4.1 节，“二进制日志格式”。The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported: row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MySQL version. For descriptions of the logging formats, see Section 7.4.4.1, “Binary Logging Formats”.

服务器以与处理 --replicate-do-db 和 --replicate-ignore-db 选项相同的方式评估 --binlog-do-db 和 --binlog-ignore-db 选项。有关此操作的详细信息，请参阅 第 19.2.5.1 节，“评估数据库级别复制和二进制日志选项”。The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options. For information about how this is done, see Section 19.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”.

副本在默认情况下启用 log_replica_updates 启动，这意味着副本会将从源接收到的任何数据修改写入自身的二进制日志。必须启用二进制日志，此设置才有效（请参阅 第 19.1.6.3 节，“副本服务器选项和变量”）。此设置使副本能够作为其他副本的源。A replica is started with log_replica_updates enabled by default, meaning that the replica writes to its own binary log any data modifications that are received from the source. The binary log must be enabled for this setting to work (see Section 19.1.6.3, “Replica Server Options and Variables”). This setting enables the replica to act as a source to other replicas.

您可以使用 RESET BINARY LOGS AND GTIDS 语句删除所有二进制日志文件，或者使用 PURGE BINARY LOGS 删除其中的一部分。请参阅 第 15.7.8.6 节，“RESET 语句” 和 第 15.4.1.1 节，“PURGE BINARY LOGS 语句”。You can delete all binary log files with the RESET BINARY LOGS AND GTIDS statement, or a subset of them with PURGE BINARY LOGS. See Section 15.7.8.6, “RESET Statement”, and Section 15.4.1.1, “PURGE BINARY LOGS Statement”.

如果您使用的是 MySQL 复制，除非确认没有副本仍需要使用它们，否则不应删除源上的旧二进制日志文件。例如，如果您的副本从未落后超过三天，您可以每天在源上执行 mysqladmin flush-logs binary，然后删除任何超过三天的日志。您可以手动删除这些文件，但更建议使用 PURGE BINARY LOGS，该命令还能安全地更新二进制日志索引文件（并且可以带日期参数）。参见 第 15.4.1.1 节，“PURGE BINARY LOGS 语句”。If you are using MySQL Replication, you should not delete old binary log files on the source until you are sure that no replica still needs to use them. For example, if your replicas never run more than three days behind, once a day you can execute mysqladmin flush-logs binary on the source and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use PURGE BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument). See Section 15.4.1.1, “PURGE BINARY LOGS Statement”.

您可以使用 mysqlbinlog 工具显示二进制日志文件的内容。当您希望为恢复操作重新处理日志中的语句时，这会很有用。例如，您可以按如下方式从二进制日志更新 MySQL 服务器：You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log for a recovery operation. For example, you can update a MySQL server from the binary log as follows:

$> mysqlbinlog log_file | mysql -h server_name

mysqlbinlog 也可以用来显示副本上的中继日志文件内容，因为它们是使用与二进制日志文件相同的格式写入的。有关 mysqlbinlog 工具及其使用方法的更多信息，请参见 第 6.6.9 节，“mysqlbinlog — 用于处理二进制日志文件的工具”。有关二进制日志和恢复操作的更多信息，请参见 第 9.5 节，“时点（增量）恢复”。mysqlbinlog also can be used to display the contents of the relay log file on a replica, because they are written using the same format as binary log files. For more information on the mysqlbinlog utility and how to use it, see Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, see Section 9.5, “Point-in-Time (Incremental) Recovery”.

二进制日志记录在语句或事务完成后立即进行，但在释放任何锁或执行任何提交之前。这确保日志按提交顺序记录。Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

对非事务性表的更新在执行后立即存储到二进制日志中。Updates to nontransactional tables are stored in the binary log immediately after execution.

在未提交的事务中，所有更新（UPDATE、DELETE 或 INSERT）会更改事务表，如 InnoDB 表，将会被缓存，直到服务器收到 COMMIT 语句。此时，mysqld 会在执行 COMMIT 前，把整个事务写入二进制日志。Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

对非事务性表的修改不能回滚。如果回滚的事务包括对非事务性表的修改，则整个事务会在末尾使用ROLLBACK语句记录，以确保这些表的修改被复制。Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

当处理事务的线程启动时，它会分配一个binlog_cache_size缓冲区来缓存语句。如果某条语句大于此大小，线程会打开一个临时文件来存储事务。线程结束时，临时文件会被删除。如果服务器启用了二进制日志加密，临时文件会被加密。When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends. If binary log encryption is active on the server, the temporary file is encrypted.

Binlog_cache_use 状态变量显示使用此缓冲区（可能还有临时文件）来存储语句的事务数量。Binlog_cache_disk_use 状态变量显示实际必须使用临时文件的事务数量。这两个变量可以用来将 binlog_cache_size 调整到足够大的值，从而避免使用临时文件。The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The Binlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.

max_binlog_cache_size 系统变量（默认值 4GB，也是最大值）可用于限制用于缓存多语句事务的总大小。如果事务大于此字节数，则事务失败并回滚。最小值为 4096。The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

如果您正在使用二进制日志和基于行的日志记录，CREATE ... SELECT 或 INSERT ... SELECT 语句中的并发插入将被转换为普通插入。这是为了确保在备份操作期间通过应用日志可以重新创建表的完全副本。如果您使用基于语句的日志记录，原始语句将被写入日志。If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. If you are using statement-based logging, the original statement is written to the log.

二进制日志格式存在一些已知的限制，这些限制可能影响从备份恢复。请参见 第 19.5.1 节，“复制功能和问题”。The binary log format has some known limitations that can affect recovery from backups. See Section 19.5.1, “Replication Features and Issues”.

存储程序的二进制日志记录按 第 27.7 节，“存储程序二进制日志” 中描述的方式进行。Binary logging for stored programs is done as described in Section 27.7, “Stored Program Binary Logging”.

请注意，由于复制方面的增强，MySQL 8.4 的二进制日志格式不同于以往版本的 MySQL。请参见 第 19.5.2 节，“MySQL 版本间的复制兼容性”。Note that the binary log format differs in MySQL 8.4 from previous versions of MySQL, due to enhancements in replication. See Section 19.5.2, “Replication Compatibility Between MySQL Versions”.

如果服务器无法写入二进制日志、刷新二进制日志文件或将二进制日志同步到磁盘，则复制源服务器上的二进制日志可能会变得不一致，副本可能会与源不同步。binlog_error_action 系统变量控制在遇到此类二进制日志错误时采取的操作。If the server is unable to write to the binary log, flush binary log files, or synchronize the binary log to disk, the binary log on the replication source server can become inconsistent and replicas can lose synchronization with the source. The binlog_error_action system variable controls the action taken if an error of this type is encountered with the binary log.

默认情况下，二进制日志在每次写入时同步到磁盘（sync_binlog=1）。如果没有启用 sync_binlog，并且操作系统或机器（不仅仅是 MySQL 服务器）发生崩溃，二进制日志的最后几条语句可能会丢失。为了防止这种情况，请启用 sync_binlog 系统变量，以便在每个 N 提交组之后将二进制日志同步到磁盘。参见 第 7.1.8 节，“服务器系统变量”。sync_binlog 的最安全值是 1（默认值），但这也是最慢的。By default, the binary log is synchronized to disk at each write (sync_binlog=1). If sync_binlog was not enabled, and the operating system or machine (not only the MySQL server) crashed, there is a chance that the last statements of the binary log could be lost. To prevent this, enable the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. See Section 7.1.8, “Server System Variables”. The safest value for sync_binlog is 1 (the default), but this is also the slowest.

在较早的 MySQL 版本中，如果发生崩溃，即使将 sync_binlog 设置为 1，表内容与二进制日志内容之间也可能出现不一致。例如，如果您使用 InnoDB 表，并且 MySQL 服务器处理一个 COMMIT 语句，它会按顺序将许多已准备事务写入二进制日志，同步二进制日志，然后将事务提交到 InnoDB。如果服务器在这两个操作之间意外退出，该事务将在重启时被 InnoDB 回滚，但仍然存在于二进制日志中。以前的版本通过启用 InnoDB 对 XA 事务的两阶段提交支持来解决此类问题。在 MySQL 8.4 中，InnoDB 对 XA 事务的两阶段提交支持始终启用。In earlier MySQL releases, there was a chance of inconsistency between the table content and binary log content if a crash occurred, even with sync_binlog set to 1. For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits the transaction into InnoDB. If the server unexpectedly exited between those two operations, the transaction would be rolled back by InnoDB at restart but still exist in the binary log. Such an issue was resolved in previous releases by enabling InnoDB support for two-phase commit in XA transactions. In MySQL 8.4, InnoDB support for two-phase commit in XA transactions is always enabled.

InnoDB 对 XA 事务中两阶段提交的支持确保了二进制日志和 InnoDB 数据文件的同步。然而，MySQL 服务器还应该配置为在提交事务前将二进制日志和 InnoDB 日志同步到磁盘。InnoDB 日志默认是同步的，而 sync_binlog=1 确保二进制日志被同步。在 XA 事务中 InnoDB 对两阶段提交的隐式支持以及 sync_binlog=1 的效果是，在崩溃后重启时，在回滚事务之后，MySQL 服务器会扫描最新的二进制日志文件，收集事务 xid 值并计算二进制日志文件中的最后有效位置。随后，MySQL 服务器会告诉 InnoDB 完成任何已成功写入二进制日志的预处理事务，并将二进制日志截断到最后有效位置。这确保了二进制日志反映 InnoDB 表的准确数据，因此复制副本保持与源数据库的同步，因为它不会接收到已被回滚的语句。InnoDB support for two-phase commit in XA transactions ensures that the binary log and InnoDB data files are synchronized. However, the MySQL server should also be configured to synchronize the binary log and the InnoDB logs to disk before committing the transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 ensures the binary log is synchronized. The effect of implicit InnoDB support for two-phase commit in XA transactions and sync_binlog=1 is that at restart after a crash, after doing a rollback of transactions, the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to complete any prepared transactions that were successfully written to the to the binary log, and truncates the binary log to the last valid position. This ensures that the binary log reflects the exact data of InnoDB tables, and therefore the replica remains in synchrony with the source because it does not receive a statement which has been rolled back.

如果 MySQL 服务器在崩溃恢复时发现二进制日志比预期的要短，则至少缺少一个成功提交的 InnoDB 事务。如果设置了 sync_binlog=1 并且磁盘/文件系统在请求时能够真正执行同步（某些系统不会），这种情况不应该发生，因此服务器会打印错误消息 The binary log file_name is shorter than its expected size。在这种情况下，该二进制日志是不正确的，应该从源数据的新快照重新启动复制。If the MySQL server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed InnoDB transaction. This should not happen if sync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not), so the server prints an error message The binary log file_name is shorter than its expected size. In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the source's data.

以下系统变量的会话值会被写入二进制日志，并在解析二进制日志时被副本遵循：The session values of the following system variables are written to the binary log and honored by the replica when parsing the binary log: