复制的问题和解决方案

中断MySQL的复制并不是件难事。因为实现简单，配置相当容易，但也意味着有很多方式会导致复制停止，陷入混乱并中断。这边描述了一些比较普遍的问题，讨论如何重现这些问题，以及当遇到这些问题时如何解决或者阻止其发生。

数据损坏或丢失的错误

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

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

主库意外关闭

如果没有设置主库的sync_binlog 选项，就可能在崩溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库I/O线程因此也可一直处于读不到尚未写入磁盘的事件的状态中。当主库重新启动时，备库将重连到主库并再次尝试去读该事件，但主库会告诉备库没有这个二进制日志偏移量。二进制日志转储线程通常很快，因此这种情况并不经常发生。
解决这个问题的方法是指定备库从下一个二进制日志的开头读日志。但是一些日志事件将永久地丢失，建议使用Percona Toolkit中的pt-table-checksum工具来检查主备一致性，以便于修复。可以通过在主库开启sync_binlog 来避免事件丢失。即使开启了sync_binlog,MyISAM表的数据仍然可能在崩溃的时候损坏，对于 InnoDB事务,如果innodb_flush_log_at_trx_commit 没有设为1 ,也可能丟失数据(但数据不会损坏)。

备库意外关闭

当备库在一次非计划中的关闭后重启时，会去读master.info文件以找到上次停止复制的位置。不幸的是，该文件并没有同步写到磁盘，文件中存储的信息可能是错误的。备库可能会尝试重新执行一些二进制日志事件，这可能会导致唯一索引错误。除非能确定备库在哪里停止(通常不太可能)，否则唯一的办法就是忽略那些错误。 Percona Toolkit中的pt-slave-restart工具可以帮助完成这一点。
如果使用的都是InnoDB表，可以在重启后观察MySQL错误日志。InnoDB在恢复过程中会打印出它的恢复点的二进制日志坐标。可以使用这个值来决定备库指向主库的偏移量。Percona Server提供了一个新的特性，可以在恢复的过程中自动将这些信息提取出来,并更新masterinfo文件,从根本上使得复制能够协调好备库上的事务。MySQL 5.5也提供了一些选项来控制如何将master.info和其他文件刷新到磁盘，这有助于减少这些问题。

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

主库上的二进制日志损坏

如果主库上的二进制日志损坏，除了忽略损坏的位置外你别无选择。可以在主库上执行FLUSH LOGS命令，这样主库会开始一个新的日志文件，然后将备库指向该文件的开始位置。也可以试着去发现损坏区域的结束位置。某些情况下可以通过SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该步骤，直到跳过所有损坏的事件。但如果有太多的损坏事件，这么做可能就没有意义了。损坏的事件头会阻止服务器找到下一个事件。这种情况下，可能不得不手动地去找到下一个完好的事件。

备库上的中继日志损坏

如果主库上的日志是完好的，就可以通过CHANGE MASTER TO命令丟弃并重新获取损坏的事件。只需要将备库指向它当前正在复制的位置(Relay_Master_Log_File/ Exec_Master_Log_Pos) 。这会导致备库丢弃所有在磁盘上的中继日志。就这一点而言， MySQL 5.5做了一些改进，它能够在崩溃后自动重新获取中继日志。

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

当主库崩溃时，InnoDB可能将一个事务标记为已提交，此时该事务可能还没有记录到二进制日志中。除非是某个备库的中继日志已经保存，否则没有任何办法恢复丢失的事务。在MySQL 5.0版本可以设置sync_binlog 选项来防止该问题，对于更早的MySQL 4.1可以设置sync_binlog和safe_binlog选项。

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

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

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

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

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

UPDATE tbl SET col????

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

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

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

某些事件已经损坏或被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的.

同样的，这种情况下mysqlbinlog也起不了多少作用。

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

InnoDB加锁引起的锁征用

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

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

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

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

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

# THIS IS NOT SAFE!
innodb_locks_unsafe_for_binlog = 1

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

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

image.png

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

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

image.png

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

过大的复制延迟

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

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

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

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

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

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

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

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

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

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

mysql> REPLACE INTO main _db. summary_ _table (co11, col2, ...)
SELECT col1, sum(co12, ...)
FROM main _db. enormous_ table GROUP BY col1;

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

总结

MySQL复制是其内建功能中的“ 瑞士军刀”，显著增加了MySQL的功能和可用性。事实上这也是MySQL这么快就如此流行的关键原因之一。

尽管复制有许多限制和风险，但大多数相对不重要或者对大多数用户而言是可以避免的。许多缺点只在一些高级特性的特殊行为中，这些特性对少数需要的人而言是有帮助的，但大多数人并不会用到。

正因为复制提供了如此重要和复杂的功能，服务器本身不提供所有其他你需要的功能，例如，配置、监控、管理和优化。第三方工具可以很好地帮助你。虽然可能有失偏颇，但我们认为最值得关注的工具一定是Percona Toolkit和Percona XtraBackup，它们能够很好地改进你对复制的使用。在使用别的工具前，建议你先检查它们的测试集合，如果没有正式的、自动化的测试集合，在将其应用到你的数据之前请认真考虑。

对于复制,不要按照想象做事，例如，使用环形复制、黑洞表或者复制过滤，除非确实有需要。使用复制简单地去镜像一份完整的数据拷贝，包括所有的权限。在各方面保持你的主备库相同可以帮助你避免很多问题。

谈到保持主库和备库相同，这里有一个简短但很重要的列表告诉你在使用复制的时候需要做什么:

使用Percona Toolkit 中的pt-table-checksum以确定备库是主库的真实拷贝
监控复制以确定其正在运行并且没有落后于主库。
理解复制的异步本质，并且设计你的应用以避免或容忍从备库读取脏的数据。
在一个复制拓扑中不要写入超过一个服务器，把备库配置为只读，并降低权限以阻止对数据的改变。