高性能MYSQL:复制同步的问题和解决方案

一、复制的问题和解决方案

中断MySQL的复制并不是件难事。因为实现简单，配置相当容易，但也意味着有很多方式会导致复制停止，陷入混乱并中断。

（一）数据损坏或丢失的错误

由于各种各样的原因，MySQL 的复制并不能很好地从服务器崩溃、掉电、磁盘损坏、内存或网络错误中恢复。遇到这些问题时，几乎可以肯定都需要从某个点开始重启复制。

大部分由于非正常关机后导致的复制问题都是由于没有把数据及时地刷到磁盘。下面是意外关闭服务器时可能会碰到的情况。

1.主库意外关闭

如果没有设置主库的 sync_binlog 选项，就可能在崩溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库 I/O 线程因此也可能一直处于读不到尚未写入磁盘的事件的状态中。

当主库重新启动时，备库将重连到主库并再次尝试去读该事件，但主库会告诉备库没有这个二进制日志偏移量。二进制日志转储线程通常很快，因此这种情况并不经常发生。

解决这个问题的方法是指定备库从下一个二进制日志的开头读日志。但是一些日志事件将永久地丢失，建议使用 Percona Toolkit 中的 pt-table-checksum 工具来检查主备一致性，以便于修复。可以通过在主库开启 sync_binlog 来避免事件丢失。

即使开启了 sync_binlog，MyISAM 表的数据仍然可能在崩溃的时候损坏，对于 InnoDB 事务，如果 innodb_flush_log_at_trx_commit 没有设为 1，也可能丢失数据（但数据不会损坏）。

2.备库意外关闭

当备库在一次非计划中的关闭后重启时，会去读 master.info 文件以找到上次停止复制的位置。不幸的是，该文件并没有同步写到磁盘，文件中存储的信息可能是错误的。

备库可能会尝试重新执行一些二进制日志事件，这可能会导致唯一索引错误。除非能确定备库在哪里停止（通常不太可能），否则唯一的办法就是忽略那些错误。Percona Toolkit 中的 pt-slave-restart 工具可以帮助完成这一点。

如果使用的都是 InnoDB 表，可以在重启后观察 MySQL 错误日志。InnoDB 在恢复过程中会打印出它的恢复点的二进制日志坐标。可以使用这个值来决定备库指向主库的偏移量。

Percona Server 提供了一个新的特性，可以在恢复的过程中自动将这些信息提取出来，并更新 master.info 文件，从根本上使得复制能够协调好备库上的事务。MySQL 5.5 也提供了一些选项来控制如何将 master.info 和其他文件刷新到磁盘，这有助于减少这些问题。

除了由于 MySQL 非正常关闭导致的数据丢失外，磁盘上的二进制日志或中继日志文件损坏并不罕见。下面是一些更普遍的场景：

3.主库上的二进制日志损坏

如果主库上的二进制日志损坏，除了忽略损坏的位置外你别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开始一个新的日志文件，然后将备库指向该文件的开始位置。

也可以试着去发现损坏区域的结束位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该步骤，直到跳过所有损坏的事件。但如果有太多的损坏事件，这么做可能就没有意义了。

损坏的事件头会阻止服务器找到下一个事件。这种情况下，可能不得不手动地去找到下一个完好的事件。

4.备库上的中继日志损坏

如果主库上的日志是完好的，就可以通过 CHANGE MASTER TO 命令丢弃并重新获取损坏的事件。只需要将备库指向它当前正在复制的位置（Relay_Master_Log_File/Exec_Master_Log_Pos）。这会导致备库丢弃所有在磁盘上的中继日志。

就这一点而言，MySQL 5.5 做了一些改进，它能够在崩溃后自动重新获取中继日志。

5.二进制日志与 InnoDB 事务日志不同步

当主库崩溃时，InnoDB 可能将一个事务标记为已提交，此时该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志已经保存，否则没有任何办法恢复丢失的事务。

在 MySQL 5.0 版本可以设置 sync_binlog 选项来防止该问题，对于更早的 MySQL 4.1 可以设置 sync_binlog 和 safe_binlog 选项。

---

当一个二进制日志损坏时，能恢复多少数据取决于损坏的类型，有几种比较常见的类型:

