MySQL主从复制：构建高可用架构

💡 摘要：你是否担心数据库单点故障导致业务中断？是否希望实现读写分离提升系统性能？是否需要跨地域的数据备份和灾难恢复？

MySQL主从复制不仅是数据备份的工具，更是构建高可用、高性能架构的基石。通过复制技术，你可以实现故障自动切换、读写分离、数据分发等多种架构模式。

本文将深入解析MySQL主从复制的原理、配置和优化，带你构建企业级的高可用数据库架构。

一、复制基础：理解主从架构的核心价值

1. 主从复制架构图解

text

MySQL主从复制架构：

┌─────────────────────────────────────────────────┐

│                  主库 (Master)                   │

│  • 接收写操作                                  │

│  • 记录二进制日志(Binlog)                      │

│  • 分配唯一的服务器ID                          │

└─────────────────────────────────────────────────┘

                     │

                     ▼

┌─────────────────────────────────────────────────┐

│                  从库 (Slave)                   │

│  • 拉取主库的Binlog                            │

│  • 重放SQL语句或应用行变更                     │

│  • 可设置为只读模式                            │

└─────────────────────────────────────────────────┘

2. 复制类型对比

二、主从复制实战配置

1. 主库配置

ini

# /etc/mysql/my.cnf 主库配置

[mysqld]

# 服务器唯一标识

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_format = ROW            # 推荐使用ROW格式

# 复制过滤（可选）

binlog_do_db = company_db      # 只复制指定数据库

# binlog_ignore_db = test_db   # 忽略指定数据库

# 二进制日志管理

expire_logs_days = 7           # 日志保留7天

max_binlog_size = 1G           # 每个binlog文件最大1G

# 启用GTID（推荐）

gtid_mode = ON

enforce_gtid_consistency = ON

2. 创建复制账号

sql

-- 在主库创建复制账号

CREATE USER 'repl'@'%' IDENTIFIED BY 'SecureReplPass123!';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

FLUSH PRIVILEGES;

-- 查看主库状态

SHOW MASTER STATUS;

/*

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      107 | company_db   |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

*/

3. 从库配置

ini

# /etc/mysql/my.cnf 从库配置

[mysqld]

server-id = 2                  # 必须唯一，不能与主库相同

relay_log = /var/log/mysql/mysql-relay.log

read_only = ON                 # 从库只读模式

super_read_only = ON           # 超级用户也只读

# 中继日志管理

relay_log_recovery = ON        # 崩溃安全

relay_log_purge = ON           # 自动清理中继日志

# GTID配置

gtid_mode = ON

enforce_gtid_consistency = ON

4. 启动复制进程

sql

-- 在从库配置复制

CHANGE MASTER TO

MASTER_HOST = 'master_host',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'SecureReplPass123!',

MASTER_PORT = 3306,

MASTER_AUTO_POSITION = 1;      # 使用GTID自动定位

-- 启动复制

START SLAVE;

-- 查看复制状态

SHOW SLAVE STATUS\G;

三、复制监控与管理

1. 复制状态监控

sql

-- 查看详细的复制状态

SHOW SLAVE STATUS\G;

-- 关键指标监控

SELECT

   Slave_IO_State,

   Master_Host,

   Master_User,

   Seconds_Behind_Master AS replication_lag,

   Slave_IO_Running,

   Slave_SQL_Running,

   Last_IO_Error,

   Last_SQL_Error

FROM performance_schema.replication_connection_status;

-- 监控复制延迟

SELECT

   NOW() - MAX(event_timestamp) AS current_lag

FROM mysql.slave_relay_log_info;

2. 性能监控脚本

bash

#!/bin/bash

# 复制监控脚本

# 检查复制状态

function check_replication() {

   local status=$(mysql -u root -p'${PASSWORD}' -e "SHOW SLAVE STATUS\G")

   

   # 检查IO和SQL线程状态

   local io_running=$(echo "$status" | grep "Slave_IO_Running:" | awk '{print $2}')

   local sql_running=$(echo "$status" | grep "Slave_SQL_Running:" | awk '{print $2}')

   local lag=$(echo "$status" | grep "Seconds_Behind_Master:" | awk '{print $2}')

   

   if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; then

       echo "ERROR: Replication is broken! IO: $io_running, SQL: $sql_running"

       echo "$status" | grep "Last_Error"

       return 1

   fi

   

   if [ "$lag" -gt 60 ]; then

       echo "WARNING: Replication lag is $lag seconds"

   else

       echo "OK: Replication is running smoothly, lag: $lag seconds"

   fi

}

