全栈开发之MySQL主从同步，读写分离后可能引发的问题

背景

我们的业务场景是写少读多，一开始采用的是单库完成数据的读写操作。随着数据的增多，从开始的0到十万再到百万级，一路经过索引优化，SQL优化等操作，勉强撑住基本的查询，然而写操作与读操作相互影响，动不动就把数据库服务器的CPU打满。。

当面临这种问题时，一般有两种解决的思路：垂直扩展（换更高配置的机器：CPU、内存、磁盘、带宽等）与水平扩展（加机器，多数据库服务实例，最好是同等配置）。垂直扩展在初期可以暂时解决问题，但随着投入成本的增加，后续可能并不能达到预期的收益，不是长久之计。水平扩展可通过一些相对较低配置的机器构成一个集群提供服务，从而分散单库的压力，实现基本的扩展。

这里就介绍下关系型数据库 MySQL 的可扩展性：当需要增加资源以执行更多工作时，系统能够获得划算的同等提升（equal bang for the buck）的能力。 MySQL 的可扩展性又分为写扩展和读扩展，显然，我们这里更多地讨论读扩展：我们采用了主从同步、读写分离的方案解决前面提到的数据库服务性能问题，关于主从复制的搭建就不多作介绍了，后面附上之前文章的链接；每当引入新的技术时，必然会带来新的问题，这里就重点讨论下采用 MySQL 主从同步，读写分离后可能引发的问题以及相应的解决方法。

当无法容忍任何延迟时怎么办？

无论是基于语句的复制，还是基于行的复制，都是通过在主库上记录二进制日志，在从库上重放日志的方式实现异步的数据复制。既然是异步复制，那么就会出现主从的数据不一致的情况，即存在延迟。可是在有些特殊的场景下，我们的业务要求零延时（相信我，你肯定会遇到这类情况），这时候怎么办？答案也很简单，很直接：强制读主库。

在落地实现层面，我们使用中间件 ShardingSphere 实现了读写分离。那么，关于如何强制读主库， ShardingSphere 也提供了对应的解决方案，核心代码如下：

// 强制路由主库完成读取
try (
    HintManager hintManager = HintManager.getInstance();
) {
    hintManager.setMasterRouteOnly();
    // 下面调用自己业务查询的方法，会切换到主库进行查询
    ...
}

想忽略某些表不进行同步怎么办？

为了降低主从数据库服务器的负载，有些数据表可能就根本不需要同步到从库，比如一些存放原始数据的临时表，它们不参与任何的业务，这时候就需要对这类表进行过滤。

编辑 MySQL 配置文件 vi /etc/my.cnf ，添加如下内容，忽略指定的数据库表即可。

replicate-wild-ignore-table=db.tb_raw_data

同步出错了怎么办？

导致同步出错的原因会有很多，比如突然断电、主从库服务意外停止等，这时候首先应停止从库 stop slave ，然后可能还需要通过 binlog 以及主从库的同步位置、复制事件进行排查；在实际中我们遇到的同步出错更多的是一开始的管理不规范，比如从库停止同步，报错信息有：

• 1032：从库中找不到要更新的记录；
• 1062：从库中出现主键冲突；

在说明同步出错的解决方法之前，我们先分析下上述的同步出错是什么原因导致的。当时，经过排查发现：有人直接在从库上进行了诸如数据导入与删除的操作。这个太危险了，所以后来我们的从库对开发人员专门创建账户，仅开放了查询权限，再后来从库直接不允许通过公网访问。以防止从库被人为意外修改，从而引发数据不同步。

接下来，讨论下上述人为导致的同步出错问题如何解决，其实，最直接简便的便是跳过错误了。

编辑 MySQL 配置文件 vi /etc/my.cnf ，添加如下内容，忽略指定的错误编号。

slave_skip_errors=1032,1062

修改配置文件后重启从库，会自动同步并跳过错误，待从库赶上主库时，再将跳过错误的配置移除，重启服务即可。

当然，还可以配置跳过错误的个数：set GLOBAL SQL_SLAVE_SKIP_COUNTER=n; # n为正整数，有几个错误，就跳过几个

