Mysql的复制模式

• 复制功能之所以能够正常工作，是因为写入二进制日志的事件是从主库获取，然后在从库上回放的。根据事件的类型，事件以不同的格式被记录在二进制日志中，一般有三种复制模式，基于row复制模式，基于statement的复制模式以及mixed复制模式

• 什么是基于row的复制模式

  • 使用row格式的二进制日志时，主库会将产生的事件（一组事件）写入二进制日志，以事件来表示数据的变更。将这些表示数据变更的事件复制到从库，然后在从库中应用这些事件，把主库数据同步到从库，这称为基于row（行）的复制，简称为RBR
  • 关键行为：将ROW变更转成Binlog二进制日志在从库重放

• 什么是基于statement的复制模式

  • 使用statement格式的二进制日志时，主库会将SQL语句文本写入二进制日志。在主库上执行的SQL语句，然后将主库的SQL变更在从库重放，这称为基于statement（语句）的复制，简称为SBR
  • 关键行为：SQL转成Binlog二进制日志在从库重放

• 什么是mixed复制模式

  • 默认采用基于SQL的复制，一旦发现基于SQL的无法精确的复制时，就会采用基于行的复制

• 基于row的优缺点在哪里？

  • 优势：

    • 可以正确复制所有数据的变更，这是最安全的复制格式

  • 劣势：

    • 生成更多的二进制日志数据，因为基于row的复制会将每行数据的变更都写入二进制日志。利用二进制日志进行备份和恢复的时间也会更长。此外，二进制日志的文件锁也会因为需要更长的时间来写入数据而被持有更久的时间，这可能会影响数据库的并发能
    • 无法直接看到从库中执行的语句，但是可以使用mysqlbinlog工具

• 基于statement的优劣势在哪里？

  • 优势：

    • 写入日志文件的数据较少。当更新或删除操作涉及多行时，可以大大减少存储空间，在利用二进制日志备份与恢复数据时也可以快速完成。
    • 日志文件中包含所有的数据变更的原始语句，可用于数据库审计。

  • 劣势：

    • 一些执行结果不确定的DML语句，不能使用基于statement的复制，否则可能会造成主从库的数据不一致

    • DML语句中，使用不带ORDER BY的LIMIT子句时，由于在主从库之间执行的排序结果可能不同，所以执行结果是不确定的

    • 使用statement格式的日志时，一些内置的函数无法正确复制，如下：

      • LOAD_FILE()
      • UUID()
      • UUID_SHORT()
      • USER()
      • FOUND_ROWS()
      • SYSDATE()（主库和从库都使用--sysdate-is-now选项启动时适用）
      • GET_LOCK()
      • IS_FREE_LOCK()
      • IS_USED_LOCK()
      • MASTER_POS_WAIT()
      • RAND()
      • RELEASE_LOCK()
      • SLEEP()
      • VERSION()

• 关于安全与不安全数据复制

  • 什么情况下是不安全复制：“安全”是指是否可以使用基于statement的格式（这里指的是在二进制日志文件中实际记录的内容为statement格式，不是指设置系统变量binlog_format = statement）正确复制语句，如果能正确复制，则认为语句是安全的，否则就认为是不安全的

  • Mysql对不安全执行的处理

    • 使用mixed格式的日志时，被视为不安全的语句在记录到二进制日志时会自动转换为row格式，被视为安全的语句在记录到二进制日志时会使用statement格式
    • 使用statement格式的日志时，对标记为不安全的语句会生成警告，甚至拒绝执行，被标记安全的语句则被正常记录