MySQL 主从复制 - MySQL 教程 | 小傅哥技术教程

主从复制概述

MySQL 主从复制是一种数据同步技术，通过将主服务器（Master）的数据变更自动复制到一个或多个从服务器（Slave），实现数据的高可用性、读写分离和负载均衡。

🎯 异步复制

主服务器不等待从服务器确认，性能最好但可能丢失数据

⚡ 半同步复制

主服务器等待至少一个从服务器确认，平衡性能和安全性

🔒 同步复制

主服务器等待所有从服务器确认，最安全但性能较低

📊 基于语句复制

复制 SQL 语句，日志文件小但可能不一致

📝 基于行复制

复制数据行变更，确保一致性但日志文件大

🔄 混合复制

自动选择最优复制方式，兼顾性能和一致性

主从复制架构图

主服务器 (Master)

→

从服务器 1 (Slave)

↘

从服务器 2 (Slave)

↘

从服务器 3 (Slave)

数据流向：主服务器 → 二进制日志 → 从服务器中继日志 → 从服务器数据

                    主从复制的优势：
                    高可用性：主服务器故障时可快速切换到从服务器
读写分离：主服务器处理写操作，从服务器处理读操作
负载均衡：分散读取负载到多个从服务器
数据备份：从服务器作为实时数据备份
数据分析：在从服务器上进行报表和分析，不影响主服务器性能
地理分布：在不同地理位置部署从服务器，提高访问速度

                

主从复制配置

准备测试环境

-- 创建复制测试数据库
CREATE DATABASE replication_demo;
USE replication_demo;

-- 创建测试表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入测试数据
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');

INSERT INTO orders (user_id, order_number, total_amount, status) VALUES
(1, 'ORD-001', 99.99, 'paid'),
(2, 'ORD-002', 149.99, 'shipped'),
(3, 'ORD-003', 79.99, 'pending');

SELECT 'Test data created successfully' AS message;
                

主服务器配置

1. 修改主服务器配置文件 (my.cnf)

# /etc/mysql/my.cnf 或 /etc/my.cnf
[mysqld]
# 服务器唯一标识
server-id = 1

# 启用二进制日志
log-bin = mysql-bin
binlog-format = ROW  # 推荐使用 ROW 格式

# 二进制日志过期时间（天）
expire-logs-days = 7

# 二进制日志文件大小
max-binlog-size = 100M

# 同步二进制日志到磁盘
sync-binlog = 1

# 指定要复制的数据库（可选）
# binlog-do-db = replication_demo

# 指定不复制的数据库（可选）
# binlog-ignore-db = mysql
# binlog-ignore-db = information_schema
# binlog-ignore-db = performance_schema
# binlog-ignore-db = sys

# InnoDB 设置
innodb-flush-log-at-trx-commit = 1
innodb-support-xa = 1

# 半同步复制插件（可选）
# plugin-load = "rpl_semi_sync_master=semisync_master.so"
# rpl-semi-sync-master-enabled = 1
# rpl-semi-sync-master-timeout = 1000
                    

-- 2. 重启 MySQL 服务
-- sudo systemctl restart mysql

-- 3. 创建复制用户
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

-- 4. 查看主服务器状态
SHOW MASTER STATUS;

-- 记录输出结果，例如：
-- +------------------+----------+--------------+------------------+
-- | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-- +------------------+----------+--------------+------------------+
-- | mysql-bin.000001 |      154 |              |                  |
-- +------------------+----------+--------------+------------------+

-- 5. 锁定表（可选，用于一致性备份）
-- FLUSH TABLES WITH READ LOCK;

-- 6. 备份主服务器数据
-- mysqldump -u root -p --all-databases --master-data=2 > master_backup.sql

-- 7. 解锁表
-- UNLOCK TABLES;

-- 查看二进制日志文件
SHOW BINARY LOGS;

-- 查看二进制日志事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;
                

从服务器配置

1. 修改从服务器配置文件 (my.cnf)

# /etc/mysql/my.cnf 或 /etc/my.cnf
[mysqld]
# 服务器唯一标识（必须与主服务器不同）
server-id = 2

# 启用中继日志
relay-log = mysql-relay-bin

# 从服务器只读（推荐）
read-only = 1

# 超级用户可以在只读模式下写入
super-read-only = 0

# 指定要复制的数据库（可选）
# replicate-do-db = replication_demo

