实战：从零搭建一个高可用的 MySQL 主从复制集群

1. 环境准备与规划

1.1 服务器规划

在本教程中，我们将使用三台服务器搭建 MySQL 主从复制集群：

• 主服务器 (Master): 192.168.1.10
• 从服务器1 (Slave1): 192.168.1.11
• 从服务器2 (Slave2): 192.168.1.12

1.2 系统要求

• 操作系统: Ubuntu 20.04 LTS
• MySQL 版本: 8.0
• 内存: 至少 2GB
• 磁盘空间: 至少 20GB

1.3 网络配置

确保所有服务器之间可以互相通信，防火墙开放 MySQL 端口（默认 3306）。

2. 安装 MySQL 服务器

2.1 在所有服务器上安装 MySQL

创建安装脚本文件: install_mysql.sh

#!/bin/bash

# 更新系统包
sudo apt update
sudo apt upgrade -y

# 安装 MySQL 服务器
wget https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.20-1_all.deb
sudo apt update

# 安装 MySQL 服务器和客户端
sudo apt install -y mysql-server mysql-client

# 启动 MySQL 服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 运行安全安装脚本
sudo mysql_secure_installation <<EOF

y
0
MySecurePassword123!
y
y
y
y
y
EOF

echo "MySQL installation completed on $(hostname)"

执行安装脚本：

chmod +x install_mysql.sh
./install_mysql.sh

2.2 配置 MySQL 根密码

创建配置文件: configure_mysql_root.sql

-- 配置 root 用户远程访问（仅用于演示，生产环境请谨慎）
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MySecurePassword123!';
CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'MySecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 创建复制专用用户
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 显示用户权限
SELECT user, host FROM mysql.user;

执行配置：

mysql -u root -p < configure_mysql_root.sql

3. 主服务器配置

3.1 配置主服务器 my.cnf

创建配置文件: /etc/mysql/conf.d/master.cnf

[mysqld]
# 服务器标识
server-id = 1

# 二进制日志配置
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 10
max_binlog_size = 100M

# 复制配置
binlog_do_db = example_db
replicate_do_db = example_db

# 从服务器可以读取二进制日志
read_only = 0

# 性能优化
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 错误日志
log_error = /var/log/mysql/mysql-error.log

# 中继日志配置（虽然主服务器通常不需要，但为了完整性）
relay_log = /var/log/mysql/mysql-relay-bin.log
relay_log_index = /var/log/mysql/mysql-relay-bin.index

# 确保主服务器不会清理中继日志
relay_log_purge = 1

# 全局事务标识符
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步复制配置
plugin_load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

3.2 重启主服务器 MySQL 服务

sudo systemctl restart mysql
sudo systemctl status mysql

3.3 在主服务器上创建测试数据库

创建数据库脚本: create_test_database.sql

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS example_db;

-- 使用测试数据库
USE example_db;

-- 创建测试表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入测试数据
INSERT INTO users (username, email) VALUES 
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('bob_wilson', 'bob@example.com');

INSERT INTO products (name, price, description) VALUES 
('Laptop', 999.99, 'High-performance laptop'),
('Mouse', 29.99, 'Wireless mouse'),
('Keyboard', 79.99, 'Mechanical keyboard');

-- 显示创建的表
SHOW TABLES;

-- 显示数据
SELECT * FROM users;
SELECT * FROM products;

执行脚本：

mysql -u root -p < create_test_database.sql

3.4 获取主服务器状态

创建检查脚本: check_master_status.sql

-- 显示主服务器状态
SHOW MASTER STATUS;

-- 显示二进制日志文件
SHOW BINARY LOGS;

-- 显示当前连接
SHOW PROCESSLIST;

-- 显示复制相关变量
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'gtid_mode';

执行脚本：

mysql -u root -p -e "source check_master_status.sql"

4. 从服务器配置

4.1 配置从服务器1 my.cnf

创建配置文件: /etc/mysql/conf.d/slave1.cnf

[mysqld]
# 服务器标识（必须唯一）
server-id = 2

# 二进制日志配置（从服务器也可以开启二进制日志，用于级联复制）
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# 中继日志配置
relay_log = /var/log/mysql/mysql-relay-bin.log
relay_log_index = /var/log/mysql/mysql-relay-bin.index
relay_log_info_file = /var/log/mysql/relay-log.info

