MySQL的`主从复制`：如何利用`semi-synchronous`与`super_read_only`实现高可用与读写分离？

MySQL主从复制：Semi-Synchronous与Super_Read_Only打造高可用读写分离

大家好，今天我们来深入探讨MySQL主从复制，特别是如何巧妙地结合 semi-synchronous 和 super_read_only 这两个特性，来构建一个高可用、读写分离的MySQL架构。 在实际生产环境中，这种组合能够显著提升系统的稳定性和性能，减轻主库的压力，并提供一定程度的数据安全性。

1. 主从复制基础回顾

在深入 semi-synchronous 和 super_read_only 之前，我们先简单回顾一下MySQL的主从复制。 主从复制的核心思想是将主库(Master)上的数据变更（包括INSERT、UPDATE、DELETE等）通过二进制日志(Binary Log)记录下来，然后从库(Slave)读取这些日志并执行，从而保持与主库数据的一致性。

复制过程大致如下：

1. 主库写入： 主库接收客户端的写请求，修改数据，并将修改操作记录到二进制日志中。
2. 从库连接： 从库启动I/O线程，连接到主库，请求主库发送二进制日志。
3. 日志传输： 主库将二进制日志发送给从库。
4. 日志存储： 从库接收到二进制日志后，将其存储到中继日志(Relay Log)中。
5. SQL线程执行： 从库启动SQL线程，读取中继日志中的事件，并在从库上执行这些事件，从而更新从库的数据。

主从复制的优点：

• 读写分离： 将读请求分发到从库，减轻主库的压力。
• 数据备份： 从库相当于主库的一个实时备份。
• 故障切换： 当主库发生故障时，可以将从库切换为主库，保证服务的可用性。
• 分析型查询： 将复杂的分析型查询放到从库执行，避免影响主库的性能。

2. Semi-Synchronous Replication：半同步复制

默认情况下，MySQL的主从复制是异步的(Asynchronous Replication)。 这意味着主库在执行完写操作后，不需要等待任何从库的确认，就可以立即返回给客户端。 这种方式性能最好，但数据一致性风险也最高。 如果主库在将数据同步到从库之前发生故障，那么从库上的数据就会丢失一部分最新的数据。

为了解决这个问题，MySQL引入了半同步复制(Semi-Synchronous Replication)。 半同步复制介于异步复制和完全同步复制之间。 它的工作方式是： 主库在执行完写操作并提交事务后，必须至少收到一个从库已经接收到该事务的二进制日志的确认，才能返回给客户端。

半同步复制的优点：

• 数据安全性： 提高了数据安全性，确保至少有一个从库拥有最新的数据。
• 可接受的性能损失： 相对于异步复制，性能会有所下降，但相对于完全同步复制，性能损失较小。

半同步复制的配置：

1. 安装插件： 在主库和从库上都要安装半同步复制插件。

   -- 在主库上安装
   INSTALL PLUGIN rpl_semi_sync_master SONAME 'rpl_semi_sync_master.so';
   
   -- 在从库上安装
   INSTALL PLUGIN rpl_semi_sync_slave SONAME 'rpl_semi_sync_slave.so';

2. 启用插件： 启用主库和从库上的半同步复制插件。

   -- 在主库上启用
   SET GLOBAL rpl_semi_sync_master_enabled = 1;
   
   -- 在从库上启用
   SET GLOBAL rpl_semi_sync_slave_enabled = 1;

3. 配置主库： 设置主库等待从库确认的超时时间。

   SET GLOBAL rpl_semi_sync_master_timeout = 10; -- 单位：秒

4. 配置从库： 配置从库连接到主库并启动复制。

   CHANGE MASTER TO
       MASTER_HOST='master_ip',
       MASTER_USER='replication_user',
       MASTER_PASSWORD='replication_password',
       MASTER_LOG_FILE='binlog.000001', -- 替换为实际的binlog文件名
       MASTER_LOG_POS=4,               -- 替换为实际的binlog位置
       GET_MASTER_PUBLIC_KEY=1,  -- 如果开启了SSL连接，需要设置
       MASTER_SSL_VERIFY_SERVER_CERT=0; -- 如果开启了SSL连接，需要设置
   START SLAVE;