# 指定不复制的数据库（可选）
# replicate-ignore-db = mysql
# replicate-ignore-db = information_schema
# replicate-ignore-db = performance_schema
# replicate-ignore-db = sys

# 跳过特定错误（谨慎使用）
# slave-skip-errors = 1062,1053

# 中继日志自动清理
relay-log-purge = 1

# 半同步复制插件（可选）
# plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
# rpl-semi-sync-slave-enabled = 1

# 并行复制（MySQL 5.7+）
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
                    

-- 2. 重启从服务器 MySQL 服务
-- sudo systemctl restart mysql

-- 3. 恢复主服务器数据到从服务器
-- mysql -u root -p < master_backup.sql

-- 4. 配置主从复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',  -- 主服务器IP
    MASTER_PORT = 3306,
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'strong_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- 从主服务器状态获取
    MASTER_LOG_POS = 154;  -- 从主服务器状态获取

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

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

-- 重要字段说明：
-- Slave_IO_Running: Yes  -- IO线程运行状态
-- Slave_SQL_Running: Yes -- SQL线程运行状态
-- Seconds_Behind_Master: 0 -- 复制延迟（秒）
-- Last_IO_Error: -- IO错误信息
-- Last_SQL_Error: -- SQL错误信息

-- 7. 测试复制
-- 在主服务器上执行：
-- INSERT INTO replication_demo.users (username, email) VALUES ('test_user', 'test@example.com');

-- 在从服务器上检查：
-- SELECT * FROM replication_demo.users WHERE username = 'test_user';
                

复制监控和管理

复制状态监控

-- 主服务器监控
-- 查看主服务器状态
SHOW MASTER STATUS;

-- 查看连接的从服务器
SHOW SLAVE HOSTS;

-- 查看二进制日志状态
SHOW BINARY LOGS;

-- 查看主服务器变量
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'server_id';

-- 从服务器监控
-- 查看从服务器详细状态
SHOW SLAVE STATUS\G

-- 关键监控指标
SELECT 
    'Replication Status' as metric_type,
    'IO Thread' as component,
    IF(Slave_IO_Running = 'Yes', 'Running', 'Stopped') as status
FROM (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(@@global.slave_status, 'Slave_IO_Running: ', -1), '\n', 1) as Slave_IO_Running
) t

UNION ALL

SELECT 
    'Replication Status',
    'SQL Thread',
    'Check SHOW SLAVE STATUS';

-- 创建复制监控视图
CREATE VIEW replication_monitor AS
SELECT 
    'Replication Health Check' as report_type,
    NOW() as check_time,
    @@server_id as server_id,
    @@read_only as read_only_status,
    @@log_bin as binary_log_enabled;

SELECT * FROM replication_monitor;

-- 复制延迟监控
DELIMITER //
CREATE PROCEDURE CheckReplicationLag()
BEGIN
    DECLARE lag_seconds INT;
    DECLARE io_running VARCHAR(10);
    DECLARE sql_running VARCHAR(10);
    
    -- 注意：这个存储过程需要在从服务器上执行
    -- 实际环境中需要通过 SHOW SLAVE STATUS 获取这些值
    
    SELECT 'Replication lag monitoring' as info;
    SELECT 'Use SHOW SLAVE STATUS to check:' as instruction;
    SELECT 'Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running' as key_fields;
    
    -- 示例告警逻辑
    SET lag_seconds = 0;  -- 从 SHOW SLAVE STATUS 获取
    
    IF lag_seconds > 60 THEN
        SELECT 'WARNING: Replication lag > 60 seconds' as alert;
    ELSEIF lag_seconds > 10 THEN
        SELECT 'CAUTION: Replication lag > 10 seconds' as alert;
    ELSE
        SELECT 'OK: Replication lag is acceptable' as alert;
    END IF;
END //
DELIMITER ;

CALL CheckReplicationLag();
                

复制故障排除

-- 常见复制问题诊断

-- 1. 检查复制线程状态
SHOW SLAVE STATUS\G

-- 2. 查看错误日志
-- tail -f /var/log/mysql/error.log

-- 3. 重启复制（当出现错误时）
STOP SLAVE;
START SLAVE;

-- 4. 跳过错误事务（谨慎使用）
-- 当遇到特定错误需要跳过时
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 5. 重新同步从服务器
-- 当数据不一致时，重新从主服务器同步
STOP SLAVE;
RESET SLAVE;

