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

时间:2022-09-07 12:02:20

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

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 表的语句时,备库在回放该建表语句时就会出现问题。

对于此问题,我们能做的就是做好预防:

  1. 主备切换时,尽量在切换后对比数据,查清楚是否有不一致的表或库。
  2. 一定不要在备库执行写操作。

4 丢失的临时表

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

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

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

临时表的特性:

  1. 只对创建临时表的连接可见。不会和其他拥有相同名字的临时表的连接起冲突;
  2. 随着连接关闭而消失,无须显式的移除它们。

4.1 更好使用临时表的方式

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

伪临时表的优劣势

优势:

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

劣势:

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

5 InnoDB 加锁读导致主备数据不一致

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

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

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

  1. 事务 1 使用获取 tab2 数据时,加入共享锁,并插入 tab1;
  2. 同时,事务 2 更新 tab2 数据时,由于写操作的排它锁机制,无法获取 tab2 的锁,等待;
  3. 事务 1 插入数据后,删除共享锁,提交事务,写入 binlog(此时 tab1 和 tab2 的记录值 都是 99);
  4. 事务 2 获取到锁,更新数据,提交事务,写入 binlog(此时 tab1 的记录值为 99,tab2 的记录值为 100)。

上述过程中,第二步非常重要。事务 2 尝试去更新 tab2 表,这需要在更新的行上加排他锁(写锁)。排他锁与其他锁不相容,包括事务 1 在行记录上加的共享锁。因此事务 2 需要等待事务 1 完成。备库在根据 binlog 进行复制时,会按同样的顺序先执行事务 1,再执行事务 2。主备数据一致。

同样的过程,如果事务 1 在第一步时没有加共享锁,流程就变成:

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

  1. 事务 1 无锁读取 tab2 数据,并插入 tab1(此时 tab1 和 tab2 的记录值 都是 99);
  2. 同时,事务 2 更新 tab2 数据,先与事务 1 提交事务,写入 binlog(此时 tab1 的记录值为 99,tab2 的记录值为 100);
  3. 事务 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 复制延迟过大

产生延迟的两种方式

  1. 突然产生延迟,然后再跟上;
  2. 稳定的延迟增大

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

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

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

  1. 使用 InnoDB 引擎时,设置 innodb_flush_log_at_trx_commit 值为 2,来使备库不要频繁的刷新磁盘,以提高事务提交效率。
  2. 禁止二进制日志记录。把 innodb_locks_unsafe_for_binlog 设置为 1,并把 MyISAM 的 delay_key_write 设置为 ALL。要注意的是,这些设置是以安全换取速度,在将备库提升为主库时,记得把这些选项设置回安全的值。
  3. 拆分效率较低的复制 SQL,分离复杂语句中的 SELECT 和 UPDATE 语句,降低复制消耗,提高效率。

总结

  1. 复制问题要分清楚是 master 的问题,还是 slave 的问题。
  2. master 问题找 binlog,slave 问题找 relaylog。

我的博客即将同步至腾讯云+社区,邀请大家一同入驻:https://cloud.tencent.com/developer/support-plan?invite_code=16s4qjbz9ahdf