MySQL复制——性能和扩展性的基石3：常见问题及解决方案当出现问题时，如何解决或防止问题发生。1数据损坏或丢失问题描述：服务器死机、断电、磁盘损坏、内存或网络错误等，导致数据损坏或丢失。问题原因：非正常关机导致数据没有及时写入硬盘。这种问题一般可以分为几种情况：1.1没有发生主库意外关闭，避免的解决方法：设置主库的sync_binlog选项为1，这个选项表示MySQL是否控制flush二进制日志。当设置为1时，表示每提交一个事务，MySQL都会刷新binlog，这是最安全但性能损失最大的设置。问题出现了，解决办法：指定备库从下一个二进制日志开始重新读取日志。但是一些日志事件将永久丢失。可以使用PerconaToolkit中的pt-table-checksum工具检查主备一致性，方便修复。1.2备库意外关闭。当备库意外关闭并重新启动时，它会读取master.info文件找到上次停止复制的位置。但是如果意外关机，这个文件中存储的信息可能是错误的。此外，备库也可能会尝试重新执行一些二进制文件，这可能会导致唯一索引错误。我们可以使用PerconaToolkit中的pt-slave-restart工具来帮助备库重新执行日志文件。如果您使用的是InnoDB表，您可以在重启后查看MySQL错误日志。InnoDB在恢复过程中会打印出恢复点的二进制日志坐标，你可以通过这个值来判断备库到主库的偏移量。1.3主库二进制日志损坏如果主库上的二进制日志损坏，只能忽略损坏的位置。忽略库存位置后，我们可以通过FLUSHLOGS命令在主库上启动一个新的日志文件，然后将备库指向该文件的开头。1.4备库中继日志损坏。如果主库上的日志完好无损，有两种解决方法：1）手工处理。找到masterbinlog日志的pos点，然后重新同步。2）自动处理。在mysql5.5中，考虑到slave宕机时relaylog损坏的问题，只要在slave的配置文件my.cnf中增加一个参数relay_log_recovery=1即可。1.5二进制日志与InnoDB事务日志不同步由于各种原因，当MySQL复制遇到服务器崩溃、电源故障、磁盘损坏、内存或网络错误时，很难恢复当时丢失的数据。复制几乎总是需要在某个时候重新启动。2未定义服务器ID如果my.cnf中没有定义服务器ID，虽然可以通过CHANGEMASTERTO设置备库，但是会遇到：mysql>STARTSLAVE;ERROR1200(HY000)：服务器启动复制时us位配置为slave；在配置文件中或使用CHANGEMASTERTO修复此错误可能令人困惑。因为我们可能已经通过CHANGEMASTERTO建立备库，并且通过SHOWMASTERSTATUS确认了，为什么会出现这样的错误呢？我们可以通过SELECT@@server_id得到一个值。需要注意的是，这个值只是默认值，我们必须为备库显式设置服务器ID。这是my.cnf中显示的配置服务器ID。3、对未复制数据的依赖如果主库上有数据库或数据表，备库上不存在，复制很容易中断，反之亦然。对于前者，假设主库上有一个single_master表，备库上没有。在主库对这张表进行操作后，备库在重放这些操作时会出现问题，导致复制中断。对于后者，假设slave上有single_slave表而master上没有。当在主库上执行创建single_slave表的语句时，备库重放建表语句时会出现问题。对于这个问题，我们能做的就是未雨绸缪：主备切换时，尝试对比切换后的数据，看看是否存在不一致的表或库。一定不要对备库进行写操作。4丢失的临时表临时表与基于语句的复制不兼容。如果备用数据库崩溃或正常关闭，则复制线程拥有的任何临时表都会丢失。重启备库后，所有依赖临时表的语句都会失败。当复制过程中出现找不到临时表的异常时，可以这样做：直接跳过错误，或者手动创建一个同名同结构的表来替换消失的临时表。临时表的特点：只对创建临时表的连接可见。不会与其他同名临时表的连接发生冲突；当连接关闭而没有明确删除它们时，它们就会消失。4.1最好使用临时表预留一个专用的数据库，在里面创建持久化表，作为伪临时表来模拟临时表的特性。只需使用CONNETCTION_ID()的返回值来为临时表创建一个唯一的名称。伪临时表的优点和缺点：更容易调试应用程序。可以通过其他连接查看应用正在维护的数据；缺点：比临时表开销大。伪临时表的创建速度较慢，因为表的.frm文件需要刷新到磁盘。5、InnoDB锁读导致主从数据不一致。共享锁用于序列化更新，以确保备库复制期间的数据一致性。在某些情况下，锁定读取可以防止混淆。假设有两张表：tab1没有数据，tab2只有一行数据，值为99。此时有两个事务更新数据。事务1将tab2的数据插入到tab1中，事务2更新tab2。事务1使用获取tab2数据时，加共享锁，插入tab1；同时，事务2更新tab2数据时，由于写操作的独占锁机制，无法获取到tab2的锁，等待；事务1插入数据后，删除共享锁锁，提交事务，写入binlog（此时tab1和tab2的记录值都是99）；事务2获取锁，更新数据，提交事务，写入binlog（此时tab1的记录值为99，tab2的记录值为99）。值为100）。在上面的过程中，第二步非常重要。事务2尝试更新tab2表，这需要更新行上的排它锁（写锁）。排他锁与其他锁不兼容，包括事务1对行记录的共享锁。因此事务2需要等待事务1完成。备库根据binlog进行复制时，会先执行事务1，再按相同的顺序执行事务2。主备数据一致。同一个流程，如果第一步事务1没有加共享锁，则流程变为：事务1无锁读取tab2数据，插入tab1（此时tab1和tab2的记录值都是99);同时，事务2更新tab2数据，先提交事务1的事务，写入binlog（此时tab1的记录值为99，tab2的记录值为100）；事务1提交事务并写入binlog（此时记录值不变）;mysqldump--single-transaction--all-databases--master-data=1--host=server1|mysql--host=server2需要注意的是，在上面的过程中，先提交事务2，先写入binlog。复制到备库时，同样先执行事务2，将tab2的记录值更新为100。然后执行事务1，读取tab2数据，插入tab1，所以最后的结果是tab1的记录值和tab2都是100，很明显，数据和主库有出入。大多数情况下建议将innodb_unsafe_for_binlog的值设置为0。基于行的复制不存在这个问题，因为它记录的是数据变化而不是语句。6过度复制延迟导致延迟的两种方式：突然延迟然后赶上；steadydelayincrease前者通常是一个SQL执行时间过长导致的，而后者即使没有慢语句也会出现。对于前者，我们可以通过备库上的慢查询日志进行优化。在备库上启用log_slow_slave_statement选项，将复制线程执行的语句记录在慢查询日志中。至于后者，目前还没有针对性的解决方案，只能通过各种方式来提高备份数据库的复制效率。而当我们要对备库进行优化时，我们会发现除了购买更快的磁盘和CPU之外，没有太大的调优空间。一些额外的工作只能通过MySQL选项来禁止，以减少备库的复制。可以采用以下方法：使用InnoDB引擎时，将innodb_flush_log_at_trx_commit的值设置为2，使备库不频繁刷新磁盘，提高事务提交效率。禁用二进制日志记录。对于MyISAM，将innodb_locks_unsafe_for_binlog设置为1，将delay_key_write设置为ALL。应该注意的是，这些设置以安全换取速度。请记住在将备用数据库提升为主数据库时将这些选项设置回安全值。拆分效率低的复制SQL，将复杂语句中的SELECT和UPDATE语句分开，减少复制消耗，提高效率。总结复制问题，需要区分是master问题还是slave问题。master的问题找binlog，slave的问题找relaylog。
