【分布式】部署MySQL主从数据库--LNMP构建（超详细）

'# 【分布式】部署MySQL主从数据库--LNMP构建（超详细）

一、背景与问题

在分布式系统中，单点数据库的性能和可靠性往往成为瓶颈。MySQL主从复制技术通过将主数据库（Master）的写操作同步到从数据库（Slave），可以实现读写分离、数据冗余和负载均衡。这种架构在电商系统、大数据分析平台等场景中广泛使用。

典型的使用场景包括：

1. 高并发读场景：通过从库分担查询压力
2. 数据备份：定期从库导出数据用于分析
3. 地域分片：将主库部署在本地，从库部署在异地

但这种架构也存在以下挑战：

• 复制延迟（主从数据同步延迟）
• 网络中断导致的数据不一致
• 主库写入压力对从库的拖累
• 索引和查询优化的特殊需求

二、基本原理

MySQL主从复制基于二进制日志（binlog）实现，其核心流程如下：

1. 事务记录：主库将所有事务操作记录到binlog中（格式可选ROW/STATEMENT/MIXED）
2. 同步传输：通过专用线程（I/O thread）将binlog传输到从库
3. 重放执行：从库通过SQL thread重放binlog，将变更同步到本地

关键概念：

• GTID（全局事务标识）：唯一标识每个事务的UUID:POS，便于故障恢复
• 同步模式：包括异步（默认）、半同步（需配置）和强同步（需专业设备）
• 延迟复制：通过slave_sql_run参数控制从库处理速度

三、环境准备

硬件要求：

• 主库：1核2G RAM，SSD磁盘
• 从库：1核2G RAM，SSD磁盘
• 网络：主从之间需保证TCP 3306端口可达

软件准备：

# 安装MySQL 8.0.32（推荐版本）
sudo apt update
sudo apt install mysql-server=8.0.32-0ubuntu0.22.04.1

配置文件准备：

# /etc/mysql/my.cnf 主库配置
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON

# /etc/mysql/my.cnf 从库配置
[mysqld]
server-id=2
relay-log=mysql-relay
relay-log-index=mysql-relay.index

四、核心实现

1. 主库配置与授权

# 创建复制用户
mysql -u root -p -e "
CREATE USER 'repl'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
"

# 查看主库状态
mysql -u root -p -e "SHOW MASTER STATUS\G"

关键代码解释：

• REPLICATION SLAVE权限允许从库进行复制
• SHOW MASTER STATUS输出包含File（binlog文件名）和Position（起始位置）

2. 从库配置与同步

# 修改从库配置文件
sudo systemctl stop mysql
sudo nano /etc/mysql/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON

sudo systemctl start mysql

# 配置从库连接主库
mysql -u root -p -e "
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='SecurePass123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_AUTO_POSITION=1;
START SLAVE;
"

关键代码解释：

• MASTER_AUTO_POSITION=1启用GTID自动定位
• START SLAVE启动复制线程

3. 复制状态监控

# 查看复制状态
SHOW SLAVE STATUS\G

# 关键字段解释：
Slave_IO_Running: Yes（表示I/O线程正常）
Slave_SQL_Running: Yes（表示SQL线程正常）
Seconds_Behind_Master: 0（表示同步延迟）

五、完整案例

案例场景：电商系统读写分离架构

部署步骤：

1. 主库配置（192.168.1.100）

   # 创建测试数据库
   mysql -u root -p -e "CREATE DATABASE test_db;"

2. 从库配置（192.168.1.101）

   # 创建测试数据库
   mysql -u root -p -e "CREATE DATABASE test_db;"

3. 主库写入测试

   mysql -u root -p -e "
   USE test_db;
   CREATE TABLE test (id INT PRIMARY KEY);
   INSERT INTO test VALUES (1);
   "

4. 从库验证

   mysql -u root -p -e "
   USE test_db;
   SELECT * FROM test;
   "

读写分离PHP脚本（位于LNMP服务器）：

<?php
// 数据库配置
$masterConfig = [
    'host' => '192.168.1.100',
    'user' => 'root',
    'password' => 'securepass',
    'db' => 'test_db'
];

$slaveConfig = [
    'host' => '192.168.1.101',
    'user' => 'root',
    'password' => 'securepass',
    'db' => 'test_db'
];