（1）数据改变，但事件仍是有效的 SQL

不幸的是，MySQL 甚至无法察觉这种损坏。因此最好还是经常检查备库的数据是否正确。在 MySQL 未来的版本中可能会被修复。

（2）数据改变并且事件是无效的 SQL

这种情况可以通过 mysqlbinlog 提取出事件并看到一些错乱的数据，例如：

UPDATE tbl SET col?????????????????

可以通过增加偏移量的方式来尝试找到下一个事件，这样就可以只忽略这个损坏的事件。

（3）数据遗漏并且/或者事件的长度是错误的

这种情况下，mysqlbinlog 可能会发生错误退出或者直接崩溃，因为它无法读取事件，并且找不到下一个事件的开始位置。

某些事件已经损坏或被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的。同样的，这种情况下 mysqlbinlog 也起不了多少作用。

当损坏非常严重，通过 mysqlbinlog 已经无法获取日志事件时，就不得不进行一些十六进制的编辑或者通过一些繁琐的技术来找到日志事件的边界。这通常并不困难，因为有一些可辨识的标记会分割事件。

---

如下例所示，首先使用 mysqlbinlog 找到样例日志的日志事件偏移量：

$ mysqlbinlog mysql-bin.000113 | egrep '^# at '
# at 4
# at 98
# at 185
# at 277
# at 369
# at 447

一个找到日志偏移量的比较简单的方法是比较一下 strings 命令输出的偏移量：

$ strings -n 2 -t d mysql-bin.0001131 bncC'G25 5.0.38-Ubuntu_0ubuntu1.1-log99 C'G146 std156 test161 create table test(a int)186 C'G233 std243 test248 insert into test(a) values(1)278 C'G325 std335 test340 insert into test(a) values(2)370 C'G417 std427 test432 drop table test448 D'G474 mysql-bin.000114

有一些可辨别的模式可以帮助定位事件的开头，注意以 ' G 结尾的字符串在日志事件开头的一个字节后的位置。它们是固定长度的事件头的一部分。

这些值因服务器而异，因此结果也可能取决于解析的日志所在的服务器。简单地分析后应该能够从二进制日志中找到这些模式并找到下一个完整的日志事件偏移量。然后通过 mysqlbinlog 的 --start-position 选项来跳过损坏的事件，或者使用 CHANGE MASTER TO 命令的 MASTER_LOG_POS 参数。

（二）使用非事务型表

如果一切正常，基于语句的复制通常能够很好地处理非事务型表。但是当对非事务型表的更新发生错误时，例如查询在完成前被kill，就可能导致主库和备库的数据不一致。

例如，假设更新一个MyISAM表的100行数据，若查询更新到了其中50条时有人kill该查询，会发生什么呢？

一半的数据改变了，而另一半则没有，结果是复制必然不同步，因为该查询会在备库重放并更新完100行数据（MySQL随后会在主库上发现查询引起的错误，而备库上则没有报错，此后复制将会发生错误并中断）​。

如果使用的是MyISAM表，在关闭MySQL之前需要确保已经运行了STOP SLAVE，否则服务器在关闭时会kill所有正在运行的查询（包括没有完成的更新）​。

事务型存储引擎则没有这个问题。如果使用的是事务型表，失败的更新会在主库上回滚并且不会记录到二进制日志中。

（三）混合事务型和非事务型表

如果使用的是事务型存储引擎，只有在事务提交后才会将查询记录到二进制日志中。因此如果事务回滚，MySQL就不会记录这条查询，也就不会在备库上重放。

但是如果混合使用事务型和非事务型表，并且发生了一次回滚，MySQL能够回滚事务型表的更新，但非事务型表则被永久地更新了。只要不发生类似查询中途被kill这样的错误，这就不是问题：MySQL此时会记录该查询并记录一条ROLLBACK语句到日志中。

结果是同样的语句也在备库执行，所有的都很正常。这样效率会低一点，因为备库需要做一些工作并且最后再把它们丢弃掉。

但理论上能够保证主备的数据一致。目前看来一切很正常。但是如果备库发生死锁而主库没有也可能会导致问题。

事务型表的更新会被回滚，而非事务型表则无法回滚，此时备库和主库的数据是不一致的。防止该问题的唯一办法是避免混合使用事务型和非事务型表。

