MySQL 高可用与复制架构实践

2025-01-20 MySQL 暂无评论 28 次阅读

MySQL 高可用与复制架构实践

前言

MySQL 作为互联网行业中应用最广泛的关系型数据库，其高可用性和数据一致性一直是架构设计的核心关注点。在实际生产环境中，单机 MySQL 无法满足高并发、高可用的需求，因此主从复制、双主架构、读写分离等技术方案应运而生。

本文将系统性地梳理 MySQL 复制架构的演进脉络，从主从复制延迟问题入手，深入探讨多线程复制（MTS）、GTID 主主复制、Keepalived 高可用架构以及 MySQL Proxy 读写分离等关键技术，并结合实际生产案例，给出完整的配置实践和故障处理方案。

一、主从复制延迟问题分析

在 MySQL 主从复制架构中，延迟问题一直是业界面临的主要挑战。主从延迟受网络、磁盘 I/O 等多种客观因素影响，但最核心的原因在于：当 Master 端写入过于繁忙时，Slave 端无法及时从 relay log 中读取并应用最新的变更记录。

对于数据实时性要求较高的业务场景，主从延迟意味着 Slave 上的数据并非最新，此时基于主从的读写分离方案就显得力不从心——业务读取 Slave 时可能拿到过时数据。因此，在实际架构中，Slave 通常只承担对实时性要求不高的查询任务（如报表统计、数据分析等）。

从 MySQL 5.6 版本开始，官方引入了多线程主从复制机制。众所周知，MySQL 采用单进程多线程的工作模式，线程数量直接影响其处理效率。在早期版本中，主从复制仅由单线程执行，这使得复制效率除了受客观因素影响外，还受到单线程处理能力的制约。

然而，MySQL 5.6 的多线程复制并不完善：它基于库级别（DATABASE）进行并行复制，即一个线程处理一个数据库的复制任务。在绝大多数生产环境中，大量操作集中在单个数据库中，因此多线程机制形同虚设，退化为实质上的单线程复制。

为此，MySQL 5.7 对多线程复制进行了彻底改进：引入基于逻辑时钟（类似 CPU 的时间片调度机制）的并行复制策略，同时在半同步复制（semi-sync）中支持通过 Performance Schema 表监控复制线程状态。这些改进使得 MySQL 自身导致的复制延迟大幅降低。

1.1 多线程复制配置（MySQL 5.7）

在 Slave 端执行以下配置：

-- 查看当前并行线程数（默认为 0，即单线程）
mysql> SHOW GLOBAL VARIABLES LIKE 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+

-- 根据实际情况设置并行线程数
mysql> SET GLOBAL slave_parallel_workers = 8;

-- 查看并行复制类型（默认为 DATABASE，即库级别并行）
mysql> SHOW GLOBAL VARIABLES LIKE '%slave_parallel_type%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+

-- 修改为基于逻辑时钟的并行方式（需先停止 Slave）
mysql> STOP SLAVE;
mysql> SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
mysql> START SLAVE;

配置完成后，可通过 SHOW FULL PROCESSLIST 查看 Slave 上的线程列表：

mysql> SHOW FULL PROCESSLIST;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info                  |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| 16 | root        | localhost | NULL | Query   |    0 | starting                                               | SHOW FULL PROCESSLIST |
| 29 | system user |           | NULL | Connect |    3 | Waiting for master to send event                       | NULL                  |
| 30 | system user |           | NULL | Connect |    3 | Slave has read all relay log; waiting for more updates | NULL                  |
| 31 | system user |           | NULL | Connect |    3 | Waiting for an event from Coordinator                  | NULL                  |
| 32 | system user |           | NULL | Connect |    3 | Waiting for an event from Coordinator                  | NULL                  |
| 33 | system user |           | NULL | Connect |    3 | Waiting for an event from Coordinator                  | NULL                  |
| 34 | system user |           | NULL | Connect |    3 | Waiting for an event from Coordinator                  | NULL                  |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+

可以看到 Coordinator 线程与多个 Worker 线程协同工作。在 Slave 的 datadir 目录下，会生成与 slave_parallel_workers 设置数量一致的线程相关文件。

1.2 持久化配置

将配置写入 /etc/my.cnf，使重启后永久生效：

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
注意：如果在运行过程中停止 Coordinator 和 Worker 线程，可能导致数据处于不一致状态。通常重启服务后会自动恢复一致性，但如果使用了非事务存储引擎或执行过 DDL，则需格外小心。

