本文问题

1. 二进制日志格式有几种，格式不同对复制有什么影响，不同格式的优缺点是什么？
2. 如何控制中继日志的名称和大小？
3. 中继日志在什么情况下创建/删除？

日志相关

二进制日志格式

主库在使用二进制日志记录数据库中的更改时，事件根据类型的不同，以不同格式记录在二进制日志中，可以通过binlog_format设置二进制日志格式。根据二进制日志格式的不同，复制也可以分为以下三种情况：

• 基于语句的复制SBR binlog_format=STATEMENT
  在这种情况下，主库将执行的SQL语句写入到二进制日志中，到从库上执行相同的语句
• 基于行的复制RBR binlog_format=ROW
  主库将表明表中的每一行如何进行更改的事件写入到二进制日志中，到从库上执行相同的事件
• 混合格式的复制 binlog_format=MIXED
  MySQL同时使用基于语句的记录和基于行的记录，根据更新操作的不同自动选择日志格式

不同二进制日志格式的优缺点

基于语句的复制

优点

• 写入日志文件的数据更少。当更新或删除多行时，可以减少日志文件占用的空间。
• 日志文件中包含了所有更新语句，可以用来审计数据库

缺点

• 不是所有更新语句都能使用基于语句的复制，一些语句生成的结果是不确定的，使用基于语句的复制来复制这些语句会导致主从不一致
• 主库和从库上表的定义必须完全相同
• INSERT ... SELECT需要更多的行锁
• 需要进行表扫描的UPDATE语句会锁定更多行
• 对于InnoDB，使用自增量的INSERT语句会阻塞其他不冲突的INSERT语句（取决于innodb_autoinc_lock_mode)
• 在存储过程中调用的NOW()函数的结果不一定相同

基于行的复制

优点

• 所有的数据库更改都能正确安全的应用到从库上
• 对于以下类型的语句，在主库上需要的行锁更少，可以提高并发性
  • INSERT ... SELECT
  • 包括AUTO_INCREMENT的INSERT
  • 带有WHERE子句，不使用太多索引或者不更改多行的UPDATE和DELETE
• 对于INSERT,UPDATE,DELETE，在从库上需要的行锁更少

缺点

• 写入日志文件的数据比较多，特别当使用UPDATE或者DELETE语句更新多行的时候。
• 不能在从库看到从主库接收并执行了哪些语句。
• 对于MyISAM存储引擎，当应用INSERT语句时需要更多的锁。

中继日志

中继日志和二进制日志一样，都是由一组日志文件和一个索引文件组成的。日志文件按数字编号，存储具体的日志内容。索引文件在存储当前正在使用的中继日志文件的名称
中继日志文件格式和二进制日志文件格式相同，都可以通过mysqlbinlog读取

中继日志的创建和删除

• 从库在以下情况下创建新的中继日志文件
  • 每次启动I/O线程的时候
  • 刷新日志的时候（FLUSH LOGS）
  • 中继日志超出最大文件大小的时候（max_relay_log_size）
• 中继日志在SQL线程执行完其中包含的所有事件并且不需要该文件后由SQL线程自动删除。中继日志不能手动删除。

相关选项

中继日志文件名称

• relay_log
  中继日志文件的基本名称，默认为host_name-relay-bin.，对于指定通道的中继日志，默认为名称为host_name-relay-bin-channel
• relay_log_index
  中继日志索引文件的名称。在MySQL5.7-中默认为host_name-relay-bin.index，对于指定通道的中继日志，默认为名称为host_name-relay-bin-channel.index。
  在MySQL8.0+以后，如果未设置relay_log_index但是设置了relay_log，使用relay_log的值作为索引文件的基本名称。此外，在MySQL8.0以后，中继日志的文件和索引名称不能与二进制日志的文件和索引名称相同。

中继日志文件大小

• max_relay_log_size
  默认值为0，表示根据max_binlog_size进行中继日志的刷新。可以动态修改，最大值是1073741824(1G)
• relay_log_space_limit
  全局变量，不能动态修改，默认值0，表示无限制。限制从库上所有中继日志文件的总大小。当磁盘空间有限时可以使用该选项。当中继日志达到限制后，I/O线程停止获取日志，等待SQL线程执行并删除不再使用的中继日志后再重新获取日志。

中继日志的删除和恢复

• relay_log_purged
  全局变量，可以动态修改，默认值ON，表示自动删除中继日志。当启用--relay-log_recovery时禁用中继日志的自动删除可能会导致数据不一致
• relay_log_recovery
  全局变量，不能动态修改，默认值OFF。是否启用中继日志的自动恢复。如果启用，在数据库启动后，将创建一个新的中继日志，初始化SQL线程的位置为新中继日志位置，初始化IO线程位置为SQL线程位置。不能在relay_log_purged=OFF的情况下启用该变量，会产生数据不一致的风险。

问题答案

1. 二进制日志格式有几种，格式不同对复制有什么影响，不同格式的优缺点是什么？
   3种，STATEMENT,ROW,MIXED。

• RBR
  • 优点：日志文件小，可以用来审计数据，恢复速度快
  • 缺点：有一些语句是不安全的，会导致主从不一致。主库和从库的表定义必须一致
• SBR
  • 优点：所有更改都能正确复制到从库上，需要的行锁更少，能提高并发性。
  • 缺点：日志文件大，大事务可能导致主从同步延迟。

3. 如何控制中继日志的名称和大小？

• 名称：relay_log和relay_log_index
• 大小：
  • 单独中继日志的大小限制：max_relay_log_size 默认为0，可以动态修改。等同于max_binlog_size
  • 所有中继日志的总大小限制：relay_log_space_limit 默认为0，不能动态修改。
    超过max_relay_log_size会创建一个新的中继日志文件，超过relay_log_space_limit会停止读取主库的二进制日志，等待SQL线程执行中继日志中的事件并删除中继日志

4. 中继日志在什么情况下创建/删除？
   创建：启动SLAVE IO线程时，FLUSH LOGS时，超出max_relay_log_size时
   删除：中继日志中的内容用完后SQL线程自动删除