如果遇到这个问题，唯一的解决办法是忽略错误，并重新同步相关的表。

基于行的复制不会受这个问题的影响。因为它记录的是数据的更改，而不是SQL语句。如果一条语句改变了一个MyISAM表和一个InnoDB表的某些行，然后主库上发生了一次死锁，InnoDB表的更新会被回滚，而MyISAM表的更新仍会被记录到日志中并在备库重放。

（四）不确定语句

当使用基于语句的复制模式时，如果通过不确定的方式更改数据可能会导致主备不一致。

例如，一条带LIMIT的UPDATE语句更改的数据取决于查找行的顺序，除非能保证主库和备库上的顺序相同。

例如，若行根据主键排序，一条查询可能在主库和备库上更新不同的行，这些问题非常微妙并且很难注意到。所以一些人禁止对那些会更新数据的语句使用LIMIT。

另外一种不确定的行为是在一个拥有多个唯一索引的表上使用REPLACE或者INSERT IGNORE语句——MySQL在主库和备库上可能会选择不同的索引。另外还要注意那些涉及INFORMATION_SCHEMA表的语句。它们很容易在主库和备库上产生不一致，其结果也会不同。

最后，需要注意许多系统变量，例如@@server_id和@@hostname，在MySQL 5.1之前无法正确地复制。基于行的复制则没有上述限制。

（五）主库和备库使用不同的存储引擎

在备库上使用不同的存储引擎，有时候可以带来好处。

但是在一些场景下，当使用基于语句的复制方式时，如果备库使用了不同的存储引擎，则可能造成一条查询在主库和备库上的执行结果不同，例如不确定语句（如前一小节提到的）在主备库使用不同的存储引擎时更容易导致问题。

如果发现主库和备库的某些表已经不同步，除了检查更新这些表的查询外，还需要检查两台服务器上使用的存储引擎是否相同。

（六）备库发生数据改变

基于语句的复制方式前提是确保备库上有和主库相同的数据，因此不应该允许对备库数据的任何更改（比较好的办法是设置read_only选项）。假设有如下语句：

mysql> INSERT INTO table1 SELECT * FROM table2;

如果备库上table2的数据和主库上不同，该语句会导致table1的数据也会不一致。换句话说，数据不一致可能会在表之间传播。不仅仅是INSERT……SELECT查询，所有类型的查询都可能发生。有两种可能的结果：备库上发生重复索引键冲突错误或者根本不提示任何错误。如果能报告错误还好，起码能够提示你主备数据已经不一致。无法察觉的不一致可能会悄无声息地导致各种严重的问题。

唯一的解决办法就是重新从主库同步数据。

（七）不唯一的服务器ID

这种问题更加难以捉摸。如果不小心为两台备库设置了相同的服务器ID，看起来似乎没有什么问题，但如果查看错误日志，或者使用innotop查看主库，可能会看到一些古怪的信息。

在主库上，会发现两台备库中只有一台连接到主库（通常情况下所有的备库都会建立连接以等待随时进行复制）。在备库的错误日志中，则会发现反复的重连和连接断开信息，但不会提及被错误配置的服务器ID。

MySQL可能会缓慢地进行正确的复制，也可能无法进行正确复制，这取决于MySQL的版本，给定的备库可能会丢失二进制日志事件，或者重复执行事件，导致重复键错误（或者不可见的数据损坏）。也可能因为备库的互相竞争造成主库的负载升高。如果备库竞争非常激烈，会导致错误日志在很短的时间内急剧增大。

唯一的解决办法是小心设置备库的服务器ID。一个比较好的办法是创建一个主库到备库的服务器ID映射表，这样就可以跟踪到备库的ID信息。如果备库全在一个子网络内，可以将每台机器IP的后八位作为唯一ID。

（八）未定义的服务器ID

如果没有在my.cnf里定义服务器ID，可以通过CHANGE MASTER TO来设置备库，但却无法启动复制：

mysql> START SLAVE;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

这个报错可能会让人困惑，因为刚刚执行CHANGE MASTER TO设置了备库，并且通过SHOW MASTER STATUS也确认了。执行SELECT @@server_id也可以获得一个值，但这只是默认值，必须为备库显式地设置服务器ID。