5. 验证： 检查主库和从库的状态，确认半同步复制已经启用。

   -- 在主库上检查
   SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_master%';
   
   -- 在从库上检查
   SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_slave%';

关键参数说明：

参数名	描述
rpl_semi_sync_master_enabled	启用或禁用主库的半同步复制。
rpl_semi_sync_slave_enabled	启用或禁用从库的半同步复制。
rpl_semi_sync_master_timeout	主库等待从库确认的超时时间。如果在指定的时间内没有收到任何从库的确认，主库将切换回异步复制模式。
rpl_semi_sync_master_wait_for_slave_count	主库需要等待多少个从库确认才能返回给客户端。 默认值为1。 如果设置大于1，则需要更多的从库参与到半同步复制中，以提高数据安全性，但也会降低性能。

注意事项：

• 半同步复制至少需要一个从库。
• 如果所有从库都发生故障，主库会切换回异步复制模式。
• 合理设置 rpl_semi_sync_master_timeout 参数，避免主库长时间等待，影响性能。
• 在高并发场景下，半同步复制可能会导致一定的性能下降，需要根据实际情况进行评估。

3. Super_Read_Only：超级只读模式

super_read_only 是MySQL 5.7.8版本引入的一个全局系统变量，它比传统的 read_only 变量更加严格。 当 super_read_only 启用时，即使拥有 SUPER 权限的用户，也无法在从库上执行任何写操作。

super_read_only 的优点：

• 数据安全： 彻底防止误操作导致从库数据被意外修改，保证数据的一致性。
• 读写分离： 强制将所有写操作引导到主库，确保读写分离策略的有效执行。
• 简化管理： 降低了人为干预从库数据导致问题的风险，简化了管理工作。

super_read_only 的配置：

-- 在从库上启用
SET GLOBAL super_read_only = 1;

super_read_only 与 read_only 的区别：

特性	read_only	super_read_only
作用范围	限制非 SUPER 权限的用户执行写操作。	限制 所有用户 (包括拥有 SUPER 权限的用户) 执行写操作。
绕过权限	拥有 SUPER 权限的用户可以绕过 read_only 限制。	即使拥有 SUPER 权限的用户也无法绕过 super_read_only 限制。
适用场景	适用于需要限制普通用户修改从库数据的场景。	适用于需要完全禁止从库数据被修改的场景，例如，严格的读写分离环境，或者需要防止误操作导致数据不一致的情况。
启用/禁用方式	SET GLOBAL read_only = 1; 或 SET GLOBAL read_only = 0;	SET GLOBAL super_read_only = 1; 或 SET GLOBAL super_read_only = 0;
主要目标	防止普通用户意外修改从库数据。	绝对防止任何用户（包括具有 SUPER 权限的用户）修改从库数据，从而确保数据完整性和读写分离的严格执行。

注意事项：

• 启用 super_read_only 后，任何写操作都会被拒绝，包括使用 SUPER 权限的用户。
• 在进行维护操作时，需要先禁用 super_read_only，然后再执行相应的操作。
• super_read_only 通常与 read_only 结合使用，以实现更严格的权限控制。

4. Semi-Synchronous + Super_Read_Only：高可用读写分离的最佳实践

将 semi-synchronous 和 super_read_only 结合使用，可以构建一个高可用、读写分离的MySQL架构，既保证了数据的一致性，又提高了系统的稳定性和性能。

架构设计：

1. 主库(Master)： 负责处理所有的写操作。
2. 从库(Slave)： 负责处理所有的读操作。
3. 半同步复制： 主库和从库之间使用半同步复制，确保至少有一个从库拥有最新的数据。
4. 超级只读模式： 在所有从库上启用 super_read_only，彻底禁止从库上的写操作。

工作流程：

