深入理解MySQL——MySQL复制的原理

1. 基于语句的复制

在 MySQL 5.0及之前的版本中只支持基于语句的复制（也称为逻辑复制），这在数据库领域是很少见的。基于语句的复制模式下，主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的 SQL再执行一遍。这种方式既有好处，也有缺点。

最明显的好处是实现相当简单。理论上讲，简单地记录和执行这些语句，能够让主备保持同步。另一个好处是二进制日志里的事件更加紧凑，所以相对而言，基于语句的模式不会使用太多带宽。一条更新好几兆数据的语句在二进制日志里可能只占几十个字节。另外 mysqlbinlog 工具是使用基于语句的日志的最佳工具。

但事实上基于语句的方式可能并不如其看起来那么便利。因为主库上的数据更新除了执行的语句外，可能还依赖于其他因素。例如，同一条 SQL 在主库和备库上执行的时间可能稍微或很不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。例如，使用CURRENT USER() 函数的语句。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。

另外一个问题是更新必须是串行的。这需要更多的锁——有时候要特别关注这一点。另外不是所有的存储引擎都支持这种复制模式。尽管这些存储引擎是包括在 MySQL 5.5及之前版本中发行的。可以在 MySQL 手册中找到基于语句的复制存在的限制的完整列表。

2. 基于行的复制

MySQL 5.1开始支持基于行的复制，这种方式会将实际数据记录在二进制日志中，跟其他数据库的实现比较相像。它有其自身的一些优点和缺点。最大的好处是可以正确地复制每一行。一些语句可以被更加有效地复制。

由于无须重放更新主库数据的查询，使用基于行的复制模式能够更高效地复制数据。重放一些查询的代价可能会很高。例如，下面有一个查询将数据从一个大表中汇总到小表;

mysql>INSERT INTO summary_table(col1,col2,sum_col3)
->SELECT col1,col2,sum(col3)
->FROM enormous table
->GROUP BY col1,col2;

想象一下，如果表 enormous table 的列 coll和 col2有三种组合，这个查询可能在源表上扫描多次，但最终只在目标表上产生三行数据。但使用基于行的复制方式，在备库上开销会小很多。这种情况下，基于行的复制模式更加高效。
但在另一方面，下面这条语句使用基于语句的复制方式代价会小很多∶

mysql>UPDATE enormous_table SET col1 = 0;

由于这条语句做了全表更新，使用基干行的复制开销会很大。因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大。并且会给主库上记录日志和复制增加额外的负载，更慢的日志记录则会降低并发度。

由于没有哪种模式对所有情况都是完美的，MySQL 能够在这两种复制模式间动态切换。默认情况下使用的是基于语句的复制方式，但如果发现语句无法被正确地复制，就切换到基于行的复制模式。还可以根据需要来设置会话级别的变量 binlog format，控制二进制日志格式。

对于基于行的复制模式，很难进行时间点恢复，但这并非不可能。稍后讲到的日志服务器对此会有帮助。

3. 基于行或基于语句∶哪种更优

我们已经讨论了这两种复制模式的优点和缺点，那么在实际应用中哪种方式更优呢?

理论上基于行的复制模式整体上更优，并且在实际应用中也适用于大多数场景。但这种方式太新了以至于没有将一些特殊的功能加入到其中来满足数据库管理员的操作需求。因此一些人直到现在还没有开始使用。以下详细地阐述两种方式的优点和缺点，以帮助你决定哪种方式更合适。

3.1 基于语句的复制模式的优点

当主备的模式不同时，逻辑复制能够在多种情况下工作。例如，在主备上的表的定义不同但数据类型相兼容、列的顺序不同等情况。这样就很容易先在备库上修改schema，然后将其提升为主库，减少停机时间。基于语句的复制方式一般允许更灵活的操作。基于语句的方式执行复制的过程基本上就是执行SQL语句。这意味着所有在服务器上发生的变事都以一种容易理解的方式运行。这样当出现问题时可以很好地去定位。

3.2 基于语句的复制模式的缺点

很多情况下通过基于语句的模式无法正确复制，几乎每一个安装的备库都会至少碰到一次。事实上对于存储过程，触发器以及其他的一些语句的复制在5.0和5.1的一系列版本中存在大量的 Bug。这些语句的复制的方式已经被修改了很多次，以使其更好地工作。简单地说;如果正在使用触发器或者存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会碰到复制问题。

3.3 基于行的复制模式的优点

几乎没有基于行的复制模式无法处理的场景。对于所有的SQL 构造、触发器、存储过程等都能正确执行。只是当你试图做一些诸如在备库修改表的 schema 这样的事情时才可能导致复制失败。

这种方式同样可能减少锁的使用，因为它并不要求这种强串行化是可重复的。基于行的复制模式会记录数据变更，因此在二进制日志中记录的都是实际上在主库上发生了变化的数据。你不需要查看一条语句夫猜测它到底修改了哪些数据。在某种程度上，该模式能够更加清楚地知道服务器上发生了哪些更改。并目有一个更好的数据变更记录。另外在一些情况下基于行的二进制日志还会记录发生改变之前的数据，因此这可能有利于某些数据恢复。

在很多情况下，由于无须像基于语句的复制那样需要为查询建立执行计划并执行查询，因此基于行的复制占用更少的CPU。

最后，在某些情况下，基于行的复制能够帮助更快地找到并解决数据不一致的情况。举个例子，如果是使用基于语句的复制模式，在备库更新一个不存在的记录时不会失败，但在基于行的复制模式下则会报错并停止复制。

3.4 基于行的复制模式的缺点

由于语句并没有在日志里记录，因此无法判断执行了哪些SQL，除了需要知道行的变化外，这在很多情况下也很重要（这可能在未来的 MySQL 版本中被修复）。

使用一种完全不同的方式在备库进行数据变更——而不是执行SQL。事实上，执行基于行的变化的过程就像一个黑盒子，你无法知道服务器正在做什么。并且没有很好的文档和解释。因此当出现问题时， 可能很难找到问题所在。例如，若备库使用一个效率低下的方式去寻找行记录并更新，你无法观察到这一点。

如果有多层的复制服务器，并且所有的都被配置成基干行的复制模式。 当会话级别的变量@@binlog_format被设置成 STATEMENT时，所执行的语句在源服务器上被记录为基于语句的模式，但第一层的备库可能将其记录成行模式，并传递给其他层的备库。也就是说你期望的基于语句的日志在复制拓扑中将会被切换到基于行的模式。基于行的日志无法处理诸如在备库修改表的 schema 这样的情况，而基于语句的日志可以。

在某些情况下，例如找不到要修改的行时，基于行的复制可能会导致复制停止，而基于语句的复制则不会。这也可以认为是基于行的复制的一个优点。该行为可以通过 slave_exec_mode来进行配置。