-- 重新获取主服务器状态
-- 在主服务器上执行：SHOW MASTER STATUS;

-- 重新配置复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'strong_password',
    MASTER_LOG_FILE = 'mysql-bin.000002',  -- 新的日志文件
    MASTER_LOG_POS = 1024;  -- 新的位置

START SLAVE;

-- 6. 检查网络连接
-- 在从服务器上测试到主服务器的连接
-- mysql -h 192.168.1.100 -u replication_user -p

-- 7. 检查磁盘空间
SHOW VARIABLES LIKE 'datadir';
-- df -h /var/lib/mysql

-- 8. 检查二进制日志
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 5;

-- 9. 复制一致性检查
-- 使用 pt-table-checksum 工具（Percona Toolkit）
-- pt-table-checksum --host=master_host --databases=replication_demo

-- 10. 创建复制健康检查脚本
DELIMITER //
CREATE PROCEDURE ReplicationHealthCheck()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE check_result TEXT DEFAULT '';
    
    -- 检查服务器角色
    IF @@read_only = 1 THEN
        SET check_result = CONCAT(check_result, 'Server Role: Slave\n');
        
        -- 这里应该检查 SHOW SLAVE STATUS 的结果
        SET check_result = CONCAT(check_result, 'Slave Status: Check SHOW SLAVE STATUS\n');
    ELSE
        SET check_result = CONCAT(check_result, 'Server Role: Master\n');
        
        -- 检查二进制日志
        IF @@log_bin = 1 THEN
            SET check_result = CONCAT(check_result, 'Binary Log: Enabled\n');
        ELSE
            SET check_result = CONCAT(check_result, 'Binary Log: Disabled\n');
        END IF;
    END IF;
    
    -- 输出检查结果
    SELECT check_result as health_check_result;
    
    -- 显示当前配置
    SELECT 
        @@server_id as server_id,
        @@read_only as read_only,
        @@log_bin as log_bin_enabled,
        @@binlog_format as binlog_format;
END //
DELIMITER ;

CALL ReplicationHealthCheck();
                

复制性能优化

-- 复制性能优化配置

-- 1. 并行复制配置（MySQL 5.7+）
-- 在从服务器上配置
STOP SLAVE;

-- 设置并行复制
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;  -- 根据CPU核心数调整
SET GLOBAL slave_preserve_commit_order = 1;

START SLAVE;

-- 2. 二进制日志优化
-- 在主服务器上配置
SET GLOBAL sync_binlog = 1;  -- 安全性优先
-- SET GLOBAL sync_binlog = 0;  -- 性能优先（有风险）

SET GLOBAL binlog_cache_size = 1048576;  -- 1MB
SET GLOBAL max_binlog_cache_size = 4294967296;  -- 4GB

-- 3. 中继日志优化
-- 在从服务器上配置
SET GLOBAL relay_log_purge = 1;  -- 自动清理中继日志
SET GLOBAL relay_log_space_limit = 1073741824;  -- 1GB

-- 4. 网络优化
SET GLOBAL slave_net_timeout = 60;  -- 网络超时时间
SET GLOBAL slave_compressed_protocol = 1;  -- 启用压缩（高延迟网络）

-- 5. 监控复制性能
SHOW STATUS LIKE 'Slave_running';
SHOW STATUS LIKE 'Slave_retried_transactions';
SHOW STATUS LIKE 'Slave_heartbeat_period';

-- 查看复制相关的状态变量
SHOW STATUS LIKE '%slave%';
SHOW STATUS LIKE '%master%';

-- 6. 创建性能监控表
CREATE TABLE replication_performance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_id INT,
    lag_seconds INT,
    io_thread_status VARCHAR(20),
    sql_thread_status VARCHAR(20),
    last_error TEXT,
    check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 性能监控存储过程
DELIMITER //
CREATE PROCEDURE LogReplicationPerformance()
BEGIN
    -- 这个存储过程应该在从服务器上运行
    -- 实际实现需要解析 SHOW SLAVE STATUS 的结果
    
    INSERT INTO replication_performance_log 
    (server_id, lag_seconds, io_thread_status, sql_thread_status, last_error)
    VALUES 
    (@@server_id, 0, 'Running', 'Running', 'No errors');
    
    SELECT 'Performance data logged' as result;