1. 客户端发送写请求到主库。
2. 主库执行写操作，并将修改操作记录到二进制日志中。
3. 主库等待至少一个从库确认已经接收到该事务的二进制日志。
4. 主库返回给客户端写操作成功。
5. 客户端发送读请求到从库。
6. 从库直接读取数据并返回给客户端。

优点：

• 高可用性： 当主库发生故障时，可以快速切换到从库，保证服务的可用性。 由于半同步复制保证了至少有一个从库拥有最新的数据，因此切换后的数据丢失风险很小。
• 读写分离： 所有读请求都由从库处理，减轻了主库的压力，提高了系统的性能。
• 数据安全： 半同步复制保证了数据的一致性，super_read_only 彻底禁止了从库上的写操作，防止误操作导致数据被意外修改。
• 可扩展性： 可以根据读请求的压力，增加从库的数量，从而提高系统的可扩展性。

示例配置：

主库配置 (Master):

-- 启用二进制日志
log_bin = mysql-bin
server_id = 1

-- 配置半同步复制
INSTALL PLUGIN rpl_semi_sync_master SONAME 'rpl_semi_sync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10;

从库配置 (Slave):

server_id = 2

-- 配置半同步复制
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'rpl_semi_sync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 启用超级只读模式
SET GLOBAL super_read_only = 1;

-- 配置连接到主库
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=4,
    GET_MASTER_PUBLIC_KEY=1,
    MASTER_SSL_VERIFY_SERVER_CERT=0;
START SLAVE;

注意事项：

• 在实际生产环境中，还需要考虑监控、备份、故障切换等问题，需要完善的运维方案。
• 根据实际业务需求，选择合适的硬件配置和网络环境。
• 定期进行性能测试和压力测试，确保系统能够满足业务需求。
• 仔细规划主库和从库的 server_id，确保唯一性。
• 监控复制延迟，并根据延迟情况调整参数或优化SQL语句。

5. 故障切换策略

虽然 semi-synchronous 提高了数据安全性，但主库发生故障时，仍然需要进行故障切换。 以下是一种常见的故障切换策略：

1. 监控： 使用监控系统实时监控主库的状态。

2. 故障检测： 当监控系统检测到主库发生故障时，触发故障切换流程。

3. 选择新主库： 从剩余的从库中选择一个作为新的主库。 通常选择复制延迟最小的从库。

4. 提升从库为主库： 将选定的从库提升为主库。 这通常包括停止从库的复制，并修改其配置，使其可以接受写请求。

   -- 在选定的从库上执行
   STOP SLAVE;
   SET GLOBAL read_only = 0; -- 关闭只读模式 (如果开启了 read_only)
   SET GLOBAL super_read_only = 0; -- 关闭超级只读模式
   RESET MASTER; -- 重置 master 信息，清空 binlog index 文件

5. 修改其他从库的配置： 修改其他从库的配置，使其复制新的主库。

   -- 在其他从库上执行
   STOP SLAVE;
   CHANGE MASTER TO
       MASTER_HOST='new_master_ip',
       MASTER_USER='replication_user',
       MASTER_PASSWORD='replication_password',
       MASTER_LOG_FILE='binlog.000001', -- 替换为新的binlog文件名
       MASTER_LOG_POS=4,               -- 替换为新的binlog位置
       GET_MASTER_PUBLIC_KEY=1,  -- 如果开启了SSL连接，需要设置
       MASTER_SSL_VERIFY_SERVER_CERT=0; -- 如果开启了SSL连接，需要设置
   START SLAVE;

6. 更新应用程序配置： 更新应用程序的配置，使其连接到新的主库。

7. 验证： 验证新的主从复制是否正常工作。

自动化故障切换：

可以使用一些工具来实现自动化故障切换，例如：

• MHA (MySQL High Availability)： 一个流行的MySQL高可用解决方案，可以自动检测主库故障并进行故障切换。
• Orchestrator： 一个MySQL拓扑管理和可视化工具，可以自动进行故障切换。
• Keepalived + VRRP： 使用Keepalived和VRRP协议来实现虚拟IP地址的漂移，从而实现故障切换。

