MySQL8.0大师之路：第11章：MySQL服务器管理-11.12 MySQL 二进制日志

JeeStudy

二进制日志
    二进制日志包含描述数据库更改（例如表创建操作或表数据更改）的“事件”。它还包含针对可能进行了更改的语句的事件（例如，不匹配任何行的DELETE），除非使用基于行的日志记录。二进制日志还包含有关每个语句花费该更新数据多长时间的信息。二进制日志具有两个重要目的：
    •对于复制，主复制服务器（master replication server）上的二进制日志提供了要发送到从属服务器（slave servers）的数据更改的记录。主服务器将其二进制日志中包含的事件发送到其从属服务器，从属服务器执行这些事件以对主服务器进行相同的数据更改。
    •某些数据恢复操作需要使用二进制日志。备份被还原后，将重新执行在执行备份后记录的二进制日志中的事件。这些事件使数据库从备份开始就保持最新状态。


    二进制日志不用于诸如SELECT或SHOW之类的不修改数据的语句。要记录所有语句（例如，确定问题查询），请使用常规查询日志（general query log）。


    运行启用了二进制日志记录的服务器会使性能稍微降低。但是，二进制日志在使您能够设置复制和进行还原操作方面的好处通常超过了这种较小的性能下降。
    二进制日志可以抵抗意外的宕机。仅记录或读取完整的事件或事务。
    服务器将重写写在二进制日志中的语句中的密码，以使它们不会以纯文本的形式出现。
    从MySQL 8.0.14开始，可以对二进制日志文件和中继日志文件（binary log files and relay log files）进行加密，从而有助于保护这些文件以及其中包含的潜在敏感数据，使其免受外部攻击者的滥用，并防止未经授权的用户查看其被存储所在的操作系统上的信息。
    通过将binlog_encryption系统变量设置为ON，可以在MySQL服务器上启用加密。



    以下讨论描述了一些影响二进制日志记录操作的服务器选项和变量。
    默认情况下启用二进制日志记录（log_bin系统变量设置为ON）。例外是如果使用mysqld通过--initialize或--initialize-insecure选项手动调用初始化数据目录，则默认情况下禁用了二进制日志记录，但可以通过指定--log-bin选项启用它来手动初始化数据目录。
    要禁用二进制日志记录，可以在启动时指定--skip-log-bin或--disable-log-bin选项。如果指定了这些选项中的任何一个，并且还指定了--log-bin，则后面指定的选项优先。
    --log-slave-updates和--slave-preserve-commit-order选项需要二进制日志记录。如果禁用二进制日志记录，请忽略这些选项，或者指定--log-slave-updates = OFF和--skip-slave-preserve-commit-order。当指定--skiplog-bin或--disable-log-bin时，MySQL默认禁用这些选项。如果将--log-slave-updates或--slavepreserve- commit-order与--skip-log-bin或--disable-log-bin一起指定，则会发出警告或错误消息。
    --log-bin [= base_name]选项用于指定二进制日志文件的基本名称。如果不提供--log-bin选项，则MySQL使用binlog作为二进制日志文件的默认基本名称。为了与早期版本兼容，如果提供的--log-bin选项不带字符串或带空字符串，则基本名称默认为host_name-bin，使用主机名。建议您指定一个基本名称，以便在主机名更改时，可以轻松地继续使用相同的二进制日志文件名。如果在日志名称中提供扩展名（例如--log-bin = base_name.extension），则该扩展名将被静默删除并忽略。
    mysqld在二进制日志基本名称后附加数字扩展名，以生成二进制日志文件名。每次服务器创建新的日志文件时，该数目都会增加，从而创建了有序的文件系列。每次发生以下任何事件，服务器都会在系列中创建一个新文件：
    •服务器启动或重新启动
    •服务器刷新日志。
    •当前日志文件的大小达到max_binlog_size。
    如果您使用的是大型事务，则二进制日志文件可能会变得比max_binlog_size大，这是因为将事务一次性写入文件中，而不会对文件进行分割。
    为了跟踪使用了哪些二进制日志文件，mysqld还创建了一个二进制日志索引文件，其中包含二进制日志文件的名称。默认情况下，该名称与二进制日志文件具有相同的基本名称，扩展名为“ .index”。您可以使用--log-binindex [= file_name]选项更改二进制日志索引文件的名称。当mysqld运行时，您不应该手动编辑该文件。这样做会使mysqld混淆。
    术语“二进制日志文件”通常表示包含数据库事件的单独编号文件。术语“二进制日志”统称为带编号的二进制日志文件加上索引文件的集合。
    二进制日志文件和二进制日志索引文件的默认位置是数据目录。您可以使用--log-bin选项指定替代位置，方法是在基本名称中添加前导绝对路径名以指定其他目录。当服务器从二进制日志索引文件中读取一个条目（该文件跟踪已使用的二进制日志文件）时，它将检查该条目是否包含相对路径。如果是这样，则将路径的相对部分替换为使用--log-bin选项设置的绝对路径。

    二进制日志索引文件中记录的绝对路径保持不变；在这种情况下，必须手动编辑索引文件以启用新路径。二进制日志文件的基本名称和任何指定的路径都可以作为log_bin_basename系统变量使用。
    在MySQL 5.7中，启用二进制日志记录时必须指定服务器ID，否则服务器将无法启动。在MySQL 8.0中，默认情况下server_id系统变量设置为1。启用二进制日志记录后，可以使用该默认ID启动服务器，但是如果您未使用server_id系统变量显式指定服务器ID，则会发出参考消息。对于复制拓扑中使用的服务器，必须为每个服务器指定一个唯一的非零服务器ID。
     具有足以设置受限制的会话系统变量的特权的客户端可以使用SET sql_log_bin = OFF语句禁用其自身语句的二进制记录。
    默认情况下，服务器记录事件的长度以及事件本身，并使用它来验证事件是否正确写入。您还可以通过设置binlog_checksum系统变量来使服务器编写事件的校验和。从二进制日志回读时，默认情况下，主服务器使用事件长度，但可以通过启用master_verify_checksum系统变量使它使用校验和（如果可用）。从属I / O线程还验证从主控接收到的事件。通过启用slave_sql_verify_checksum系统变量，可以使从属SQL线程在从中继日志中读取时使用校验和（如果可用）。
    二进制日志中记录的事件的格式取决于二进制日志格式。支持三种格式类型：基于行的日志，基于语句的日志和基于混合的日志。使用的二进制日志记录格式取决于MySQL版本。
    服务器以与--replicate-do-db和--replicate-ignore-db选项相同的方式评估--binlog-do-db和--binlog-ignore-db选项。
    复制从属服务器是在默认情况下启用时启用log_slave_updates系统变量启动的，这意味着从属服务器会将从复制主服务器收到的所有数据修改写入其自己的二进制日志。必须启用二进制日志才能使此设置生效。此设置使从属服务器可以充当链式复制中其他从属服务器的主服务器。
    您可以使用RESET MASTER语句删除所有二进制日志文件，或者使用PURGE BINARY LOGS删除其中的一部分。
    如果使用复制，则在确保没有从属仍需要使用它们之前，不应删除主服务器上的旧二进制日志文件。例如，如果您的从属服务器从未运行超过三天，则可以每天一次在主服务器上执行mysqladmin flush-logs，然后删除超过三天的任何日志。您可以手动删除文件，但是最好使用PURGE BINARY LOGS，它还可以安全地为您更新二进制日志索引文件（并且可以使用date参数）。
    您可以使用mysqlbinlog实用程序显示二进制日志文件的内容。当您要重新处理日志中的语句以进行恢复操作时，此功能很有用。例如，可以从二进制日志更新MySQL服务器，如下所示：
    shell> mysqlbinlog log_file | mysql -h server_name
    mysqlbinlog也可用于显示复制从属中继日志文件（relay log file）的内容，因为它们是使用与二进制日志文件相同的格式编写的。
    在语句或事务完成之后但在释放任何锁或完成任何提交之前，立即执行二进制日志记录。这样可以确保日志以提交顺序记录。
    非事务表的更新在执行后立即存储在二进制日志中。
    在未提交的事务中，将缓存所有更改事务表（例如InnoDB表）的更新（UPDATE，DELETE或INSERT），直到服务器接收到COMMIT语句为止。此时，mysqld在执行COMMIT之前将整个事务写入二进制日志。
    对非事务表的修改不能回滚。如果回滚的事务包括对非事务表的修改，则整个事务将在末尾使用ROLLBACK语句记录下来，以确保复制对这些表的修改。
    当处理事务的线程启动时，它将为缓冲区语句分配binlog_cache_size缓冲区。如果语句大于此值，则线程将打开一个临时文件来存储事务。当线程结束时，将删除临时文件。从MySQL 8.0.17开始，如果服务器上的二进制日志加密处于活动状态，则对临时文件进行加密。
    Binlog_cache_use状态变量显示使用该缓冲区（可能还有一个临时文件）存储语句的事务数。Binlog_cache_disk_use状态变量显示实际上有多少个事务必须使用临时文件。这两个变量可用于将binlog_cache_size调整为足够大的值，从而避免使用临时文件。
    可以使用max_binlog_cache_size系统变量（默认为4GB，这也是最大值）来限制用于缓存多语句事务的总大小。如果事务大于此多个字节，它将失败并回滚。最小值为4096。
    如果您使用的是基于二进制日志和基于行的日志记录，则将并发插入转换为CREATE ... SELECT或INSERT ... SELECT语句的普通插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。如果您使用基于语句的日志记录，则原始语句将被写入日志。
    请注意，由于复制功能的增强，MySQL 8.0中的二进制日志格式与以前的MySQL版本有所不同。
    如果服务器无法写入二进制日志，刷新二进制日志文件或将二进制日志同步到磁盘，则复制主数据库上的二进制日志可能会变得不一致，并且复制从数据库可能会与主数据库失去同步。如果二进制日志遇到这种类型的错误，则binlog_error_action系统变量控制所采取的操作。
    •默认设置ABORT_SERVER使服务器停止二进制日志记录并关闭。此时，您可以确定并纠正错误原因。重新启动后，恢复将继续进行，就像服务器意外中止一样。
    •设置IGNORE_ERROR提供了与旧版本MySQL的向后兼容性。使用此设置，服务器将继续进行中的事务并记录错误，然后停止二进制日志记录，但继续执行更新。此时，您可以确定并纠正错误原因。要恢复二进制日志记录，必须再次启用log_bin，这需要重新启动服务器。
    仅在需要向后兼容性且二进制日志在此MySQL服务器实例上为非必需时才使用此选项。例如，您可能只将二进制日志用于服务器的间歇审核或调试，而不将其用于从服务器复制或将其用于时间点还原操作。


    默认情况下，每次写入时（sync_binlog = 1），二进制日志都会同步到磁盘。如果未启用sync_binlog，并且操作系统或计算机（不仅是MySQL服务器）崩溃，则二进制日志的最后一条语句可能会丢失。为防止这种情况，请在每N个提交组之后启用sync_binlog系统变量以将二进制日志同步到磁盘。sync_binlog的最安全值为1（默认值），但这也是最慢的。
    在早期的MySQL版本中，即使发生了崩溃，即使将sync_binlog设置为1，也有可能导致表内容与二进制日志内容之间出现不一致的情况。例如，如果您使用的是InnoDB表，而MySQL服务器处理的是COMMIT语句，依次将许多准备好的事务写入二进制日志，同步二进制日志，然后将事务提交到InnoDB。如果服务器在这两个操作之间崩溃，则事务将在重新启动时由InnoDB回滚，但仍存在于二进制日志中。在以前的版本中，通过为XA事务中的两阶段提交启用InnoDB支持，解决了此问题。在8.0.0及更高版本中，始终启用对XA事务中的两阶段提交的InnoDB支持。
    InnoDB对XA事务中的两阶段提交的支持确保了二进制日志和InnoDB数据文件是同步的。但是，还应将MySQL服务器配置为在提交事务之前将二进制日志和InnoDB日志同步到磁盘。 InnoDB日志默认情况下是同步的，并且sync_binlog = 1确保二进制日志已同步。 InnoDB对XA事务中两阶段提交的隐式支持以及sync_binlog = 1的作用是，崩溃后重启时，在回滚事务之后，MySQL服务器将扫描最新的二进制日志文件以收集事务xid值并计算二进制日志文件中的最后一个有效位置。然后，MySQL服务器告诉InnoDB完成已成功写入二进制日志的所有准备好的事务，并将二进制日志截断到最后一个有效位置。这样可以确保二进制日志反映了InnoDB表的确切数据，因此从属服务器与主服务器保持同步，因为它没有收到已回滚的语句。
    如果MySQL服务器在崩溃恢复时发现二进制日志短于原先的二进制日志，则它至少缺少一个成功提交的InnoDB事务。如果sync_binlog = 1且磁盘/文件系统在被要求进行实际同步时（某些情况没有这样做），则不会发生这种情况，因此服务器将输出一条错误消息。二进制日志file_name短于其预期大小。在这种情况下，此二进制日志不正确，应从主数据的新快照重新开始复制​​。
    解析二进制日志时，以下系统变量的会话值将写入二进制日志并由复制从属服务器遵循：

• sql_mode (except that the NO_DIR_IN_CREATE mode is not replicated; )
• foreign_key_checks
• unique_checks
• character_set_client
• collation_connection
• collation_database
• collation_server
• sql_auto_is_null