二、GTID 复制原理与主主搭建

2.1 GTID 工作原理

GTID（Global Transaction Identifier，全局事务标识符）是 MySQL 5.6 版本引入的一项重要特性，它为每一个事务分配全局唯一的 ID，极大简化了主从复制的管理和故障切换。其工作流程如下：

  1. 事务产生：Master 更新数据时，在事务执行前生成 GTID，并与事务一同记录到 binlog 中。
  2. 日志传输：Slave 的 I/O 线程将变更的 binlog 写入本地的 relay log。
  3. GTID 比对：SQL 线程从 relay log 中提取 GTID，然后与 Slave 端 binlog 中已记录的 GTID 进行比对。
  4. 去重跳过：如果该 GTID 的事务已经执行过，Slave 直接忽略。
  5. 事务执行：如果该 GTID 未被执行，Slave 从 relay log 中读取并执行该事务，并将 GTID 记录到本地 binlog。
  6. 索引选择：解析过程中会优先使用主键，无主键则使用二级索引，都没有则进行全表扫描。

2.2 GTID 的优势

  • 唯一性：一个事务对应一个唯一 ID，一个 GTID 在同一个服务器上只会执行一次，天然支持幂等。
  • 简化配置：GTID 复制模式不需要手动指定二进制日志文件名和位置（即不再依赖 MASTER_LOG_FILE 和 MASTER_LOG_POS）。
  • 故障切换自动化：减少人工干预，降低服务故障恢复时间。当 Master 宕机后，可自动从多个备机中提升新主机，无需手动计算位点。

2.3 GTID 主主复制搭建

2.3.1 my.cnf 配置

在两台 MySQL 服务器上分别添加以下配置：

[mysqld]
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
datadir                 = /data/mysql/data
log-bin                 = /data/mysql/binlog/mysql-bin
server_id               = 161                      # 两台服务器需保持不同
auto_increment_offset   = 1                         # 另一台配置为 2
auto_increment_increment= 2
log-slave-updates       = ON                        # 从库的变更也记录到 binlog
enforce_gtid_consistency= ON                        # 强制 GTID 一致性
gtid-mode               = ON                        # 开启 GTID 模式
binlog-format           = ROW
skip-name-resolve
explicit_defaults_for_timestamp
max_connections         = 2000
wait_timeout            = 3600
connect_timeout         = 800
max_allowed_packet      = 64M
character-set-server    = utf8

# InnoDB 配置
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 256M
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 20G

# 其他优化
join_buffer_size    = 64M
read_buffer_size    = 2M
sort_buffer_size    = 2M
table_open_cache    = 1800
thread_cache_size   = 384
sql_mode            = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file  = /var/run/mysqld/mysqld.pid
特别说明：auto_increment_offset 和 auto_increment_increment 是双主架构的关键配置。以两节点为例，offset 分别设为 1 和 2，increment 设为 2，这样两台服务器的自增 ID 就不会冲突（一台生成 1、3、5...，另一台生成 2、4、6...）。

2.3.2 创建复制账号

在 MasterA 上创建复制账号：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.116.103.90' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;

在 MasterB 上创建复制账号：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.8.99.105' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;

2.3.3 配置主从关系

MasterA 指向 MasterB：

mysql> CHANGE MASTER TO
    ->   MASTER_HOST = '10.183.103.232',
    ->   MASTER_USER = 'repl',
    ->   MASTER_PASSWORD = 'your_password',
    ->   MASTER_PORT = 3306,
    ->   MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

MasterB 指向 MasterA：

mysql> CHANGE MASTER TO
    ->   MASTER_HOST = '10.8.99.105',
    ->   MASTER_USER = 'repl',
    ->   MASTER_PASSWORD = 'your_password',
    ->   MASTER_PORT = 3306,
    ->   MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

确认两台服务器的 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes，即表示主主复制搭建完成。

MASTER_AUTO_POSITION = 1 表示启用 GTID 自动定位，不再需要手动指定 MASTER_LOG_FILE 和 MASTER_LOG_POS。如果需要手动定位，仍可使用传统方式：

CHANGE MASTER TO
  MASTER_HOST = '10.140.134.197',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'your_password',
  MASTER_PORT = 3307,
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 3456;

2.4 使用 Xtrabackup 搭建 GTID 主从

