【Linux服务器】-MySQL 8.0 主从复制完整部署文档

（适用于 1 主 1 备，备库在主库故障时可接管写入）

环境信息

0. 重要提醒

1. 确认备份坐标：请确保 MASTER_LOG_FILE 和 MASTER_LOG_POS 来自主库全量备份时 mysqldump 生成的注释，而不是实时 SHOW MASTER STATUS 的值。
2. 参数调优：innodb_buffer_pool_size 已根据备库 8 GB 内存计算为 4G，主库可按实际内存调整（若主库也是 8 GB，建议同设为 4G）。
3. 密码安全：生产环境请务必修改复制账号 repl 的密码，本文仅作示例。

1. 安装 MySQL 后的基础配置

1.1 确保服务运行与开机自启

systemctl status mysqld
systemctl enable mysqld

1.2 修改 root 密码（若为初始安装）

grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
sql
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的强密码';
FLUSH PRIVILEGES;

1.3 基础安全设置（可选）

mysql_secure_installation

2. 系统配置（/etc/my.cnf）

2.1 主库配置

编辑 /etc/my.cnf，在 [mysqld] 下添加或修改：

vi /etc/my.cnf

[mysqld]
# 基础标识与日志
server-id = 1
log-bin = mysql-bin
max_binlog_size = 1024M
binlog_expire_logs_seconds = 1209600       # 14 天自动清理

# 自增 ID 错开（1主1备，increment=2）
auto_increment_offset = 1
auto_increment_increment = 2

# 忽略系统库的 binlog
binlog-ignore-db = mysql,information_schema,performance_schema,sys

# 内存与磁盘（根据实际硬件调整）
innodb_buffer_pool_size = 4G               # 假设主库内存 8GB，设为 50%
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

2.2 备库配置

编辑 /etc/my.cnf，在 [mysqld] 下添加或修改：

[mysqld]
server-id = 2

# 中继日志
relay_log = mysql-relay-bin
relay_log_recovery = 1

# 自增 ID 错开（备库 offset=2）
auto_increment_offset = 2
auto_increment_increment = 2

# 若将来可能提升为主库，可提前开启 binlog（可选）
# log-bin = mysql-bin

# 内存与磁盘（备库 8GB 内存，分配 4GB 给 InnoDB）
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

注意：innodb_buffer_pool_size 建议设为物理内存的 50%~60%。8 GB 内存推荐 4G 或 5G。
修改后，分别重启主备库 MySQL：

systemctl restart mysqld

3. 主库：创建复制专用账号

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

验证：

SELECT user, host FROM mysql.user WHERE user = 'repl';
SHOW GRANTS FOR 'repl'@'%';

4. 备库：获取与主库一致的数据

4.1 在主库做全量一致性备份（不锁表）

mysqldump -u root -p \
  --all-databases \
  --master-data=2 \
  --single-transaction \
  --flush-logs \
  --routines \
  --triggers \
  --events \
  > /tmp/master_backup_$(date +%Y%m%d).sql

4.2 确认备份文件中的 binlog 坐标

grep "CHANGE MASTER TO" /tmp/master_backup_$(date +%Y%m%d).sql

应看到类似输出：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000997', MASTER_LOG_POS=1069750293;

请核实此处文件与位置是否与备份文件一致，本文档后续将使用 mysql-bin.000997 和 1069750293。

4.3 传输备份文件到备库

scp /tmp/master_backup_*.sql 192.168.100.2:/tmp/

4.4 备库导入数据（使用 screen 防止中断）

# 安装 screen（如未安装）
yum install screen -y

# 启动 screen 会话
screen -S import_db

# 导入（若安装 pv 可查看进度）
pv /tmp/master_backup_*.sql | mysql -u root -p \
  --init-command="SET SESSION foreign_key_checks=0; SET SESSION unique_checks=0; SET SESSION sql_log_bin=0;"

按 Ctrl+A 再 D 脱离 screen，导入继续后台运行。
重新查看：screen -r import_db

5. 备库配置复制并启动

5.1 设置复制源

STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO
  MASTER_HOST = '192.168.100.1',
  MASTER_PORT = 3306,
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl',
  MASTER_LOG_FILE = 'mysql-bin.000997',    -- 来自备份文件
  MASTER_LOG_POS = 1069750293;             -- 来自备份文件
START SLAVE;

5.2 检查复制状态

SHOW SLAVE STATUS\G

关键字段：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master 逐渐下降

6. 性能优化

6.1 启用并行复制（备库执行）

STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_parallel_workers = 8;          -- 根据 CPU 核数调整
START SLAVE SQL_THREAD;

若想持久化，在备库 my.cnf 中添加：

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8

6.2 主库 WRITESET 优化（可选，需重启主库）

在 [mysqld] 下添加：

binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64

7. 日常维护

7.1 监控延迟

SHOW SLAVE STATUS\G
关注 Seconds_Behind_Master，若持续增大需排查。

7.2 清理主库 binlog

SHOW BINARY LOGS;
PURGE BINARY LOGS TO 'mysql-bin.000997';   -- 只删除早于备库 Relay_Master_Log_File 的日志
已配置自动清理 binlog_expire_logs_seconds = 1209600（14天）。

8. 故障切换（主库宕机 → 备库接管）

1. 应用连接切换到备库 IP 192.168.100.2。
2. 在备库执行：

STOP SLAVE;
RESET SLAVE ALL;
备库即成为独立可写数据库。

原主库恢复后，如需重新搭建主从，可将其作为新备库，重新备份与配置。

9. 常见故障处理

跳过单个事务（慎用）：

STOP SLAVE;
SET GLOBAL slave_parallel_workers = 0;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
-- 追平后恢复并行
SET GLOBAL slave_parallel_workers = 8;