# 复制配置
replicate_do_db = example_db
replicate_ignore_db = mysql
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema

# 从服务器设置为只读（防止误操作）
read_only = 1
super_read_only = 1

# 全局事务标识符
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步复制配置
plugin_load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

# 性能优化
skip_slave_start = 1
relay_log_recovery = 1

# 错误日志
log_error = /var/log/mysql/mysql-error.log

4.2 配置从服务器2 my.cnf

创建配置文件: /etc/mysql/conf.d/slave2.cnf

[mysqld]
# 服务器标识（必须唯一）
server-id = 3

# 二进制日志配置
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# 中继日志配置
relay_log = /var/log/mysql/mysql-relay-bin.log
relay_log_index = /var/log/mysql/mysql-relay-bin.index
relay_log_info_file = /var/log/mysql/relay-log.info

# 复制配置
replicate_do_db = example_db
replicate_ignore_db = mysql
replicate_ignore_db = information_schema
replicate_ignore_db = performance_schema

# 从服务器设置为只读
read_only = 1
super_read_only = 1

# 全局事务标识符
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步复制配置
plugin_load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 1

# 性能优化
skip_slave_start = 1
relay_log_recovery = 1

# 错误日志
log_error = /var/log/mysql/mysql-error.log

4.3 重启从服务器 MySQL 服务

分别在两个从服务器上执行：

sudo systemctl restart mysql
sudo systemctl status mysql

4.4 配置从服务器复制

在主服务器上获取复制所需信息：

mysql -u root -p -e "SHOW MASTER STATUS;"

记录输出中的 File 和 Position 值。

创建从服务器配置脚本: configure_slave_replication.sql

-- 停止从服务器复制
STOP SLAVE;

-- 重置从服务器
RESET SLAVE ALL;

-- 配置主服务器连接（替换为实际的主服务器IP和获取的File、Position值）
CHANGE MASTER TO 
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='ReplPassword123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157,
MASTER_AUTO_POSITION=0;

-- 或者使用 GTID 自动定位（推荐）
CHANGE MASTER TO 
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='ReplPassword123!',
MASTER_AUTO_POSITION=1;

-- 启动从服务器复制
START SLAVE;

-- 检查从服务器状态
SHOW SLAVE STATUS\G

在两个从服务器上分别执行此脚本。

5. 验证复制状态

5.1 检查复制状态脚本

创建验证脚本: check_replication_status.sql

-- 检查从服务器状态
SHOW SLAVE STATUS\G

-- 检查进程列表
SHOW PROCESSLIST;

-- 检查数据库和表是否同步
USE example_db;
SHOW TABLES;
SELECT * FROM users;
SELECT * FROM products;

-- 检查复制相关变量
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled';

5.2 在主服务器上测试数据同步

创建测试数据脚本: test_data_sync.sql

USE example_db;

-- 插入新数据
INSERT INTO users (username, email) VALUES 
('test_user_1', 'test1@example.com'),
('test_user_2', 'test2@example.com');

INSERT INTO products (name, price, description) VALUES 
('Monitor', 199.99, '27-inch 4K monitor'),
('Headphones', 149.99, 'Noise-cancelling headphones');

-- 更新数据
UPDATE users SET username = 'john_doe_updated' WHERE username = 'john_doe';

-- 删除数据
DELETE FROM users WHERE username = 'bob_wilson';

-- 查询验证
SELECT * FROM users;
SELECT * FROM products;

在主服务器上执行此脚本，然后在从服务器上验证数据是否同步。

6. 监控和维护

6.1 创建监控脚本

创建监控脚本: mysql_replication_monitor.sh

#!/bin/bash

# MySQL 复制监控脚本
# 配置信息
MYSQL_USER="root"
MYSQL_PASSWORD="MySecurePassword123!"
MASTER_HOST="192.168.1.10"
SLAVE1_HOST="192.168.1.11"
SLAVE2_HOST="192.168.1.12"

# 颜色输出
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo "=== MySQL 主从复制集群监控 ==="
echo "检查时间: $(date)"
echo