对于已有大量数据的场景，推荐使用 Percona Xtrabackup 进行物理备份恢复后再建立主从关系。

操作步骤：

  1. 全量备份（在 Master 端执行）：
innobackupex --defaults-file=/etc/my.cnf \
  --user=root --password='your_password' \
  --socket=/var/lib/mysql/mysql.sock \
  /data/Xtrabackup
  1. 重放 redo log（确保数据一致性）：
innobackupex --defaults-file=/etc/my.cnf \
  --user=root --password='your_password' \
  --apply-log \
  --socket=/var/lib/mysql/mysql.sock \
  /data/Xtrabackup/2017-05-18_10-07-06
  1. 将备份目录同步至 Slave 服务器 data_dir 下，并修改权限：
chown -R mysql:mysql /data/mysql
  1. 启动 MySQL 服务：
systemctl start mysqld
  1. 从备份信息中获取已执行的 GTID：
cat /data/Xtrabackup/2017-05-18_10-07-06/xtrabackup_info | grep binlog_pos
# 输出示例：binlog_pos = GTID of the last change '132028ab-abc5-11e6-b2f0-000c29a60c3d:1-45675'
  1. 在 Slave 上设置已执行的 GTID 集合（需先确认 SHOW MASTER STATUS 中 Executed_Gtid_Set 为空，否则先执行 RESET MASTER）：
mysql> SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
mysql> SET @@SESSION.SQL_LOG_BIN = 0;
mysql> SET @@GLOBAL.GTID_PURGED = '132028ab-abc5-11e6-b2f0-000c29a60c3d:1-45675';
mysql> SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
  1. 配置并启动复制：
mysql> CHANGE MASTER TO
    ->   MASTER_HOST = 'your_master_ip',
    ->   MASTER_PORT = 3306,
    ->   MASTER_USER = 'repl',
    ->   MASTER_PASSWORD = 'your_password',
    ->   MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

三、GTID 复制错误处理实战

在 GTID 主主复制环境中，偶尔会遇到复制中断的情况（如主键冲突、索引重复等），此时需要跳过特定的错误事务。以下介绍几种常用的处理方案。

3.1 方法一：注入空事务跳过特定 GTID

这是最精确、最常用的方法，通过注入空事务来标记某个 GTID 已执行。

步骤一：定位出错的 GTID

通过 SHOW SLAVE STATUS\G 分析：

mysql> SHOW SLAVE STATUS\G;
-- 关注以下字段：
-- Retrieved_Gtid_Set: 6bbaffcd-67bc-11e7-9897-b083fee3a6c4:821732,
--                      7747baa0-67af-11e7-a17a-14187749f08f:1-158507467
-- Executed_Gtid_Set:  6bbaffcd-67bc-11e7-9897-b083fee3a6c4:1-821732

对比 Retrieved_Gtid_Set（已接收）与 Executed_Gtid_Set（已执行），找出未执行的 GTID。

如果 GTID 出现间隙分段且持续跳动，无法判断跳过点，可以直接从 relay log 中查找：

mysqlbinlog --base64-output=decode-rows -vv relay-bin.002226 | grep -C 20 'CREATE UNIQUE INDEX'

输出示例：

# at 295633394
#180302 9:58:27 server id 100  end_log_pos 295633286 CRC32 0xf9a0d370  GTID  last_committed=210240  sequence_number=210241
SET @@SESSION.GTID_NEXT= '7747baa0-67af-11e7-a17a-14187749f08f:157539539'/*!*/;
# at 295633459
#180302 9:58:27 server id 100  end_log_pos 295633454 CRC32 0xee0146fc  Query  thread_id=22555406  exec_time=0  error_code=0
use `xfdrippay`/*!*/;
SET TIMESTAMP=1519955907/*!*/;
CREATE UNIQUE INDEX acup ON user_contract_relation(appid,channel,userid,planid)
/*!*/;

步骤二：执行空事务跳过

-- 1. 停止 Slave
mysql> STOP SLAVE;

-- 2. 设置要跳过的 GTID（从 Retrieved_Gtid_Set 获取）
mysql> SET @@SESSION.GTID_NEXT = '7747baa0-67af-11e7-a17a-14187749f08f:157539539';

-- 3. 提交空事务
mysql> BEGIN; COMMIT;

-- 4. 恢复 GTID 为自动模式
mysql> SET SESSION GTID_NEXT = AUTOMATIC;

-- 5. 重新启动 Slave
mysql> START SLAVE;

