🔧

MySQL 主从复制

📂 devops ⏱ 3 min 467 words

MySQL 主从复制

什么是主从复制

MySQL 主从复制是将主数据库的数据变更自动复制到从数据库的技术。它实现了数据冗余、读写分离和高可用性。

复制原理

主库 (Master)                    从库 (Slave)
┌─────────────┐                ┌─────────────┐
│   写操作    │                │   读操作    │
│      ↓      │                │      ↑      │
│  Binlog    │ ──复制──→       │  Relay Log │
│      ↓      │                │      ↓      │
│  数据变更   │                │  SQL Thread │
└─────────────┘                └─────────────┘

配置主库

修改 my.cnf

[mysqld]
# 服务器唯一 ID
server-id = 1

# 启用 binlog
log-bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

# 同步设置
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

重启 MySQL

sudo systemctl restart mysql

创建复制用户

-- 登录 MySQL
mysql -u root -p

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

-- 查看主库状态
SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置从库

修改 my.cnf

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

# 只读模式（可选）
read_only = 1
super_read_only = 1

# 中继日志
relay-log = relay-bin
relay_log_recovery = 1

重启 MySQL

sudo systemctl restart mysql

配置复制

-- 登录从库 MySQL
mysql -u root -p

-- 配置主库信息
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

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

复制状态监控

查看主库状态

SHOW MASTER STATUS;
SHOW MASTER LOGS;
SHOW PROCESSLIST;

查看从库状态

SHOW SLAVE STATUS\G

关键字段：

Slave_IO_Running: IO 线程是否运行
Slave_SQL_Running: SQL 线程是否运行
Seconds_Behind_Master: 复制延迟（秒）
Last_Error: 最后错误信息

监控脚本

#!/bin/bash
# check_replication.sh

SLAVE_HOST="192.168.1.101"
SLAVE_USER="monitor"
SLAVE_PASS="monitor_password"

# 检查复制状态
STATUS=$(mysql -h $SLAVE_HOST -u $SLAVE_USER -p$SLAVE_PASS -e "SHOW SLAVE STATUS\G" 2>/dev/null)

IO_RUNNING=$(echo "$STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
DELAY=$(echo "$STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "复制异常！IO: $IO_RUNNING, SQL: $SQL_RUNNING"
    # 发送告警
fi

if [ "$DELAY" -gt 60 ]; then
    echo "复制延迟过大: ${DELAY}秒"
fi

常见问题处理

复制中断

-- 查看错误
SHOW SLAVE STATUS\G

-- 跳过错误（谨慎使用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 跳过特定错误
STOP SLAVE;
SET GLOBAL slave_skip_errors = 1062;
START SLAVE;

主从数据不一致

# 使用 pt-table-checksum 检查
pt-table-checksum --replicate=percona.checksums \
  h=192.168.1.100,u=monitor,p=monitor_password

# 使用 pt-table-sync 修复
pt-table-sync --replicate=percona.checksums \
  h=192.168.1.100,h=192.168.1.101 \
  --execute

重建从库

# 在主库备份
mysqldump -u root -p --all-databases --master-data > full_backup.sql

# 在从库恢复
mysql -u root -p < full_backup.sql

# 重新配置复制
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=154;
START SLAVE;

GTID 复制

配置 GTID

# 主库和从库都添加
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

使用 GTID 配置复制

-- 主库创建用户（同上）

-- 从库配置
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION = 1;
START SLAVE;

实践案例

部署读写分离

# Nginx 配置读写分离
upstream write_db {
    server 192.168.1.100:3306;
}

upstream read_db {
    server 192.168.1.101:3306;
    server 192.168.1.102:3306;
}

server {
    listen 80;
    
    # 写操作
    location /api/write {
        proxy_pass http://write_db;
    }
    
    # 读操作
    location /api/read {
        proxy_pass http://read_db;
    }
}

主从切换

-- 1. 停止从库复制
STOP SLAVE;

-- 2. 重置从库
RESET SLAVE ALL;

-- 3. 将从库提升为主库
SET GLOBAL read_only = 0;

-- 4. 更新应用配置

性能优化

# 主库优化
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format = ROW

# 从库优化
[mysqld]
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

最佳实践

使用 GTID 简化复制管理
监控复制延迟
定期检查数据一致性
设置合理的 binlog 过期时间
使用半同步复制提高数据安全性

总结

MySQL 主从复制是实现数据冗余和高可用的重要技术。正确配置和监控复制可以确保数据安全和系统可用性。