END //
DELIMITER ;

-- 定期执行性能监控
-- 可以通过 cron 或 MySQL Event Scheduler 定期调用
-- CREATE EVENT replication_monitor_event
-- ON SCHEDULE EVERY 1 MINUTE
-- DO CALL LogReplicationPerformance();
                

高级复制拓扑

🔗 链式复制

Master → Slave1 → Slave2，适合地理分布式部署

⭐ 星型复制

一个主服务器对应多个从服务器，最常见的拓扑

🔄 双主复制

两个服务器互为主从，实现双向同步

🌐 多源复制

一个从服务器从多个主服务器复制数据

双主复制配置

-- 双主复制配置（Master-Master Replication）

-- 服务器A配置 (my.cnf)
-- [mysqld]
-- server-id = 1
-- log-bin = mysql-bin
-- auto-increment-increment = 2
-- auto-increment-offset = 1
-- binlog-do-db = replication_demo

-- 服务器B配置 (my.cnf)
-- [mysqld]
-- server-id = 2
-- log-bin = mysql-bin
-- auto-increment-increment = 2
-- auto-increment-offset = 2
-- binlog-do-db = replication_demo

-- 在服务器A上创建复制用户
CREATE USER 'repl_user_a'@'%' IDENTIFIED BY 'password_a';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user_a'@'%';
FLUSH PRIVILEGES;

-- 在服务器B上创建复制用户
CREATE USER 'repl_user_b'@'%' IDENTIFIED BY 'password_b';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user_b'@'%';
FLUSH PRIVILEGES;

-- 在服务器A上配置到服务器B的复制
CHANGE MASTER TO
    MASTER_HOST = 'server_b_ip',
    MASTER_USER = 'repl_user_b',
    MASTER_PASSWORD = 'password_b',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

START SLAVE;

-- 在服务器B上配置到服务器A的复制
CHANGE MASTER TO
    MASTER_HOST = 'server_a_ip',
    MASTER_USER = 'repl_user_a',
    MASTER_PASSWORD = 'password_a',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

START SLAVE;

-- 测试双主复制
-- 在服务器A上插入数据
INSERT INTO replication_demo.users (username, email) VALUES ('user_from_a', 'a@example.com');

-- 在服务器B上插入数据
INSERT INTO replication_demo.users (username, email) VALUES ('user_from_b', 'b@example.com');

-- 检查两个服务器上的数据是否一致
SELECT * FROM replication_demo.users ORDER BY id;
                

多源复制配置 (MySQL 5.7+)

-- 多源复制配置
-- 一个从服务器从多个主服务器复制数据

-- 从服务器配置 (my.cnf)
-- [mysqld]
-- server-id = 100
-- master-info-repository = TABLE
-- relay-log-info-repository = TABLE
-- relay-log-recovery = ON

-- 配置第一个主服务器的复制通道
CHANGE MASTER TO
    MASTER_HOST = 'master1_ip',
    MASTER_USER = 'repl_user1',
    MASTER_PASSWORD = 'password1',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154
    FOR CHANNEL 'master1';

-- 配置第二个主服务器的复制通道
CHANGE MASTER TO
    MASTER_HOST = 'master2_ip',
    MASTER_USER = 'repl_user2',
    MASTER_PASSWORD = 'password2',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154
    FOR CHANNEL 'master2';

-- 启动所有复制通道
START SLAVE FOR CHANNEL 'master1';
START SLAVE FOR CHANNEL 'master2';

-- 查看多源复制状态
SHOW SLAVE STATUS FOR CHANNEL 'master1'\G
SHOW SLAVE STATUS FOR CHANNEL 'master2'\G

-- 停止特定通道
STOP SLAVE FOR CHANNEL 'master1';

-- 重置特定通道
RESET SLAVE FOR CHANNEL 'master1';

-- 查看所有复制通道
SELECT 
    CHANNEL_NAME,
    HOST,
    PORT,
    USER,
    SOURCE_LOG_FILE,
    SOURCE_LOG_POS
FROM performance_schema.replication_connection_configuration;

-- 查看复制应用状态
SELECT 
    CHANNEL_NAME,
    SERVICE_STATE,
    LAST_ERROR_MESSAGE,
    LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_applier_status;
                

故障转移和高可用

手动故障转移

-- 手动故障转移步骤

