MySQL 复制 - 性能与扩展性的基石 3：常见问题及解决方案
主备复制过程中有很大可能会出现各种问题，接下来我们就讨论一些比较普遍的问题，以及当遇到这些问题时，如何解决或者预防问题发生。

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

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

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

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

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

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

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

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

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

2 未定义的服务器 ID
如果没有再 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。

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

对于后者，假设备库上有一个 single_slave 表，主库没有。在主库上执行创建 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 的记录值为 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 复制延迟过大
产生延迟的两种方式

突然产生延迟，然后再跟上；
稳定的延迟增大
前者通常是由于一条执行时间过长的 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。
原文地址https://www.cnblogs.com/BeiGuo-FengGuang/p/10666693.html