3.2 方法二：使用 pt-slave-restart 工具

Percona Toolkit 中的 pt-slave-restart 可以自动监控并跳过指定的复制错误：

# 跳过所有 1062 错误（主键冲突）
pt-slave-restart --user=root --password='your_password' \
  --socket=/var/lib/mysql/mysql.sock \
  --error-numbers=1062

# 跳过包含特定表名的错误
pt-slave-restart --user=root --password='your_password' \
  --socket=/var/lib/mysql/mysql.sock \
  --error-text="test.t1"
注意：老版本的 pt-slave-restart 不支持 GTID 模式。在 GTID 模式下，SET GLOBAL SQL_SLAVE_SKIP_COUNTER 无法使用，需升级到支持 GTID 的新版本，或使用方法一的手动注入方式。

3.3 方法三：reset master 方式（不推荐）

此方法通过重置 Master 状态来跳过一段 GTID 范围：

mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> SET @@GLOBAL.GTID_PURGED = '8f9e146f-0a18-11e7-810a-0050568833c8:1-4';
mysql> START SLAVE;

这些命令的含义是：忽略 8f9e146f-0a18-11e7-810a-0050568833c8:1-4 这段 GTID 事务，从 GTID 序号 5 开始继续复制。

不推荐原因：RESET MASTER 会清空 binlog。在双主架构中，必须确保另一台已完全完成同步，然后需要重新搭建复制关系（STOP SLAVE -> RESET SLAVE -> START SLAVE），操作复杂度高且风险大。

3.4 其他工具与参数

使用 slave_exec_mode 参数（动态修改，仅跳过 duplicate-key 和 no-key-found 错误）：

mysql> SET GLOBAL slave_exec_mode = 'IDEMPOTENT';

使用 slave_skip_errors 参数（需重启，非 Dynamic 变量）：

[mysqld]
slave_skip_errors = 1062

3.5 常见错误：函数创建权限

在从库同步时，可能遇到以下错误：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

四、MySQL + Keepalived 双主高可用架构

4.1 架构概述

在生产环境中，单纯的 MySQL 主主复制仍然存在单点故障风险：如果某一台 MySQL 宕机，应用程序需要手动切换数据库连接地址。为此，引入 Keepalived 实现虚拟 IP（VIP）漂移，配合双主架构，可以构建自动故障转移的高可用方案。

架构要点：

  • 两台 MySQL 互为主从（双主），均可读写
  • Keepalived 在两台服务器上运行，提供 VIP（如 10.215.182.113）
  • 正常情况下，VIP 绑定在优先级较高的 MasterA 上
  • 当 MasterA 的 MySQL 服务异常时，Keepalived 检测脚本触发 VIP 漂移到 MasterB
  • 应用程序始终连接 VIP，无需感知后端 MySQL 的切换

4.2 安装 MySQL 与 Keepalived

安装 MySQL 5.7.18：

wget https://downloads.mysql.com/archives/get/file/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
# 卸载系统自带的 MariaDB
rpm -qa | grep mariadb | xargs rpm -e --nodeps
# 按顺序安装 RPM 包
rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm

安装 Keepalived：

yum install -y keepalived

4.3 MySQL 初始化

# 创建所需目录
mkdir -p /data/mysql/3306/binlog
mkdir -p /data/mysql/3306/tmp
mkdir -p /data/mysql/3306/slowlog

# 启动 MySQL
systemctl start mysqld

# 获取初始临时密码
grep 'temporary password' /var/log/mysqld.log

# 登录并修改密码
mysql -p'临时密码'
mysql> ALTER USER root@'localhost' IDENTIFIED BY 'new_password';
mysql> FLUSH PRIVILEGES;

4.4 Keepalived 配置

Master 端配置（优先级 100）

global_defs {
   notification_email {
         user@example.com
   }
   notification_email_from user@example.com
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_Mysql
}

vrrp_instance HA_1 {
        state BACKUP                  # 两台均设为 BACKUP，配合非抢占模式
        interface bond1
        virtual_router_id 221
        priority 100                  # 主节点优先级较高
        advert_int 2
        nopreempt                     # 非抢占模式
        authentication {
                auth_type PASS
                auth_pass mycat
        }
        virtual_ipaddress {
            10.215.182.113/24
        }
}

