同乐学堂前面章节已经提到过复制带来的问题，说明了每章提及的问题是如何在复制环境中产生影响的。本章注重讲述复制本身。大多数复制错误是由于错误或性能下降引起的，如从服务器落后于主服务器几个小时。MySQL的复制是异步的。这意味着主服务器并不关心从服务器上的数据是否是一致的。尽管可配置成多主环形复制，实际意义上就是一串服务器各自同时担当主服务器和从服务器的角色。
MySQL多主设置请参考图5-1，该图对多主复制进行了说明。
图5-1 两台服务器互相复制这里A是从服务器B的主服务器，同时B是从服务器A的主服务器。你可以往这个链中随意增加服务器（见图5-2）。
图5-2 多主环形复制
排查此种配置的步骤，首先挑出一对主从服务器（见图5-3）。
图5-3 关注多主复制的一个方向
然后像简单的复制环境一样解决。接着挑出另外一对继续。这里不描述其细节，5.3.2节会简述。
提示从5.5版本开始，MySQL包中包入了一个半同步复制插件。如果插件开启，主服务器会等待其中一个从服务器发送确认，告知其成功执行了每一个事件。这并不等同于同步复制，因为主服务器并不知道从服务器在执行事件后数据是否相同（2.7.1节讨论过这种可能性）。此外，如果主服务器连有大量从服务器，并不能保证数据已经复制到所有从服务器上。排查半同步复制和异步复制一样，唯一的不同体现在具体选项的影响上。这里不针对半同步复制进行具体描述。如果你遇到了问题，按照第3章中说的方法做即可。MySQL从服务器运行两个与复制相关线程：I/O线程，它负责处理主服务器过来的所有流量；SQL线程它重新执行事件，在从服务器上复制结果。两种线程遇到的不同问题，应该使用不同的技巧来解决，在本章分两节来讨论它们。
5.1 查看从服务器状态
在开始对线程排错之前，首先介绍一个查看复制状态信息的有用命令：从库上运行的SHOW SLAVE STATUS查询。下面的例子是在运行正常的从服务器上获取的，当主服务器停止时从服务器上是如何显示错误的。我将分段说明输出。
这是I/O线程的状态。对于一个运行的从服务器，它通常包含Waiting for master to send event。
在这里，“Master_Log_File”这一行显示了主服务器上的二进制日志的文件名，如果此时从服务器发生了I/O线程错误，那么这一行将不会显示任何信息。
“106”表示从服务器读取的主服务器二进制日志中的位置。
“Relay_Log_File”是中继日志的名字，是一个在从服务器上包含经主服务器二进制日志转换过内容的文件。
“255”是中继日志的当前位置。 “Slave_IO_Running”表示I/O线程的基本状态，即它是否正在运行。结果或者为Yes或者为No。
这一次，我们看到的为SQL线程的运行状态。同样它或者为Yes或者为No。
“Exec_Master_Log_Pos”中的106代表执行至主服务器二进制日志的位置。如果从服务器有延迟这个值是不同于Read_Master_Log_Pos的。
“Seconds_Behind_Master”显示了从服务器落后主服务器多远。它包含了从服务器上最后执行的事件和中继日志中复制过来的主服务器二进制日志的最后一个事件之间相隔的秒数。理想情况下这个值为0。如果从服务器没有和主服务器连接，这个字段为NULL。
这也是停止的从服务器的输出。 “Last_IO_Errno”要么显示的是I/O线程上最近的错误，要么是0（代表自从服务器启动以来没有产生过错误）。
这两行包含最近的I/O错误的文本。这种情况下，它们包含I/O线程为什么出错的信息。
“Last_SQL_Errno”要么显示的是SQL线程上最近的错误，要么是0（代表自从服务器启动以来没有产生过错误）。
再次说明，SQL错误的文本。虽然这里没有错误，但是这两行也可能包含SQL线程出错的消息。既然你们已经熟悉了SHOW SLAVE STATUS的输出，下面我们就可以转向排错了。
5.2 与I/O线程有关的复制错误
一般I/O错误包括：
从服务器无法连接主服务器；
从服务器连接到主服务器，但不断断开连接；
从服务器延迟。
当I/O错误出现时，前面章节中我们看到的从服务器状态会变成Slave_IO_Running：No，原因会出现在Last_IO_Errno和Last_IO_Error字段中。错误日志也包含I/O线程出错的消息前提是log_warnings设为1（默认值）。
当从服务器无法连接时，首先在主服务器上检查复制用户是否有正确的权限。复制用户（在开始复制时在CHANGE MASTER查询中指定为master_user的用户）必须在主服务器上拥有REPLICATION SLAVE权限。如果它没有，在主服务器上对该用户授予此权限。一旦你确认复制用户拥有正确的权限，你就需要检查网络。使用ping工具，来判断主服务器所在的主机是否可以访问。下面是一个例子。
如果ping无法连接主服务器主机，这清楚地定位了网络有问题并且需要修复它。也可以利用telnet来检查MySQL服务器是否可以访问。给telnet命令指定主服务器的主机和端口参数。
例子中，MySQL服务器是可以访问的：5.1.59-debug-log}O&i`(D^,#!o8h%zY0$`;D^)是欢迎字符串。如果ping正常但telnet不能连接到服务器，你需要查明MySQL服务器是否正在运行，端口是否可访问，也就是从服务器主机是否可打开主服务器端口，并且主服务器主机是否允许从服务器主机连接到该端口。如果前面的测试成功，但I/O复制线程依然停止，使用复制账户的凭证在MySQL命令行连接主服务器，确保能够连接。下面是成功连接上并确认复制用户权限正确的例子：
这里SHOW GRANTS表明通过从服务器的复制用户的参数可以从主服务器复制数据。当从服务器可以连接到主服务器但是不停地断开连接时，使用操作系统工具来诊断网络。可以使用tcpdump或者nestat来监视流量，甚至是经由网络传送大文件并观测进度来确保网络的稳定性。我们的目的是确定主从服务器之间的连接是否有中断。如果到主服务器的连接已经建立，netstat的输出如下所示。
这个例子中主服务器执行了一条查询，它成功地复制了。当从服务器严重落后主服务器时，这说明从服务器负载过高或者网络慢。本章后面讨论SQL线程时再回到过载这个问题上。为检查网络是否慢，使用tcpdump或者发送大文件并观察传送包消耗的时间。也要检查MySQL是否利用到了系统提供的所有可用上下行带宽。如果带宽使用量超过了80%，你有必要购买更快的网络硬件。如果未利用可用带宽，检查是否其他软件占用了同一个网络接口并影响了MySQL服务器。如果是其他软件的原因，将它转移到另外一台主机上或者至少更换成另外的硬件网络接口。
另外一个I/O线程错误是中继日志损坏。你很可能看到如下SQL线程报错。
本节将此讨论这个问题，而不在与SQL线程相关的章节，因为造成这个问题的真正原因可能是之前的I/O线程错误导致损坏了中继日志。当SQL线程在试图执行中继日志中的事件时，一旦遇到损坏的部分就会出现此现象。在遇到这样一个错误时，首先要做的就是依照错误消息中的指示：使用mysqlbinlog工具检查主服务器二进制日志及从服务器中损坏的中继日志。mysqlbinlog将二进制日志文件转换为可读的格式。只需要这样调用它。
此例中我使用了一个有效的二进制日志文件。如果文件被损坏，mysqlbinlog会明确提示。
这里使用了行级二进制日志格式，输出显示行事件看起来大致是这样的。如果使用binlog_format=statement，所有事件作为SQL语句输出。加上—verbose选项可以看到行级事件的SQL表现形式：
除了--verbose选项之外，我使用了--start-position和--stop-position来说明在日志文件过大的情况下如何限制mysqlbinlog输出特定的位置。
可以将mysqlbinlog的输出管道至MySQL客户端然后执行查询。这适用于SBR和基于行的复制（RBR），当你想调试二进制日志事件是如何在从服务器上执行时这也很用。如果问题出现在主服务器的二进制日志上，找出发生的原因。首先手动重启复制，还原从Exec_Master_Log_pos到最近可能的位置的事件，手动执行它们，然后观察Seconds_Behind_Master直到0，比较主从服务器上的表。
如果从损坏到现在变化太大，找出被更改过的行不太现实，你很可能需要备份主服务器，然后在从服务器上加载备份并重启复制。可以对单个表进行复制进行重放。找到二进制日志中数据正确复制的最后一点，然后设置replicate-wild-do-table选项并运行：
这里log_pos是主服务器二进制日志或者中继日志中那张表的最后一个正确位置。在从服务器到达该位置后会停止，移除replicate-wild-do-table选项并重启服务器。如何检查表是否一致，有多种方法来检查表在主从服务器上的一致性。这里做一个简要概述，根据遇到的问题使用其中一种方法。校验表顾名思义，此查询返回一个表校验和。这条MySQL语句不需要任何额外安装什么，始终可用。
当你想检查主从服务器上的表数据是否一致时，在服务器两端分别执行该查询并比较结果。确保Seconds_Behind_Master是零，当CHECKSUM TABLE运行时保证主服务器上没有对这张表的写入操作。Mysqldiff这是随着MySQL Workbench捆绑安装的其中一个MySQL WB实用工具。此工具读取数据服务器对象的定义，然后使用类似diff的方法来判断两个对象是否一致。下面是一个用来对复制排错的例子：
pt-table-checksum它属于Percona工具包集的一部分，是上述讨论中最强大的一个工具。它连接至主服务器和从服务器来比较表结构和表数据是否相同。为了做到这一点，该工具首先创建一张存放主服务器上表的检验和。这个值经复制后，从服务器上再执行pt-table-checksum来检验数据。这里列举一个检查复制的例子：
此条命令计算并保存book数据服务器中的每张表的校验和。一旦从服务器启动，我们就可以检查表数据是否相同：
工具会输出的找到的不同，（如果有）。
这里我们可以看到ts表在主从服务器上的数据是不一致的，而t1表中的数据是一样的。无论你使用什么工具，保证在主服务器上检查后不要再复制改变。最简单的方法就是在你当前检验的表上加上一个写锁。mysqldiff和pt-table-checksum能做到的比我刚刚说的更多，但我所介绍的方法可以用来帮助排查复制故障，这是它最重要的一个功能。如果你在主服务器二进制日志没有发现任何错误，中继日志也没有被损坏，这可能是网络问题或者磁盘损坏的现象。这两种情况下，可以把从服务器上中继日志的位置重新定位至Exec_Master_Log_Pos，然后重启，按顺序执行下面的查询，STOP SLAVE; CHANGE MASTER master_log_pos=Exec_Master_Log_Pos_Value, master_log_file=Relay_Master_Log_File_Value'; START SLAVE，中继日志会重建。如果损坏是一个异常事件，复制会启动并再次运行。但不要只清理，忽略了造成问题的可能性。请检查你的磁盘日志和网络问题。为了查明是否是磁盘问题，检查操作系统的日志文件，利用工具来检查磁盘是否有坏块。如果发现了，修复磁盘。否则，你可能再次遇到类似问题。
在老版本的MySQL中网络问题会造成损坏。在5.0.56和5.1.24版本之前，不稳定的网络经常造成中继日志损坏。在5.0.56和5.1.24版本中，bug #26489修复了，这种问题极少见了。从5.6.2版本开始，复制校验和引入了。这解决了遗留至今网络中断造成损坏的问题。这些修正并没有自动恢复损坏的中继日志，但是防止了由于主服务器上问题或者网络问题导致的日志损坏。从5.5版本开始，可以配置relay-log-recovery选项，当从服务器重启时可自动恢复。但即使你使用了打过补丁的MySQL新版本，你仍然要检查网络。越早定位网络问题，修复它们也越快。即便开启了自动恢复，解决网络问题也需要时间，同样也会拖慢复制。本节考虑到了一些SQL线程错误，它是由于I/O线程引起的。下一节将讨论跟I/O线程无关的SQL线程问题。
5.3 与SQL线程有关的问题
2.7.1节提到过每个从服务器只有单个SQL线程，所以它的所有错误都可用单线程的MySQL客户端进行测试。即使你运行多线程从服务器预览版，当重现错误时你也可以始终让它使用单线程。如果减少到只激活一个SQL线程未能使问题消失，请使用以下方法来修复单线程的逻辑错误，然后再切换至多线程。重新创建一条导致复制失败的查询很简单：用MySQL命令行工具执行它就行了。当在从服务器上收到SQL错误时，复制停止了。SHOW SLAVE STATUS显示了导致问题的SQL线程错误：
错误消息通常包含SQL查询的文本及失败的原因。此例中，错误消息很明显（我在从服务器上删除了t1表以创建这个例子），但在有疑问的情况下，你可以尝试在MySQL命令行下运行同样的命令来查看结果：
本例中的错误清楚表明你接下来需要做什么来解决问题：创建表
在表创建后，可以重启SQL线程：
问题解决了，从服务器又成功运行。
5.3.1 当主从服务器上数据不同的时候
如果错误不能简单解决，检查主从服务器上表的定义是否一致。在出问题的查询执行前你也应该检查表中数据是否一样。 提示MySQL复制允许你在主从服务器上对表有不同的定义。如果你工作在这种配置下，分析相同的查询在不同表上是怎样运行的。同样也要检查是否使用了不同的存储引擎或者索引，这些都会影响最终结果。当SQL线程停止时，一个很常见的原因就是从服务器的表不同于主服务器。这里不会说明造成错误的所有原因，普遍的有以下几个。在例2-1或其他地方我们见到的问题，并发事务更新不能保证数据的一致性。INSERT ON DUPLICATE KEY UPDATE，如果随着其他连接上的更新运行在从服务器上，导致和主服务器执行顺序不一致，会更新错误的行，并且跳过主服务器上已经更新的行。不考虑从服务器的存在，在MyISAM表上执行并发插入。使用了不确定性函数[1]。记住一个从服务器是否防崩溃也是非常重要的，因为当mysqld发生崩溃后，它能够在重新启动时重复执行崩溃前的事务，从而使主服务器与从服务器有不同的数据。在非事务表更新的中间当从服务器故障时也会发生类似问题，如MyISAM。
5.3.2 从服务器上的循环复制以及无复制写入
如果你在同步之外对从服务器进行写入，你必须关注数据一致性。两种方法可以避免问题出现，确保写入对象有别于复制改变的数据，并且总是在主从服务器上使用具有不同序列的主键。可使用AUTO_INCREMENT主键来保持不同键值，在每个主服务器上设置不同的auto_increment_offset选项起点，保持复制环境中auto_increment_increment为服务器的个数。环形复制中，主服务器即为另一台主服务器的从服务器[2]，这样一来也要避免相同的问题，复制数据在从服务器写入的情况下产生冲突。MySQL是允许配置环形复制的，但基于异步复制设计不能保证其数据一致性。一致性需要你自己来维护。因此，从排错角度来讲，调试环境复制的错误和其他复制问题一样。记住，双向配置就是服务器互相为主从服务器。一旦你遇到错误，首先确定它们中的主从角色，然后根据情况来应对。很可能你需要测试主从关系，并且互换角色。为了举例说明技巧，让我们看一下第5章开篇（见图5-4）“MySQL多主配置”中简单的示例。
如果在这种配置中遇到问题，取出一对（像图5-5中那样），然后就像简单的主从一样来解决问题。排错时停止B上的所有更新。
图5-5 关注多主复制中的一个方向
在问题解决后，临时停止A上的更新并转向B，就像图5-6中显示的配置一样。
如果在这种情况下还有问题，继续解决，然后开始A上的更新，现在你就回到了环形多主配置了。此时，最好分析当时为什么会出现问题，并在两台服务器重启更新前进行修复。这种方法适用于环中任意数目的服务器。当搭建环形复制时，你需要清楚地分开查询，以便一台主服务器上的变更不会干扰到另外一台。错误可以使复制中断或导致数据不一致。我不在此说明最佳实践，你可以在由Charles Bell等人撰写的《高可用MySQL》一书（O’Reilly出版）的第4章中找到多主复制搭建的详细介绍。好的设计对于创建一个无故障环形多圭复制，是至关重要的。
5.3.3 不完整或被改变的SQL语句
如果错误消息没有显示完整的查询，并且错误日志也不包含完整的查询（当查询超过1024字节时会发生），你需要使用mysqlbinlog工具在主服务器二进制日志或者从服务器的中继日志中获取完整版的查询，然后分析为何执行失败。这对基于语句的复制生效，因为查询的记录和发送是通过原生可读SQL形式进行的。但如果使用行格式你应该怎么做呢？行事件和查询一样是可以在MySQL客户端执行的。在mysqlbinlog中使用—verbose选项来获取行事件的SQL表示形式。在从服务器上始终使用和主服务器二进制日志事件应用时执行的相同查询。运用mysqlbinlog工具去检查运行哪条查询节省时间。二进制日志偶尔会包含一些查询，这些查询和主服务器上原始执行的查询有一些偏差，副作用就凸显出来了。如果你忽视这些影响，你可能花好几个小时去再现问题，但使用主服务器上执行的查询就无法再现。
5.3.4 主从服务器出现的不同错误
另一条让人混淆的状态消息是“Query caused different errors on master and slave...”，消息中通常会含有这种字眼。最让人疑惑的是，这个消息会说从服务器上没有错误——“Error on slave:‘no error’(0).”——但主服务器上有一个错误。这可以发生在，例如，主服务器上的错误由触发器产生，但是主表更新成功了。原始表上的查询被写入二进制日志文件中，包含了一个触发器更新失败的错误代码。这种情况下，如果从服务器上的触发器成功执行了或者从服务器上的表根本就没有触发器，那么从服务器上的查询不会返回错误，因此我们得到了这么一条消息。为了迅速解决这个问题，使用“SET GLOBAL SLAVE_SKIP_COUNTER=1”忽略错误，并继续复制。不要忘了寻找造成问题的真正原因防止再次出现。如果主服务器上的错误是由于触发器死锁造成的，手动修复从服务器因为主从表产生了不同的数据。要做到这一点，你需要找出哪些表包含不同数据并更新从服务器以保持和主服务器一致。
5.3.5 配置
另一个重点是检查主从服务器的配置选项。理想状况下两者应该是一致的，而有时也有好的理由使它们保持不同，如不同的硬件或负载。当配置不一致并且你开始收到的SQL错误不太容易解释时，检查那些能改变服务器行为的配置参数。我们在第3章讨论过一些。
我推荐使用--no-defaults选项来运行mysqld，就像单服务器配置一样，来检查服务器是否受你的定制选项影响，这里我推荐复制主服务器的配置到从服务器上，使得从服务器拥有和主服务器完全一致的配置。只调整服务器之间需要不同的那些选项，如server_id，这个必须始终唯一。判断问题是否可以复现。如果不能，你放心一定是配置的一个变量导致了问题。此时你只须找出那个有问题的变量（用3.6节提到的技巧），然后做相应调整。始终比较主从服务器上配置的不同。
5.3.6 当从服务器远远落后主服务器时
5.2节讨论了网络不可靠导致Seconds_Behind_Master增大的情形。另一个造成巨大延迟的原因是从服务器执行速度慢于主服务器。速度慢的硬件，更小的缓冲区，或者自身读和复制线程的资源争用都会导致从服务器更慢。另一个可能性就是主服务器是并行执行语句的，但是从服务器使用单线程一个一个地执行所有二进制日志中的事件。首先你需要的做的就是找出从服务器落后的真正原因，然后思考如可改善。对于速度慢的硬件最简单，通过购买性能好的硬件就行。但在花钱之前，分析一下主服务器是否有效利用了硬件资源，及从服务器上与性能相关的选项是否可优化。例如，如果主服务器运行在一个共享环境中，而从服务器是在一台稍慢的专有服务器上，你是有机会去提升它的速度的。计算主服务器实际使用了多少资源，通过调整配置参数从服务器性能就有多少提升。如果两台服务器硬件一样，或者从服务器配置更好但依然落后，请检查与性能相关的选项。分析其影响并根据情况调整从服务器。将主从服务器上的选项设为相同是个好的起点。这样，可以肯定这些选项对于复制事件是优化的，你只需要调整从服务器上那些提高并发负载的选项就行了。最坏的情况是，缓慢是由于主服务器并行执行但从服务器是单线程产生的。这种情况下你所能做的，除了使用多线程从服务器预览之外，就是升级从服务器的硬件，尽可能地调整性能相关的选项。上述例子中，你仍然应该分析在从服务器SQL线程执行的同时其查询的影响。第2章提到过关于并发的错误排查。 [1] 确定性函数指每次执行时使用同样的参数返回的结果是一样的。CONCAT('Hello, ', 'world!')是确定性的，而NOW()不是。[2] 这种复制也叫“多主复制”或“双向复制”。
微信扫一扫，打赏作者吧～