（九）对未复制数据的依赖性

如果在主库上有备库不存在的数据库或表，复制会很容易意外中断，反之亦然。假设主库上有一个备库不存在的数据库，命名为scratch。如果在主库上发生对该数据库中表的更新，备库会在尝试重放这些更新时中断。同样的，如果在主库上创建一个备库上已存在的表，复制也可能中断。

没有什么好的解决办法，唯一的办法就是避免在主库上创建备库上没有的表。

这样的表是如何创建的呢？有很多可能的方式，其中一些可能更难防范。例如，假设先在备库上创建一个数据库scratch，该数据库在主库上不存在，然后因为某些原因切换了主备。当完成这些后，可能忘记了移除scratch数据库以及它的权限。这时候一些人就可以连接到该数据库并执行一些查询，或者一些定期的任务会发现这些表，并在每个表上执行OPTIMIZE TABLE命令。

当提升备库为主库时，或者决定如何配置备库时，需要注意这一点。任何导致主备不同的行为都会产生潜在的问题。

（十）丢失的临时表

临时表在某些时候比较有用，但不幸的是，它与基于语句的复制方式是不相容的。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。重启备库后，所有依赖于该临时表的语句都会失败。

当基于语句进行复制时，在主库上并没有安全使用临时表的方法。许多人确实很喜欢临时表，所以很难去说服他们，但这是不可否认的事实。不管它们的存在多么短暂，都会使得备库的启动和停止以及崩溃恢复变得困难，即使是在一个事务内使用也一样。（如果在备库使用临时表可能问题会少些，但如果备库本身也是一个主库，问题依然存在。）

如果备库重启后复制因找不到临时表而停止，可能需要做以下一些事情：可以直接跳过错误，或者手动地创建一个名字和结构相同的表来代替消失的临时表。不管用什么办法，如果写入查询依赖于临时表，都可能造成数据不一致。

避免使用临时表没有看起来那么难，临时表主要有两个比较有用的特性：

• 只对创建临时表的连接可见。所以不会和其他拥有相同名字临时表的连接起冲突。
• 随着连接关闭而消失，所以无须显式地移除它们。

可以保留一个专用的数据库，在其中创建持久表，把它们作为伪临时表，以模拟这些特性。只需要为它们选择一个唯一的名字。还好这很容易做到：简单地将连接ID拼接到表名之后。例如，之前创建临时表的语句为：CREATE TEMPORARY TABLE temp.top_users(…), 现在则可以执行CREATE TABLE temp.top_users_1234(…)，其中1234是函数CONNECTION_ID()的返回值。当应用不再使用该伪临时表后，可以将其删除或使用一个清理线程来将其移除。表名中使用连接ID可以用于确定哪些表不再被使用——可以通过SHOW PROCESSLIST命令来获得活跃连接列表，并将其与表名中的连接ID相比较。

使用实体表而非临时表还有别的好处。例如，能够帮助你更容易调试应用程序，因为可以通过别的连接来查看应用正在维护的数据。如果使用的是临时表，可能就没这么容易做到。

但是实体表可能会比临时表多一些开销，例如创建会更慢，因为为这些表分配的.frm文件需要刷新到磁盘。可以通过禁止sync_frm选项来加速，但这可能会导致潜在的风险。

如果确实需要使用临时表，也应该在关闭备库前确保Slave_open_temp_tables状态变量值为0。如果不是0，在重启备库后就可能会出现问题。合适的流程是执行STOP SLAVE，检查变量，然后再关闭备库。如果在停止复制前检查变量，可能会发生竞争条件的风险。

（十一）不复制所有的更新

如果错误地使用SET SQL_LOG_BIN=0或者没有理解过滤规则，备库可能会丢失主库上已经发生的更新。有时候希望利用此特性来做归档，但常常会导致意外并出现不好的结果。

例如，假设设置了replicate_do_db规则，把sakila数据库的数据复制到某一台备库上。如果在主库上执行如下语句，会导致主备数据不一致：

mysql> USE test;
mysql> UPDATE sakila.actor ...

其他类型的语句甚至会因为没有复制依赖导致备库复制抛出错误而失败。

（十二）InnoDB加锁读引起的锁争用