# 定期检查

while true; do

   check_replication

   sleep 30

done

四、高可用架构设计

1. 一主多从架构

text

一主多从架构：

┌─────────────────────────────────────────────────┐

│                  主库 (Master)                   │

└─────────────────────────────────────────────────┘

           │                │                │

           ▼                ▼                ▼

┌───────────────────┐┌───────────────────┐┌───────────────────┐

│    从库1 (Read)    ││    从库2 (Read)    ││    从库3 (Backup)  │

└───────────────────┘└───────────────────┘└───────────────────┘

2. 级联复制架构

text

级联复制架构：

┌─────────────────────────────────────────────────┐

│                  主库 (Master)                   │

└─────────────────────────────────────────────────┘

                           │

                           ▼

┌─────────────────────────────────────────────────┐

│                中间主库 (Relay Master)           │

└─────────────────────────────────────────────────┘

           │                │                │

           ▼                ▼                ▼

┌───────────────────┐┌───────────────────┐┌───────────────────┐

│      从库集群       ││      从库集群       ││      从库集群       │

└───────────────────┘└───────────────────┘└───────────────────┘

3. 双主复制架构

ini

# 双主配置（谨慎使用）

[mysqld]

auto_increment_increment = 2   # 自增步长

auto_increment_offset = 1      # 自增偏移

# 第二台主库

auto_increment_increment = 2

auto_increment_offset = 2

五、复制过滤与数据分发

1. 数据库级过滤

ini

# 主库配置复制过滤

binlog_do_db = important_db

binlog_ignore_db = temp_db

# 从库配置复制过滤

replicate_do_db = important_db

replicate_ignore_db = temp_db

2. 表级过滤

ini

# 从库表级过滤

replicate_do_table = db1.important_table

replicate_ignore_table = db1.log_table

replicate_wild_do_table = db2.backup_%

replicate_wild_ignore_table = db3.temp_%

3. 基于GTID的过滤

sql

-- 基于GTID的复制过滤

CHANGE MASTER TO

MASTER_AUTO_POSITION = 1,

MASTER_HEARTBEAT_PERIOD = 60;

-- 跳过特定GTID事务

SET GTID_NEXT = 'source_id:transaction_id';

BEGIN; COMMIT;

SET GTID_NEXT = 'AUTOMATIC';

六、故障切换与恢复

1. 手动故障切换流程

bash

#!/bin/bash

# 手动故障切换脚本

# 1. 停止主库写操作

mysql -h master -u root -p'${PASSWORD}' -e "SET GLOBAL read_only = ON;"

# 2. 等待从库追平