同步延迟了怎么办？

当数据量突然增大时（瞬间大批量数据写入主库时），主从同步延迟不断增大。。这种情况我们曾经遇到过，当时通过多线程（并行）复制的手段解决了这一问题，使从库快速赶上主库。

• 查看当前是否已使用了多线程

mysql> SHOW VARIABLES LIKE '%slave_parallel%'; 
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+
2 rows in set (0.00 sec)

slave_parallel_workers 为0，表明当前是单线程同步，那么可以改为多线程提升同步效率。

• 修改为多线程同步：4

mysql> STOP SLAVE SQL_THREAD;SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';SET GLOBAL slave_parallel_workers=4;START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

• 查看当前是否已使用了多线程

mysql> SHOW VARIABLES LIKE '%slave_parallel%'; 
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.01 sec)

Note：使用多线程复制时，需要注意数据库的版本应高于5.5。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

当需要将一个从库提升为主库怎么办？

正常情况下，这里的将从库提升为主库的操作还是比较简便的。

1. 停止向旧主库写入；
2. 让从库追赶上主库；
3. 将一台从库配置为新的主库；
4. 将从库与写操作指向新的主库，然后开启主库的写入。

从库越多越好吗？

我们实际采用的是一主多从的存储架构，在有少量写和大量读时，这种配置是非常给力的，可以把读分摊到多个从库上，那么这时就会产生一个疑问，我们的从库可以再多加几个吗，可以无限扩展吗？显然，可以多加几个从库，但并不能无限扩展。

一个从库对主库造成的开销是很低的。主要包括：启用二进制日志的开销、网络IO开销、唤醒复制线程发送事件的开销等。但是当从库的数量增加到一定数量，会对主库造成过大的负担，甚至主从之间的带宽成为瓶颈，引发从库的数据同步延迟等问题，所以 MySQL 的读扩展并不能无限扩展。

如何通过复制扩展写操作？

很不幸，复制只能扩展读操作，无法扩展写操作。。

如果真的希望扩展写操作，基本只有分库分表了，或者类似我们如今微服务的数据隔离设计，一个服务一个库，每个微服务管好自己的数据，即按照业务分库。我们用到的中间件 ShardingSphere 本身除了支持读写分离，也支持分库分表，不过实际中我们并没有走这一步，毕竟分库分表后必然又会引入新的复杂性。

从库同步账号的密码忘记了怎么办？

因为主从复制也不是每天都要进行配置，我们的读写分离1主2从，运行了一年之后想要再增加第3台从库，这时突然发现忘记了之前配置从库使用的复制账号与密码信息，这就尴尬了。。

不怕，我们可以看下以前配置主从时用的复制账号信息，在每个从库服务器上，都有一个 master.info 文件，里面有我们需要的信息(^▽^)。

cat /var/lib/mysql/master.info

这样，就找回了复制账号与密码信息；此时，你在细品以下：这是个安全隐患。因此，一定要注意 /var/lib/mysql/master.info 这个文件的权限控制。

总结

实际上，通过实践 MySQL 主从同步、读写分离的读扩展架构，共涉及1主3从四台数据库服务器，帮我们实现了在多个核心数据表单表数据过亿的情况下依然具从良好的查询性能。以上关于主从复制、读写分离的问题都是我们在实际实践中真实遇到的问题，这里做个简单整理。MySQL的主从复制是一门很大的学问，值得我们做进一步的探索。

Reference

关于 MySQL 主从同步、读写分离，我之前零星地总结过遇到问题与解决方法。如果恰巧你也遇到了类似问题，那么很高兴能够为你节省点时间。

• 挂载并初始化Linux数据盘，完成MySQL数据迁移
• Ubuntu下对MySQL指定数据库定时备份
• mysqldump备份时导致所有数据表锁定，无法提供服务
• MySQL主从同步配置
• 问题排查：线上MySQL启动报错：Job for mysqld.service failed because the control process exited with error code.
• 一个不规范操作导致MySQL主从同步中断（GTID模式）
• 数据量激增，导致MySQL主从同步延迟

If you have any questions or any bugs are found, please feel free to contact me.

Your comments and suggestions are welcome!