正常情况下，InnoDB的读操作是非阻塞的，但在某些情况下需要加锁。特别是在使用基于语句的复制方式时，执行INSERT…SELECT操作会锁定源表上的所有行。MySQL需要加锁以确保该语句的执行结果在主库和备库上是一致的。实际上，加锁导致主库上的语句串行化，以确保和备库上执行的方式相符。

这种设计可能导致锁竞争、阻塞，以及锁等待超时等情况。一种缓解的办法就是避免让事务开启太久以减少阻塞。可以在主库上尽快地提交事务以释放锁。

把大命令拆分成小命令，使其尽可能简短。这也是一种减少锁竞争的有效方法。即使有时很难做到，但也是值得的（使用Percona Toolkit中的pt-archiver工具会很简单）。

另一种方法是替换掉INSERT…SELECT语句，在主库上先执行SELECT INTO OUTFILE，再执行LOAD DATA INFILE。这种方法更快，并且不需要加锁。这种方法很特殊，但有时还是有用的。最大的问题是为输出文件选择一个唯一的名字，并在完成后清理掉文件。可以通过之前讨论过的CONNECTION_ID()来保证文件名的唯一性，并且可以使用定时任务（UNIX的crontab,Windows平台的计划任务）在连接不再使用这些文件后进行自动清理。

也可以尝试关闭上面的这种锁机制，而不是使用上面的变通方法。有一种方法可以做到，但在大多数场景下并不是好办法，备库可能会在不知不觉间就失去和主库的数据同步。这也会导致在做恢复时二进制日志变得毫无用处。但如果确实觉得这么做的利大于弊，可以使用下面的办法来关闭这种锁机制：

# THIS IS NOT SAFE!
innodb_locks_unsafe_for_binlog = 1

这使得查询的结果所依赖的数据不再加锁。如果第二条查询修改了数据并在第一条查询之前先提交，在主库和备库上执行这两条语句的结果可能不相同。对于复制和基于时间点的恢复都是如此。

为了了解锁定读取是如何防止混乱的，假设有两张表：一个没有数据，另一个只有一行数据，值为99。有两个事务更新数据。事务1将第二张表的数据插入到第一张表，事务2更新第二张表（源表），如图所示。

第二步非常重要，事务2尝试去更新源表，这需要在更新的行上加排他锁（写锁）。排他锁与其他锁是不相容的，包括事务1在行记录上加的共享锁。因此事务2需要等待直到事务1完成。事务按照其提交的顺序在二进制日志中记录，所以在备库重放这些事务时产生相同的结果。

但从另一方面来说，如果事务1没有在读取的行上加共享锁，就无法保证了。图10-17显示了在没有锁的情况下可能的事件序列。

如果没有加锁，记录在日志中的事务顺序在主备上可能会产生不同的结果。MySQL会先记录事务2，这会影响到事务1在备库上的结果，而主库上则不会发生，从而导致了主备的数据不一致。

我们强烈建议在大多数情况下将innodb_locks_unsafe_for_binlog的值设置为0。基于行的复制由于记录了数据的变化而非语句，因此不会存在这个问题。

（十三）在主-主复制结构中写入两台主库

试图向两台主库写入并不是一个好主意。如果同时还希望安全地写入两台主库，会碰到很多问题，有些问题可以解决，有些则很难。一个专业人员可能需要经历大量的教训才能明白其中的不同。

在MySQL 5.0中，有两个变量可以用于帮助解决AUTO_INCREMENT自增主键冲突的问题：auto_increment_increment和auto_increment_offset。可以通过设置这两个变量来错开主库和备库生成的数字，这样可以避免自增列的冲突。

但是这并不能解决所有由于同时写入两台主库所带来的问题；自增问题只是其中的一小部分。而且这种做法也带来了一些新的问题：

• 很难在复制拓扑间做故障转移。
• 由于在数字之间出现间隙，会引起键空间的浪费。
• 只有在便用了AUTO_INCREMENT主键的时候才有用。有时候使用AUTO_INCREMENT列作为主键并不总是好主意。

你也可以自己来生成不冲突的主键值。一种办法是创建一个多个列的主键，第一列使用服务器ID值。这种办法很好，但却使得主键的值变得更大，会对InnoDB二级索引键值产生多重影响。