virtual_server 10.215.182.113 3306 {
        delay_loop 2
        lb_algo wlc
        lb_kind DR
        nat_mask 192.168.37.35
        protocol TCP
        persistence_timeout 60
        real_server 10.116.103.90 3306 {
                weight 1
                notify_down /etc/keepalived/mysqlcheck/mysql.sh
                TCP_CHECK {
                        connect_port 3306
                        connect_timeout 3
                        nb_get_retry 2
                        delay_before_retry 1
                }
        }
}

Backup 端配置（优先级 90）

global_defs {
   notification_email {
         user@example.com
   }
   notification_email_from user@example.com
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_Mysql
}

vrrp_instance HA_1 {
        state BACKUP
        interface ens2f0
        virtual_router_id 221
        priority 90
        advert_int 2
        authentication {
                auth_type PASS
                auth_pass mycat
        }
        virtual_ipaddress {
            10.215.182.113
        }
}

virtual_server 10.215.182.113 3306 {
        delay_loop 2
        lb_algo wlc
        lb_kind DR
        nat_mask 192.168.37.35
        protocol TCP
        persistence_timeout 60
        real_server 10.84.178.157 3306 {
                weight 1
                notify_down /etc/keepalived/mysqlcheck/mysql.sh
                TCP_CHECK {
                        connect_port 3306
                        connect_timeout 3
                        nb_get_retry 2
                        delay_before_retry 1
                }
        }
}

MySQL 健康检测脚本（/etc/keepalived/mysqlcheck/mysql.sh）：

#!/bin/sh
# 当检测到 MySQL 不可用时，停止本机 Keepalived 以实现 VIP 漂移
/usr/bin/systemctl stop keepalived.service
chmod u+x /etc/keepalived/mysqlcheck/mysql.sh

启动 Keepalived

systemctl start keepalived
# 不重启重新加载配置
systemctl reload keepalived

4.5 配置说明

  • 非抢占模式：两台 Keepalived 的 state 均设为 BACKUP，配合 nopreempt 参数。这样即使高优先级节点恢复后，也不会立即抢回 VIP，避免频繁切换导致的连接中断。
  • 健康检测：通过 TCP_CHECK 检测 MySQL 3306 端口。当检测失败时，触发 notify_down 脚本停止本地 Keepalived，VIP 自动漂移到备用节点。
  • virtual_router_id：主备必须一致（同一虚拟路由组）。

五、MySQL Proxy 读写分离

在双主高可用架构基础上，引入读写分离可以进一步提升系统的吞吐能力。MySQL Proxy 是官方提供的轻量级中间件，通过 Lua 脚本实现 SQL 语句的路由。

5.1 基本配置

/opt/mysql_proxy/bin/mysql-proxy \
  --daemon \
  --log-level=debug \
  --log-file=/data/log/mysql/mysql-proxy.log \
  --plugins=proxy \
  -b 127.0.0.1:3306 \
  -r 127.0.0.1:3307 \
  --proxy-lua-script=/opt/mysql_proxy/lib/mysql-proxy/lua/rw-splitting.lua \
  --plugins=admin \
  --admin-username=admin \
  --admin-password=admin \
  --admin-lua-script=/opt/mysql_proxy/lib/mysql-proxy/lua/admin.lua

参数说明：

参数说明
--daemon以守护进程方式运行
--plugins=proxy加载代理插件
-b后端写库（Master）地址
-r后端读库（Slave）地址
--proxy-lua-script读写分离 Lua 脚本路径
--plugins=admin加载管理插件
--admin-username / --admin-password管理接口认证

5.2 压力测试工具 mysqlslap

MySQL 自带的 mysqlslap 可用于模拟并发负载，验证读写分离效果：

mysqlslap --help

关键参数：

参数说明
--concurrency=10,50,100并发连接线程数，支持逗号分隔多个值
--engines测试的存储引擎
--iterations测试运行的次数
--auto-generate-sql自动生成 SQL 脚本进行测试
--auto-generate-sql-load-type测试类型：read、write、update、mixed
--number-of-queries总共运行的查询数
--debug-info额外输出 CPU 及内存相关信息
--number-int-cols测试表中 INT 类型字段数量
--number-char-cols测试表中 CHAR 类型字段数量
--create-schema指定测试的数据库（Schema）
--query使用自定义 SQL 或存储过程执行测试

示例：

mysqlslap \
  --user=root --password='your_password' \
  --host=127.0.0.1 --port=3306 \
  --concurrency=50,100 \
  --iterations=10 \
  --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --number-of-queries=10000 \
  --create-schema=testdb \
  --debug-info