// 判断写操作
if (isset($_GET['write'])) {
    $pdo = new PDO(
        "mysql:host={$masterConfig['host']};dbname={$masterConfig['db']};charset=utf8mb4",
        $masterConfig['user'], 
        $masterConfig['password']
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("INSERT INTO test VALUES (2)");
} else {
    // 读操作随机选择主库或从库
    $is_master = mt_rand(0, 1) == 1;
    $pdo = $is_master 
        ? new PDO("mysql:host={$masterConfig['host']};...", $masterConfig['user'], $masterConfig['password']) 
        : new PDO("mysql:host={$slaveConfig['host']};...", $slaveConfig['user'], $slaveConfig['password']);
    
    $stmt = $pdo->query("SELECT * FROM test");
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);
}
?>

六、源码解析

主库binlog生成机制：

// MySQL源码中binlog生成核心逻辑（简化版）
void log_bin_log_event(THD *thd, const char *query) {
    if (gtid_mode) {
        // 生成GTID标识
        gtid_t gtid = generate_gtid();
        write_to_binlog(gtid, query);
    } else {
        write_to_binlog(query);
    }
}

从库SQL线程处理：

void process_binlog_event(THD *thd, const char *event_data) {
    if (is_transactional_event(event_data)) {
        // 重放事务
        execute_sql_event(thd, event_data);
    } else {
        // 处理行级变更
        apply_row_event(thd, event_data);
    }
}

七、进阶使用

1. 多从库架构

# 配置第二个从库（192.168.1.102）
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='repl',
MASTER_PASSWORD='SecurePass123!',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154,
MASTER_AUTO_POSITION=1;
START SLAVE;

2. 高可用方案

# 使用MySQL Group Replication（8.0+）
CREATE SERVER 'slave1' FOREIGN DATA WRAPPER 'mysql'
OPTIONS(HOST '192.168.1.101', USER 'repl', PASSWORD 'SecurePass123!', DATABASE 'test_db');

3. 增强复制

# 启用半同步复制
SET GLOBAL plugin_dir='/usr/lib/mysql/plugin/';
SET GLOBAL plugin_load='rpl_semi_sync_master.so;rpl_semi_sync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=1000;

八、性能与工程实践

1. 性能优化

• 主库参数优化：

  sync_binlog=1
  innodb_flush_log_at_trx_commit=1

• 从库参数优化：

  innodb_buffer_pool_size=2G
  slave_parallel_threads=4

2. 索引优化

# 为查询字段添加索引
CREATE INDEX idx_name ON test(name);

3. 异常处理

// 异常捕获示例
try {
    $pdo->exec("INSERT INTO test VALUES (3)");
} catch (PDOException $e) {
    if ($e->getCode() == 1022) { // 唯一约束冲突
        echo "Duplicate key error";
    } else {
        throw $e;
    }
}

4. 安全加固

• 使用SSL加密复制：

  [mysqld]
  ssl-cert=/etc/ssl/certs/mysql-cert.pem
  ssl-key=/etc/ssl/private/mysql-key.pem

九、常见问题与踩坑

1. 同步延迟问题

现象：Seconds_Behind_Master持续增大
解决：

• 检查主库写入压力
• 增加从库资源（CPU/内存）
• 优化慢查询

2. GTID冲突问题

现象：Last_Error提示"GTID not applied"
解决：

• 确认主库server_id唯一
• 使用RESET SLAVE重置从库
• 检查主库gtid_mode配置

3. 网络中断问题

现象：复制中断后数据不一致
解决：

• 配置主从自动重连
• 部署Keepalived实现VIP漂移
• 使用rsync做冷备份

十、最佳实践

1. 主从架构建议：

   • 主库只处理写操作
   • 从库负责读操作
   • 使用读写分离中间件（如ProxySQL）

2. 监控建议：

   • 部署Prometheus+Grafana监控
   • 设置自动报警阈值（如延迟>30s）

3. 维护建议：

   • 定期执行FLUSH TABLES WITH READ LOCK进行备份
   • 保持主从版本一致
   • 避免在从库执行写操作

十一、总结

MySQL主从复制是分布式系统中重要的数据同步机制，通过理解其底层原理和实现细节，可以更好地应对生产环境中的各种挑战。在部署过程中，需要特别注意网络配置、权限管理、数据一致性等问题。对于高并发读写场景，合理设计主从架构并配合缓存、中间件等技术，可以显著提升系统性能和可靠性。

但需要注意的是，主从复制并不适合所有场景：

• 不适合频繁更新的场景（会导致同步延迟）
• 不适合高写入压力场景（主库负担重）
• 不适合对数据一致性要求极高的场景（如金融系统）

在选择主从架构时，应综合考虑业务需求、数据特征、系统规模等因素，结合监控系统和自动化运维工具，构建稳定可靠的分布式数据库体系。