-- 1. 检测主服务器故障
-- 在监控系统中检测主服务器是否响应
-- mysql -h master_ip -u monitor_user -p -e "SELECT 1;"

-- 2. 选择新的主服务器
-- 选择数据最新、延迟最小的从服务器
-- 在候选从服务器上检查复制状态
SHOW SLAVE STATUS\G

-- 3. 停止所有从服务器的复制
STOP SLAVE;

-- 4. 在新主服务器上启用写入
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- 5. 重新配置其他从服务器
-- 获取新主服务器的状态
SHOW MASTER STATUS;

-- 在其他从服务器上重新配置复制
CHANGE MASTER TO
    MASTER_HOST = 'new_master_ip',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

START SLAVE;

-- 6. 更新应用程序配置
-- 修改应用程序的数据库连接配置，指向新的主服务器

-- 7. 验证故障转移
-- 测试写入操作
INSERT INTO replication_demo.users (username, email) VALUES ('failover_test', 'test@example.com');

-- 检查从服务器是否正常复制
SELECT * FROM replication_demo.users WHERE username = 'failover_test';
                

自动故障转移脚本

#!/bin/bash
# mysql_failover.sh - MySQL 自动故障转移脚本

# 配置参数
MASTER_HOST="192.168.1.100"
SLAVE_HOSTS=("192.168.1.101" "192.168.1.102" "192.168.1.103")
MYSQL_USER="admin"
MYSQL_PASS="password"
REPL_USER="replication_user"
REPL_PASS="repl_password"
LOG_FILE="/var/log/mysql_failover.log"

# 日志函数
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE
}

# 检查服务器连接
check_server() {
    local host=$1
    mysql -h $host -u $MYSQL_USER -p$MYSQL_PASS -e "SELECT 1;" > /dev/null 2>&1
    return $?
}