while true; do

   lag=$(mysql -h slave -u root -p'${PASSWORD}' -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

   if [ "$lag" -eq 0 ]; then

       break

   fi

   sleep 5

done

# 3. 提升从库为主库

mysql -h slave -u root -p'${PASSWORD}' -e "

STOP SLAVE;

RESET SLAVE ALL;

SET GLOBAL read_only = OFF;

"

# 4. 切换应用连接指向新主库

# 更新负载均衡器配置或应用配置

2. 基于GTID的故障恢复

sql

-- 在新主库上重置复制

STOP SLAVE;

RESET MASTER;

-- 在其他从库上指向新主库

STOP SLAVE;

CHANGE MASTER TO

MASTER_HOST = 'new_master',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'SecureReplPass123!',

MASTER_AUTO_POSITION = 1;

START SLAVE;

七、复制优化与性能调优

1. 并行复制优化

ini

# MySQL 5.7+ 并行复制配置

slave_parallel_type = LOGICAL_CLOCK

slave_parallel_workers = 8      # 根据CPU核心数调整

slave_preserve_commit_order = ON

# MySQL 8.0 增强并行复制

binlog_transaction_dependency_tracking = WRITESET

slave_parallel_workers = 16

2. 网络与I/O优化

ini

# 网络压缩

slave_compressed_protocol = ON

# 批量应用事务

slave_transaction_retries = 10

slave_checkpoint_group = 512

slave_checkpoint_period = 300

# 中继日志优化

relay_log_info_repository = TABLE

relay_log_recovery = ON

3. 监控与告警配置

sql

-- 创建复制监控表

CREATE TABLE replication_monitor (

   id INT AUTO_INCREMENT PRIMARY KEY,

   check_time DATETIME DEFAULT CURRENT_TIMESTAMP,

   io_running ENUM('Yes','No'),

   sql_running ENUM('Yes','No'),

   seconds_behind_master INT,

   last_error TEXT,

   INDEX (check_time)

);

-- 定期插入监控数据

INSERT INTO replication_monitor (io_running, sql_running, seconds_behind_master)

SELECT

   Slave_IO_Running,

   Slave_SQL_Running,

   Seconds_Behind_Master

FROM information_schema.SLAVE_STATUS;

八、安全与备份策略

1. 复制链路安全

ini

# SSL加密复制连接

MASTER_SSL = 1

MASTER_SSL_CA = /path/to/ca.pem

MASTER_SSL_CERT = /path/to/client-cert.pem

MASTER_SSL_KEY = /path/to/client-key.pem

# 复制账号权限最小化

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

2. 备份策略

bash

# 从库备份，不影响主库性能

#!/bin/bash

# 从库备份脚本

# 停止复制，确保备份一致性

mysql -u root -p'${PASSWORD}' -e "STOP SLAVE SQL_THREAD;"

# 执行备份

mysqldump --single-transaction --routines --triggers \

--all-databases | gzip > /backup/mysql_backup_$(date +%Y%m%d).sql.gz

# 恢复复制

mysql -u root -p'${PASSWORD}' -e "START SLAVE SQL_THREAD;"

# 验证备份完整性

gzip -t /backup/mysql_backup_$(date +%Y%m%d).sql.gz

九、常见问题与解决方案

1. 复制中断处理

sql

-- 常见错误：重复键冲突、数据不存在等

-- 查看错误信息

SHOW SLAVE STATUS\G;

-- 跳过错误（谨慎使用）

STOP SLAVE;

SET GLOBAL sql_slave_skip_counter = 1;  # 跳过一个事件

START SLAVE;

-- 或者使用GTID跳过

STOP SLAVE;

SET GTID_NEXT = 'source_id:transaction_id';

BEGIN; COMMIT;

SET GTID_NEXT = 'AUTOMATIC';

START SLAVE;

2. 数据不一致修复

bash

# 使用pt-table-checksum检查数据一致性

pt-table-checksum --replicate=test.checksums \

--create-replicate-table \

--empty-replicate-table \

--recursion-method=hosts \

h=master_host,u=root,p=password

# 使用pt-table-sync修复不一致

pt-table-sync --replicate test.checksums \

--execute \

h=master_host,u=root,p=password

十、云原生环境下的复制

1. 云数据库复制配置

bash

# AWS RDS主从复制配置

# 创建只读副本

aws rds create-db-instance-read-replica \

   --db-instance-identifier read-replica \

   --source-db-instance-identifier master-instance

# 配置跨区域复制

aws rds create-db-instance-read-replica \

   --db-instance-identifier dr-replica \

   --source-db-instance-identifier master-instance \

   --region us-west-2

2. 容器化环境复制

yaml

# Kubernetes StatefulSet配置

apiVersion: apps/v1

kind: StatefulSet

metadata:

 name: mysql

spec:

 serviceName: mysql

 replicas: 3

 template:

   spec:

     containers:

     - name: mysql

       image: mysql:8.0

       env:

       - name: MYSQL_REPLICATION_USER

         value: repl

       - name: MYSQL_REPLICATION_PASSWORD

         valueFrom:

           secretKeyRef:

             name: mysql-secret

             key: replication-password

总结：构建健壮的复制架构

1. 最佳实践清单

• 使用GTID简化故障切换
  
• 配置并行复制提升性能
  
• 启用SSL加密复制链路
  
• 定期监控复制状态和延迟
  
• 实施自动故障切换机制
  
• 定期进行故障切换演练
  

2. 高可用架构指标

通过本文的全面指南，你现在已经掌握了MySQL主从复制的核心知识和实践技巧。记住：复制不仅是数据备份的手段，更是构建高可用架构的基础。现在就开始规划和实施你的MySQL复制架构吧！