也可以使用只有一列的主键，在主键的高字节位存储服务器ID。简单的左移位（除法）和加法就可以实现。例如，使用的是无符号BIGINT（64位）的高8位来保存服务器ID，可以按照如下方法在服务器15上插入值11：

mysql> INSERT INTO test(pk_col, ...) VALUES( (15 << 56) + 11, ...);

如果想把结果转换为二进制，并将其填充为64位，其效果显而易见：

mysql> SELECT LPAD(CONV(pk_col, 10, 2), 64, '0') FROM test;
+--------------------------------------------+
| LPAD(CONV(pk_col, 10, 2), 64, '0')         |
+--------------------------------------------+
| 000011110000000000000000000000000000001011 |

该方法的缺点是需要额外的方式来产生键值，因为AUTO_INCREMENT无法做到这一点。不要在INSERT语句中将常量15替换为@@server_id，因为这可能在备库产生不同的结果。

还可以使用MD5()或UUID()等函数来获取伪随机数，但这样做性能可能会很差，因为它们产生的值较大，并且本质上是随机的，这尤其会影响到InnoDB（除非是在应用中产生值，否则不要使用UUID()，因为基于语句的复制模式下UUID()不能正确复制）。

这个问题很难解决，我们通常推荐重构应用程序，以保证只有一个主库是可写的。谁能想得到呢？

（十四）过大的复制延迟

复制延迟是一个很普遍的问题。不管怎么样，最好在设计应用程序时能够让其容忍备库出现延迟。如果系统在备库出现延迟时就无法很好地工作，那么应用程序也许就不应该用到复制。但是也有一些办法可以让备库跟上主库。

MySQL单线程复制的设计导致备库的效率相当低下。即使备库有很多磁盘、CPU或者内存，也会很容易落后于主库。因为备库的单线程通常只会有效地使用一个CPU和磁盘。而事实上，备库通常都会和主库使用相同配置的机器。

备库上的锁同样也是问题。其他在备库运行的查询可能会阻塞住复制线程。因为复制是单线程的，复制线程在等待时将无法做别的事情。

复制一般有两种产生延迟的方式：突然产生延迟然后再跟上，或者稳定的延迟增大。前一种通常是由于一条运行很长时间的查询导致的，而后者即便在没有长时间运行的查询时也会出现。

不幸的是，目前我们没那么容易确定备库是否接近其容量上限。正如之前提到的。如果负载总是保持均匀的，备库在负载达到99%时和其负载在10%的时候表现的性能相同，但一旦达到100%时就会突然开始产生延迟。但实际上负载不太可能很稳定，所以当备库接近写容量时，就可能在尖峰负载时看到复制延迟的增加。

当备库无法跟上时，可以记录备库上的查询并使用一个日志分析工具找出哪里慢了。不要依赖于自己的直觉，也不要基于查询在主库上的查询性能进行判断，因为主库和备库性能特征很不相同。最好的分析办法是暂时在备库上打开慢查询日志记录，然后使用第3章讨论的pt-query-digest工具来分析。如果打开了log_slow_slave_statements选项，在标准的MySQL慢查询日志能够记录MySQL 5.1及更新的版本中复制线程执行的语句，这样就可以找到在复制时哪些语句执行慢了。Percona Server和MariaDB允许开启或禁止该选项而无须重启服务器。

除了购买更快的磁盘和CPU（固态硬盘能够提供极大的帮助，详细参阅第9章），备库没有太多的调优空间。大部分选项都是禁止某些额外的工作以减少备库的负载。一个简单的办法是配置InnoDB，使其不要那么频繁地刷新磁盘，这样事务会提交得更快些。可以通过设置innodb_flush_log_at_trx_commit的值为2来实现。还可以在备库上禁止二进制日志记录，把innodb_locks_unsafe_for_binlog设置为1，并把MyISAM的delay_key_write设置为ALL。但是这些设置以牺牲安全换取速度。如果需要将备库提升为主库，记得把这些选项设置回安全的值。

1.不要重复写操作中代价较高的部分