# 获取从服务器延迟
get_slave_lag() {
    local host=$1
    local lag=$(mysql -h $host -u $MYSQL_USER -p$MYSQL_PASS -e \
        "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
    echo ${lag:-999999}
}

# 选择最佳从服务器作为新主服务器
select_new_master() {
    local best_slave=""
    local min_lag=999999
    
    for slave in "${SLAVE_HOSTS[@]}"; do
        if check_server $slave; then
            local lag=$(get_slave_lag $slave)
            log_message "Slave $slave lag: $lag seconds"
            
            if [ $lag -lt $min_lag ]; then
                min_lag=$lag
                best_slave=$slave
            fi
        else
            log_message "Slave $slave is not accessible"
        fi
    done
    
    echo $best_slave
}

# 提升从服务器为主服务器
promote_slave_to_master() {
    local new_master=$1
    
    log_message "Promoting $new_master to master"
    
    # 停止复制
    mysql -h $new_master -u $MYSQL_USER -p$MYSQL_PASS -e "STOP SLAVE;"
    
    # 启用写入
    mysql -h $new_master -u $MYSQL_USER -p$MYSQL_PASS -e \
        "SET GLOBAL read_only = 0; SET GLOBAL super_read_only = 0;"
    
    # 重置从服务器状态
    mysql -h $new_master -u $MYSQL_USER -p$MYSQL_PASS -e "RESET SLAVE ALL;"
    
    log_message "$new_master promoted to master successfully"
}

# 重新配置其他从服务器
reconfigure_slaves() {
    local new_master=$1
    
    # 获取新主服务器状态
    local master_status=$(mysql -h $new_master -u $MYSQL_USER -p$MYSQL_PASS -e \
        "SHOW MASTER STATUS\G")
    local log_file=$(echo "$master_status" | grep "File:" | awk '{print $2}')
    local log_pos=$(echo "$master_status" | grep "Position:" | awk '{print $2}')
    
    log_message "New master status: $log_file, $log_pos"
    
    for slave in "${SLAVE_HOSTS[@]}"; do
        if [ "$slave" != "$new_master" ] && check_server $slave; then
            log_message "Reconfiguring slave $slave"
            
            # 停止复制
            mysql -h $slave -u $MYSQL_USER -p$MYSQL_PASS -e "STOP SLAVE;"
            
            # 重新配置主服务器
            mysql -h $slave -u $MYSQL_USER -p$MYSQL_PASS -e "
                CHANGE MASTER TO
                    MASTER_HOST = '$new_master',
                    MASTER_USER = '$REPL_USER',
                    MASTER_PASSWORD = '$REPL_PASS',
                    MASTER_LOG_FILE = '$log_file',
                    MASTER_LOG_POS = $log_pos;
            "
            
            # 启动复制
            mysql -h $slave -u $MYSQL_USER -p$MYSQL_PASS -e "START SLAVE;"
            
            log_message "Slave $slave reconfigured successfully"
        fi
    done
}

# 发送告警通知
send_alert() {
    local message=$1
    log_message "ALERT: $message"
    
    # 发送邮件告警
    echo "$message" | mail -s "MySQL Failover Alert" admin@example.com
    
    # 发送到监控系统
    # curl -X POST "http://monitoring-system/alert" -d "message=$message"
}

# 主故障转移流程
main_failover() {
    log_message "Starting MySQL failover process"
    
    # 检查主服务器状态
    if check_server $MASTER_HOST; then
        log_message "Master server $MASTER_HOST is accessible, no failover needed"
        return 0
    fi
    
    log_message "Master server $MASTER_HOST is not accessible, starting failover"
    send_alert "Master server $MASTER_HOST failed, starting automatic failover"
    
    # 选择新的主服务器
    local new_master=$(select_new_master)
    
    if [ -z "$new_master" ]; then
        log_message "No suitable slave found for promotion"
        send_alert "Failover failed: No suitable slave server available"
        return 1
    fi
    
    log_message "Selected $new_master as new master"
    
    # 执行故障转移
    promote_slave_to_master $new_master
    reconfigure_slaves $new_master
    
    log_message "Failover completed successfully"
    send_alert "Failover completed: $new_master is now the master server"
    
    # 更新配置文件或DNS记录
    # update_application_config $new_master
    
    return 0
}

# 执行故障转移
main_failover
                

使用 MHA 进行自动故障转移

# MHA (Master High Availability) 是专业的 MySQL 高可用解决方案

# 1. 安装 MHA
# 在管理节点安装 MHA Manager
# yum install mha4mysql-manager

# 在所有 MySQL 节点安装 MHA Node
# yum install mha4mysql-node

# 2. 配置 MHA
# 创建 MHA 配置文件 /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mha_password
ping_interval=3
repl_user=replication_user
repl_password=repl_password
ssh_user=root

[server1]
hostname=192.168.1.100
port=3306
candidate_master=1

[server2]
hostname=192.168.1.101
port=3306
candidate_master=1

[server3]
hostname=192.168.1.102
port=3306
no_master=1

# 3. 检查 MHA 配置
masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

# 4. 启动 MHA Manager
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover

# 5. 监控 MHA 状态
masterha_check_status --conf=/etc/mha/app1.cnf

# 6. 手动故障转移
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=dead --dead_master_host=192.168.1.100 --new_master_host=192.168.1.101

# 7. 在线主服务器切换
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.1.101 --orig_master_is_new_slave
                

最佳实践和注意事项

🎯 主从复制最佳实践：

监控复制延迟：建立实时监控，及时发现复制问题
定期检查一致性：使用工具验证主从数据一致性
合理配置参数：根据业务需求调整复制相关参数
网络优化：确保主从服务器间网络稳定可靠
故障转移演练：定期进行故障转移测试
备份策略：主从复制不能替代备份
权限管理：严格控制复制用户权限

⚠️ 常见问题和注意事项：

数据不一致：避免在从服务器上进行写操作
复制延迟：监控并优化网络和配置参数
二进制日志清理：合理设置日志保留期限
磁盘空间：监控中继日志和二进制日志占用空间
字符集问题：确保主从服务器字符集一致
时区设置：保持主从服务器时区同步
版本兼容性：注意 MySQL 版本间的兼容性

< 1s

理想复制延迟

99.9%

目标可用性

< 30s

故障转移时间

0

数据丢失容忍度

-- 清理示例对象
DROP VIEW IF EXISTS replication_monitor;
DROP TABLE IF EXISTS replication_performance_log;
DROP PROCEDURE IF EXISTS CheckReplicationLag;
DROP PROCEDURE IF EXISTS LogReplicationPerformance;
DROP PROCEDURE IF EXISTS ReplicationHealthCheck;
DROP DATABASE IF EXISTS replication_demo;

SELECT 'MySQL replication tutorial completed. All example objects cleaned up.' AS message;
                