MySQL 复制 - 性能与扩展性的基石 3：常见问题及解决方案

摘要：问题原因非正常关机导致没有把数据及时的写入硬盘。丢失的临时表临时表和基于语句的复制方式不相容。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。临时表的特性只对创建临时表的连接可见。

主备复制过程中有很大可能会出现各种问题，接下来我们就讨论一些比较普遍的问题，以及当遇到这些问题时，如何解决或者预防问题发生。

问题描述：服务器崩溃、断电、磁盘损坏、内存或网络错误等问题，导致数据损坏或丢失。
问题原因：非正常关机导致没有把数据及时的写入硬盘。

这种问题，一般可以分为几种情况导致：

问题未发生，避免方案：设置主库的 sync_binlog 选项为 1。此选项表示 MySQL 是否控制 binlog 的刷新。当设置为 1 时，表示每次事务提交，MySQL 都会把 binlog 刷下去，是最安全，性能损耗也最大的设置。
问题已发生，解决方案：指定备库从下一个二进制日志的开头重新读日志。但是一些日志事件将永久性丢失。可以使用 Percona Toolkit 中的 pt-table-checksum 工具来检查主备一致性，以便于修复。

备库意外关闭重启时，会去读 master.info 文件以找到上次停止复制的位置。但是在意外关闭的情况下，这个文件存储的信息可能是错误的。此外，备库也可能会尝试重新执行一些二进制文件，这可能会导致唯一索引错误。我们可以通过 Percona Toolkit 中的 pt-slave-restart 工具，帮助备库重新执行日志文件。

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

如果主库上的二进制日志损坏，除了忽略损坏的位置外，别无选择。在忽略存货位置后，我们可以通过 FLUSH LOGS 命令在主库开始一个新的日志文件，然后将备库指向该文件的开始位置。

如果主库上的日志是完好的，有两种解决方案：
1) 手工处理。找到 master binlog 日志的 pos 点，然后重新同步。

2) 自动处理。mysql5.5 考虑到 slave 宕机中继日志损坏这一问题，只要在 slave 的的配置文件 my.cnf 里增加一个参数 relay_log_recovery=1 即可。

由于各种各样的原因，MySQL 的复制碰到服务器崩溃、断电、磁盘损坏、内存或网络错误时，很难恢复当时丢失的数据。几乎都需要从某个点开始重启复制。

如果没有再 my.cnf 里定义服务器 ID，虽然可以通过 CHANGE MASTER TO 来设置备库，但在启动复制时会遇到：

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

这个报错可能会让人困惑。因为我们可能已经通过 CHANGE MASTER TO 设置了备库，并且通过 SHOW MASTER STATUS 也确认了，为什么还会有这样的报错呢？我们通过 SELECT @@server_id 可以获得一个值，要注意的是，这个值只是默认值，我们必须为备库显式地设置服务器 ID。也就是在 my.cnf 里显示的设置服务器 ID。

如果在主库上有备库上不存在的数据库或数据表，复制就很容易中断，反之亦然。
对于前者，假设在主库上有一个 single_master 表，备库没有。在主库上对此表进行操作后，备库在尝试回放这些操作时就会出现问题，导致复制中断。

对于后者，假设备库上有一个 single_slave 表，主库没有。在主库上执行创建 single_slave 表的语句时，备库在回放该建表语句时就会出现问题。

对于此问题，我们能做的就是做好预防：

主备切换时，尽量在切换后对比数据，查清楚是否有不一致的表或库。

一定不要在备库执行写操作。

临时表和基于语句的复制方式不相容。如果备库崩溃或者正常关闭，任何复制线程拥有的临时表都会丢失。重启备库后，所有依赖于该临时表的语句都会失败。

复制时出现找不到临时表的异常时，可以做：

直接跳过错误，或者手动地创建一个名字和结构相同的表来代替消失的的临时表。

临时表的特性：

只对创建临时表的连接可见。不会和其他拥有相同名字的临时表的连接起冲突；

随着连接关闭而消失，无须显式的移除它们。

保留一个专用的数据库，在其中创建持久表，把它们作为伪临时表，以模拟临时表特性。只需要通过 CONNETCTION_ID() 的返回值，给临时表创建唯一的名字。

伪临时表的优劣势
优势：

更容易调试应用程序。可以通过别的连接来查看应用正在维护的数据；

劣势：

比临时表多一些开销。创建较慢伪临时表会较慢，因为表的 .frm 文件需要刷新到磁盘。

使用共享锁，串行化更新，保证备库复制时数据一致。

某些情况下，加锁读可以防止混乱。假设有两张表：tab1 没有数据，tab2 只有一行数据，值为 99。此时，有两个事务更新数据。事务 1 将 tab2 的数据插入到 tab1，事务 2 更新 tab2。

事务 1 使用获取 tab2 数据时，加入共享锁，并插入 tab1；

同时，事务 2 更新 tab2 数据时，由于写操作的排它锁机制，无法获取 tab2 的锁，等待；

事务 1 插入数据后，删除共享锁，提交事务，写入 binlog（此时 tab1 和 tab2 的记录值 都是 99）；

事务 2 获取到锁，更新数据，提交事务，写入 binlog（此时 tab1 的记录值为 99，tab2 的记录值为 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。基于行的复制由于记录了数据的变化而非语句，因此不会存在这个问题。

产生延迟的两种方式

突然产生延迟，然后再跟上；

稳定的延迟增大

前者通常是由于一条执行时间过长的 SQL 导致，而后者即使在没有慢语句也会出现。

对于前者，我们可以通过备库上的慢查询日志来进行优化。在备库上开启 log_slow_slave_statement 选项，可以在慢查询日志中记录复制线程执行的语句。

而对于后者，没有针对性的解决方案，只能通过各种方式提高备库的复制效率。而当我们想去对备库做优化时，会发现，除了购买更快的磁盘和 CPU，并没有太多的调优空间。只能通过 MySQL 选项禁止某些额外的工作以减少备库的复制。可以通过下面几种方式：

使用 InnoDB 引擎时，设置 innodb_flush_log_at_trx_commit 值为 2，来使备库不要频繁的刷新磁盘，以提高事务提交效率。

禁止二进制日志记录。把 innodb_locks_unsafe_for_binlog 设置为 1，并把 MyISAM 的 delay_key_write 设置为 ALL。要注意的是，这些设置是以安全换取速度，在将备库提升为主库时，记得把这些选项设置回安全的值。

拆分效率较低的复制 SQL，分离复杂语句中的 SELECT 和 UPDATE 语句，降低复制消耗，提高效率。

复制问题要分清楚是 master 的问题，还是 slave 的问题。

master 问题找 binlog，slave 问题找 relaylog。