重构应用程序并且/或者优化查询通常是最好的保持备库同步的办法。尝试去最小化系统中重复的工作。任何主库上昂贵的写操作都会在每一个备库上重放。如果可以把工作转移到备库，那么就只有一台备库需要执行，然后我们可以把写的结果回传到主库，例如，通过执行LOAD DATA INFILE。

这里有个例子，假设有一个大表，需要汇总到一个小表中用于日常的操作：

mysql> REPLACE INTO main_db.summary_table (col1, col2, ...)-> SELECT col1, sum(col2, ...)-> FROM main_db.enormous_table GROUP BY col1;

如果在主库上执行查询，每个备库将同样需要执行庞大的GROUP BY查询。当进行太多这样的操作时，备库将无法跟上。把这些工作转移到一台备库上也许会有帮助。在备库上创建一个特别保留的数据库，用于避免和从主库上复制的数据产生冲突。可以执行以下查询：

mysql> REPLACE INTO summary_db.summary_table (col1, col2, ...)-> SELECT col1, sum(col2, ...)-> FROM main_db.enormous_table GROUP BY col1;

现在可以执行SELECT INTO OUTFILE，然后再执行LOAD DATA INFILE，将结果集加载到主库中。现在重复工作被简化为LOAD DATA INFILE操作。如果有N个备库，就节约了N-1次庞大的GROUP BY操作。

该策略的问题是需要处理陈旧数据。有时候从备库读取的数据和写入主库的数据很难保持一致（下一章我们会详细描述这个问题）。如果难以在备库上读取数据，依然能够简化并节省库备工作。如果分离查询的REPLACE和SELECT部分，就可以把结果返回给应用程序，然后将其插入到主库中。首先，在主库执行如下查询：

mysql> SELECT col1, sum(col2, ...) FROM main_db.enormous_table GROUP BY col1;

然后为结果集的每一行重复执行如下语句，将结果插入到汇总表中：

mysql> REPLACE INTO main_db.summary_table (col1, col2, ...) VALUES (?, ?, ...);

这种方法再次避免了在备库上执行查询中的GROUP BY部分。将SELECT和REPLACE分离后意味着查询的SELECT操作不会在每一台备库上重放。

这种通用的策略——节约了备库上昂贵的写入操作部分——在很多情况下很有帮助：计算查询的结果代价很昂贵，但一旦计算出来后，处理就很容易。

2.在复制之外并行写入

另一种避免备库严重延迟的办法是绕过复制。任何在主库的写入操作必须在备库串行化。因此有理由认为“串行化写入”不能充分利用资源。所有写操作都应该从主库传递到备库吗？如何把备库有限的串行写入容量留给那些真正需要通过复制进行的写入？

这种考虑有助于对写入进行区分。特别是，如果能确定一些写入可以轻易地在复制之外执行，就可以并行化这些操作以利用备库的写入容量。

一个很好的例子是之前讨论过的数据归档。OLTP归档需求通常是简单的单行操作。如果只是把不需要的记录从一个表移到另一个表，就没有必要将这些写入复制到备库。可以禁止归档查询记录到二进制日志中，然后分别在主库和备库上单独执行这些归档查询。

自己复制数据到另外一台服务器，而不是通过复制，这听起来有些疯狂，但却对一些应用有意义，特别是如果应用是某些表的唯一更新源。复制的瓶颈通常集中在小部分表上。如果能在复制之外单独处理这些表，就能够显著地加快复制。

3.为复制线程预取缓存

如果有正确的工作负载，就能通过预先将数据读入内存中，以受益于在备库上的并行I/O所带来的好处。这种方式并不广为人知。大多数人不会使用，因为除非有正确的工作负载特性和硬件配置，否则可能没有任何用处。我们刚刚讨论过的其他几种变通方式通常是更好的选择，并且有更多的方法来应用它们。但是我们知道也有小部分应用会受益于数据预取。

有两种可行的实现方法。一种是通过程序实现，略微比备库SQL线程提前读取中继日志并将其转换为SELECT语句执行。这会使得服务器将数据从磁盘加载到内存中，这样当SQL线程执行到相应的语句时，就无须从磁盘读取数据。事实上，SELECT语句可以并行地执行，所以可以加速SQL线程的串行I/O。当一条语句正在执行时，下一条语句需要的数据也正在从磁盘加载到内存中。