# 检查主服务器状态
echo "1. 检查主服务器状态 ($MASTER_HOST):"
MASTER_STATUS=$(mysql -h $MASTER_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS\G" 2>/dev/null)

if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ 主服务器运行正常${NC}"
    echo "$MASTER_STATUS"
else
    echo -e "${RED}✗ 主服务器连接失败${NC}"
fi
echo

# 检查从服务器1状态
echo "2. 检查从服务器1状态 ($SLAVE1_HOST):"
SLAVE1_STATUS=$(mysql -h $SLAVE1_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" 2>/dev/null)

if [ $? -eq 0 ]; then
    IO_RUNNING=$(echo "$SLAVE1_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
    SQL_RUNNING=$(echo "$SLAVE1_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
    SECONDS_BEHIND=$(echo "$SLAVE1_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
    
    if [ "$IO_RUNNING" = "Yes" ] && [ "$SQL_RUNNING" = "Yes" ]; then
        echo -e "${GREEN}✓ 从服务器1复制正常${NC}"
        echo "复制延迟: $SECONDS_BEHIND 秒"
    else
        echo -e "${RED}✗ 从服务器1复制异常${NC}"
        echo "IO Running: $IO_RUNNING"
        echo "SQL Running: $SQL_RUNNING"
    fi
else
    echo -e "${RED}✗ 从服务器1连接失败${NC}"
fi
echo

# 检查从服务器2状态
echo "3. 检查从服务器2状态 ($SLAVE2_HOST):"
SLAVE2_STATUS=$(mysql -h $SLAVE2_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" 2>/dev/null)

if [ $? -eq 0 ]; then
    IO_RUNNING=$(echo "$SLAVE2_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
    SQL_RUNNING=$(echo "$SLAVE2_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
    SECONDS_BEHIND=$(echo "$SLAVE2_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
    
    if [ "$IO_RUNNING" = "Yes" ] && [ "$SQL_RUNNING" = "Yes" ]; then
        echo -e "${GREEN}✓ 从服务器2复制正常${NC}"
        echo "复制延迟: $SECONDS_BEHIND 秒"
    else
        echo -e "${RED}✗ 从服务器2复制异常${NC}"
        echo "IO Running: $IO_RUNNING"
        echo "SQL Running: $SQL_RUNNING"
    fi
else
    echo -e "${RED}✗ 从服务器2连接失败${NC}"
fi
echo

# 检查数据一致性
echo "4. 检查数据一致性:"
check_table_count() {
    local host=$1
    local count=$(mysql -h $host -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE example_db; SELECT COUNT(*) FROM users;" -s 2>/dev/null)
    echo $count
}

MASTER_COUNT=$(check_table_count $MASTER_HOST)
SLAVE1_COUNT=$(check_table_count $SLAVE1_HOST)
SLAVE2_COUNT=$(check_table_count $SLAVE2_HOST)

echo "主服务器 users 表记录数: $MASTER_COUNT"
echo "从服务器1 users 表记录数: $SLAVE1_COUNT"
echo "从服务器2 users 表记录数: $SLAVE2_COUNT"

if [ "$MASTER_COUNT" = "$SLAVE1_COUNT" ] && [ "$MASTER_COUNT" = "$SLAVE2_COUNT" ]; then
    echo -e "${GREEN}✓ 数据一致性检查通过${NC}"
else
    echo -e "${RED}✗ 数据一致性检查失败${NC}"
fi

6.2 设置定时监控

将监控脚本设置为定时任务：

chmod +x mysql_replication_monitor.sh
# 每5分钟执行一次监控
echo "*/5 * * * * /path/to/mysql_replication_monitor.sh >> /var/log/mysql_replication_monitor.log" | sudo tee -a /etc/crontab

7. 故障恢复与切换

7.1 主从切换脚本

创建主从切换脚本: mysql_failover.sh

#!/bin/bash

# MySQL 主从故障切换脚本
MYSQL_USER="root"
MYSQL_PASSWORD="MySecurePassword123!"
CURRENT_MASTER="192.168.1.10"
NEW_MASTER="192.168.1.11"  # 选择延迟最小的从服务器作为新主

echo "开始 MySQL 主从故障切换..."
echo "当前主服务器: $CURRENT_MASTER"
echo "新主服务器: $NEW_MASTER"
echo

# 检查当前主服务器状态
echo "1. 检查当前主服务器状态..."
CURRENT_MASTER_STATUS=$(mysql -h $CURRENT_MASTER -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT 1" 2>/dev/null)

if [ $? -eq 0 ]; then
    echo "当前主服务器仍然在线，是否继续切换？(y/N)"
    read -r response
    if [[ ! "$response" =~ ^([yY][eE][sS]|[yY])+$ ]]; then
        echo "切换已取消"
        exit 0
    fi
fi

# 在新的主服务器上停止复制并重置
echo "2. 在新的主服务器上配置..."
mysql -h $NEW_MASTER -u $MYSQL_USER -p$MYSQL_PASSWORD <<EOF
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only=0;
SET GLOBAL super_read_only=0;
EOF

# 在其他从服务器上重新指向新的主服务器
echo "3. 重新配置其他从服务器..."
OTHER_SLAVE="192.168.1.12"
mysql -h $OTHER_SLAVE -u $MYSQL_USER -p$MYSQL_PASSWORD <<EOF
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO 
MASTER_HOST='$NEW_MASTER',
MASTER_USER='repl',
MASTER_PASSWORD='ReplPassword123!',
MASTER_AUTO_POSITION=1;
START SLAVE;
EOF

# 在新的主服务器上创建复制用户
echo "4. 在新的主服务器上创建复制用户..."
mysql -h $NEW_MASTER -u $MYSQL_USER -p$MYSQL_PASSWORD <<EOF
CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
EOF

echo "5. 故障切换完成"
echo "新的主服务器: $NEW_MASTER"
echo "请更新应用程序的连接配置"

8. 高可用架构流程图

graph TD
    A[应用程序] --> B[负载均衡器]
    B --> C[主数据库 Master]
    B --> D[从数据库 Slave1]
    B --> E[从数据库 Slave2]
    
    C -->|二进制日志复制| D
    C -->|二进制日志复制| E
    D -->|中继日志应用| F[数据同步]
    E -->|中继日志应用| F
    
    G[监控系统] --> C
    G --> D
    G --> E
    G --> H[报警通知]
    
    I[备份系统] --> C
    I --> J[定期备份]
    
    style A fill:#4CAF50,stroke:#388E3C,color:white
    style B fill:#2196F3,stroke:#1976D2,color:white
    style C fill:#FF9800,stroke:#F57C00,color:white
    style D fill:#9C27B0,stroke:#7B1FA2,color:white
    style E fill:#9C27B0,stroke:#7B1FA2,color:white
    style G fill:#F44336,stroke:#D32F2F,color:white
    style I fill:#607D8B,stroke:#455A64,color:white
    style F fill:#009688,stroke:#00796B,color:white
    style H fill:#F44336,stroke:#D32F2F,color:white
    style J fill:#795548,stroke:#5D4037,color:white

9. 数据备份策略

9.1 创建备份脚本

创建备份脚本: mysql_backup.sh

#!/bin/bash

# MySQL 数据库备份脚本
MYSQL_USER="root"
MYSQL_PASSWORD="MySecurePassword123!"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

echo "开始 MySQL 数据库备份: $(date)"

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 全量备份
echo "执行全量备份..."
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases --single-transaction --routines --triggers --events > $BACKUP_DIR/$DATE/full_backup.sql

# 备份二进制日志
echo "备份二进制日志..."
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "FLUSH BINARY LOGS;"
BINLOG_FILES=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINARY LOGS;" | awk 'NR>1 {print $1}')
for file in $BINLOG_FILES; do
    cp /var/log/mysql/$file $BACKUP_DIR/$DATE/
done

# 压缩备份文件
echo "压缩备份文件..."
tar -czf $BACKUP_DIR/$DATE.tar.gz -C $BACKUP_DIR $DATE

# 清理临时文件
rm -rf $BACKUP_DIR/$DATE

# 清理旧备份
echo "清理旧备份文件..."
find $BACKUP_DIR -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete

echo "备份完成: $(date)"
echo "备份文件: $BACKUP_DIR/$DATE.tar.gz"

10. 总结

通过以上详细的步骤，我们成功搭建了一个高可用的 MySQL 主从复制集群。这个架构提供了：

1. 数据冗余: 多副本确保数据安全
2. 负载均衡: 读操作可以分发到从服务器
3. 高可用性: 主服务器故障时可以快速切换
4. 可扩展性: 可以轻松添加更多从服务器
5. 监控维护: 完整的监控和备份策略

这个解决方案适合生产环境使用，能够满足大多数业务场景的高可用需求。