6. 如何选择：半同步复制，异步复制，还是增强半同步复制？

MySQL 5.7 之后引入了增强半同步复制，也叫做无损半同步复制。相比普通的半同步复制，它能保证在主库宕机的情况下，数据绝对不丢失。在选择复制方式的时候，需要考虑以下因素：

• 数据一致性要求： 如果对数据一致性要求非常高，例如金融系统，那么应该选择增强半同步复制。如果允许一定程度的数据丢失，可以选择半同步复制。如果对数据一致性要求不高，可以选择异步复制。
• 性能要求： 异步复制性能最好，半同步复制性能略差，增强半同步复制性能最差。需要根据业务的性能要求选择合适的复制方式。
• 成本： 增强半同步复制需要更多的硬件资源和网络带宽，成本更高。

简单来说，如果对数据安全性要求最高，并且能接受一定的性能损失，那么选择增强半同步复制。如果对数据安全性要求较高，并且对性能有一定要求，那么选择半同步复制。如果对数据安全性要求不高，并且对性能要求很高，那么选择异步复制。

7. 实际案例分析

假设我们有一个电商网站，数据库用于存储商品信息、用户信息、订单信息等。 该网站的读请求量远大于写请求量，因此需要进行读写分离。 同时，由于订单信息非常重要，不能容忍数据丢失，因此需要保证数据的一致性。

解决方案：

1. 架构： 采用一主多从的架构。
2. 复制： 主库和从库之间使用半同步复制，确保至少有一个从库拥有最新的订单信息。
3. 只读： 在所有从库上启用 super_read_only，防止误操作导致订单信息被意外修改。
4. 路由： 使用中间件（例如：ProxySQL）将读请求路由到从库，将写请求路由到主库。
5. 监控： 使用监控系统实时监控主库和从库的状态，并配置自动故障切换。

配置示例：

主库配置：

-- 启用二进制日志
log_bin = mysql-bin
server_id = 1

-- 配置半同步复制
INSTALL PLUGIN rpl_semi_sync_master SONAME 'rpl_semi_sync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10;

从库配置：

server_id = 2

-- 配置半同步复制
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'rpl_semi_sync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 启用超级只读模式
SET GLOBAL super_read_only = 1;

-- 配置连接到主库
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=4,
    GET_MASTER_PUBLIC_KEY=1,
    MASTER_SSL_VERIFY_SERVER_CERT=0;
START SLAVE;

ProxySQL配置：

-- 配置主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (10, 'master_ip', 3306, 100, 1000);

-- 配置从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (20, 'slave1_ip', 3306, 50, 500);
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (20, 'slave2_ip', 3306, 50, 500);

-- 配置读写分离规则
INSERT INTO mysql_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES (1, 'SELECT * FROM orders WHERE ...', 20, 1);
INSERT INTO mysql_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES (2, 'INSERT INTO orders ...', 10, 1);
INSERT INTO mysql_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES (3, 'UPDATE orders ...', 10, 1);
INSERT INTO mysql_rules (rule_id, match_digest, destination_hostgroup, apply) VALUES (4, 'DELETE FROM orders ...', 10, 1);

LOAD MYSQL RULES TO RUNTIME;
SAVE MYSQL RULES TO DISK;

通过以上配置，我们可以实现高可用、读写分离的MySQL架构，既保证了订单信息的安全性，又提高了电商网站的性能。

8. 最后，再回顾一下

总而言之，semi-synchronous 和 super_read_only 是MySQL主从复制中两个非常重要的特性。 结合使用这两个特性，可以有效地提高MySQL数据库的可用性、数据安全性和性能，并简化管理。在实际生产环境中，需要根据具体的业务需求和场景，选择合适的配置方案。同时，还需要关注监控、备份、故障切换等问题，以构建一个稳定可靠的MySQL架构。