如果满足下面这些条件，预取可能会有效：

• 复制SQL线程是I/O密集型的，但备库服务器并不是I/O密集型的。一个完全的I/O密集型服务器不会受益于预取，因为它没有多余的磁盘性能来提供预取。
• 备库有多个硬盘驱动器，也许8个或者更多。
• 使用的是InnoDB引擎，并且工作集远不能完全加载到内存中。

一个受益于预读取的例子是随机单行UPDATE语句，这些语句通常在主库上高并发执行。DELETE语句也可能受益于这种方法，但INSERT语句则不太可能会——尤其是当顺序插入时——因为前一次插入已经使索引“预热”了。

如果表上有很多索引，同样无法预取所有将要被修改的数据。UPDATE语句可能需要更新所有索引，但SELECT语句通常只会读取主键和一个二级索引。UPDATE语句依然需要去读取其他索引的数据以进行更新。在多索引表上这种方法的效率会降低。

这种技术并不是“银弹”，有很多原因会导致其不能工作，甚至适得其反。只有在清楚硬件和操作系统的状况时才能尝试这种方法。我们知道有些人利用这种办法将复制速度提升了300%到400%，但我们也尝试过很多次，并发现这种方法常常无法工作。正确地设置参数非常重要，但并没有绝对正确的参数组合。

mk-slave-prefetch是Maatkit中的一款工具，该工具实现了本节所提到的预取策略。mk-slave-prefetch本身有很多复杂的策略以保证其在尽可能多的场景下工作。但缺点是它实在太复杂并且需要许多专业知识来使用。另一款工具是Anders Karlsson的slavereadahead工具，可以从http://sourceforge.net/projects/slavereadahead/获得。

另一种方法在是在InnoDB内部实现的。它可以允许设置事务为特殊的模式，以允许InnoDB执行“假”更新。因此可以使用一个程序来执行这些假更新，这样复制线程就可以更快地执行真正的更新。我们已经在Percona Server中为一个非常流行的互联网网络应用单独开发了该功能。可以去检查一下此特性现在的状态，因为在本书出版时或许已经更新过了。

如果正在考虑这项技术，可以从一个熟悉其工作原理及可用选项的专家那里获得很好的建议。这应该作为其他方案都不可行时最后的解决办法。

（十五）来自主库的过大的包

另一个难以追踪的问题是主库的max_allowed_packet值和备库的不匹配。在这种情况下，主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时，可能会碰到各种各样的问题，包括无限报错和重试，或者中继日志损坏。

（十六）受限制的复制带宽

如果使用受限的带宽进行复制，可以开启备库上的slave_compressed_protocol选项（在MySQL 4.0及新版本中可用）​。

当备库连接主库时，会请求一个被压缩的连接——和MySQL客户端使用的压缩连接一样。

使用的压缩引擎是zlib，我们的测试表明它能将文本类型的数据压缩到大约其原始大小的三分之一。

其代价是需要额外的CPU时间，包括在主库上压缩数据和在备库上解压数据。

如果主库和其备库间的连接是慢速连接，可能需要将分发主库和备库分布在同一地点。这样就只有一台服务器通过慢速连接和主库相连，可以减少链路上的带宽负载以及主库的CPU负载。

（十七）磁盘空间不足

复制有可能因为二进制日志、中继日志或临时文件将磁盘撑满。

特别是在主库上执行了LOAD DATA INFILE查询并在备库开启了log_slave_updates选项。

延迟越严重，接收到但尚未执行的中继日志会占用越多的磁盘空间。

可以通过监控磁盘并设置relay_log_space选项来避免这个问题。

（十八）复制的局限性

MySQL复制可能失败或者不同步，不管有没有报错，这是因为其内部的限制导致的。

大量的SQL函数和编程实践不能被可靠地复制。

很难确保应用代码里不会出现这样或那样的问题，特别是应用或者团队非常庞大的时候。

另外一个问题是服务器的Bug，虽然听起来很消极，但大多数MySQL的主版本都存在着历史遗留的复制Bug。

特别是每个主版本的第一个版本。诸如存储过程这样的新特性常常会导致更多的问题。

MySQL复制非常复杂。应用程序越复杂，你就需要越小心。但是如果学会了如何使用，复制会工作得很好。