六、备份策略与自动化

在任何高可用方案中，备份都是最后一道防线。推荐使用 Percona Xtrabackup 实现物理热备份，它支持 InnoDB 的非阻塞备份，不会影响业务运行。

6.1 Xtrabackup 安装

# 安装依赖
rpm -ivh libev-4.15-3.el7.x86_64.rpm
rpm -ivh perl-DBD-MySQL-4.023-5.el7.x86_64.rpm
rpm -ivh perl-Digest-MD5-2.52-3.el7.x86_64.rpm
# 安装 Xtrabackup
rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

6.2 备份策略设计

推荐的备份策略为：

  • 每天凌晨 04:00：执行一次全量备份
  • 每隔 6 小时：在上一次备份基础上执行增量备份
  • 保留 2 天：自动清理超过 2 天的备份文件

6.3 备份脚本核心逻辑

#!/bin/bash
# 备份工具：percona-xtrabackup-24-2.4.7
# 备份策略：
#   (1) 每天凌晨 04:00 进行全量备份
#   (2) 每隔 6 小时增量备份一次
#   (3) 自动清理 2 天前的备份

xtrabackup_path="/usr/bin/innobackupex"
mysql_cnf_path="/etc/my.cnf"
thread_cnt=4

# 全量备份
if [[ "$1" == "full" ]]; then
    ${xtrabackup_path} \
        --defaults-file=${mysql_cnf_path} \
        --user=xtrabackup \
        --password='your_password' \
        --socket=/var/lib/mysql/mysql.sock \
        --parallel=${thread_cnt} \
        --no-timestamp \
        ${current_bak_path}
    echo "NULL|${current_bak_path}|full" >> ${inc_base_list}
fi

# 增量备份
if [[ "$1" == "inc" ]]; then
    prev_backup_dir=$(sed '/^$/d' ${inc_base_list} | tail -1 | awk -F '|' '{print $2}')
    ${xtrabackup_path} \
        --defaults-file=${mysql_cnf_path} \
        --user=xtrabackup \
        --password='your_password' \
        --socket=/var/lib/mysql/mysql.sock \
        --parallel=${thread_cnt} \
        --no-timestamp \
        --incremental ${current_bak_path} \
        --incremental-basedir=${prev_backup_dir}
    echo "${prev_backup_dir}|${current_bak_path}|inc" >> ${inc_base_list}
fi

# 清理 2 天前的备份
rm -rf ${backup_base_dir}/$(date -d '2 days ago' +'%F')_*
sed -i "/$(date -d '2 days ago' +'%F')/d" ${inc_base_list}

6.4 计划任务

# 配置 crontab
00 04 * * * /data/scripts/mysqlbackup.sh full >/dev/null 2>&1
00 */6 * * * /data/scripts/mysqlbackup.sh inc >/dev/null 2>&1

总结

本文从 MySQL 主从复制延迟问题出发，系统性地梳理了以下关键技术要点：

  1. 多线程复制（MTS）：MySQL 5.7 基于 LOGICAL_CLOCK 的并行复制机制，通过 slave_parallel_workers 和 slave_parallel_type 参数配置，显著降低了主从延迟。
  2. GTID 主主复制：GTID 为每个事务提供全局唯一标识，简化了复制配置和故障切换。双主架构中需注意 auto_increment_offset 和 auto_increment_increment 的设置以避免自增 ID 冲突。
  3. GTID 错误处理：通过注入空事务跳过特定 GTID 是推荐的标准操作方式，pt-slave-restart 工具可实现自动化跳过。
  4. Keepalived 高可用：通过 VIP 漂移实现 MySQL 双主的自动故障转移，配合健康检测脚本和 TCP_CHECK 机制，确保服务连续性。
  5. 读写分离：MySQL Proxy 结合 Lua 脚本可实现灵活的路由策略，mysqlslap 工具可用于性能验证。
  6. 备份策略：Xtrabackup 的「全量 + 增量」备份方案是最佳实践，通过 crontab 实现自动化，确保数据安全。

将这些技术组合使用，可以构建一套完整、稳定、可运维的 MySQL 高可用复制架构，满足绝大多数互联网业务的需求。在实际落地时，建议先在测试环境中充分验证，尤其要模拟主从切换、故障恢复等场景，确保各项参数和脚本的正确性。

暂无评论