萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

雲主機上搭建 MySQL 主從複製:完整步驟與避坑指南

MySQL 主從複製架構優勢

主從複製是 MySQL 高可用與讀寫分離的基石。通過 Binlog 異步/半同步傳輸,實現數據實時同步,具備:

  • 讀寫分離:主庫寫、從庫讀,線性擴展讀性能
  • 實時備份:從庫作為熱備,主庫故障可秒級切換
  • 離線分析:從庫跑報表查詢,不影響主庫 OLTP 性能

環境規劃

角色 內網 IP 公網 IP 版本 Server ID
Master 10.4.14.168 101.69.178.208 MySQL 5.7+ 1
Slave 10.4.5.9 101.69.178.219 MySQL 5.7+ 2

步驟 1:安裝 MySQL(雙節點同步執行)

# CentOS 7/8 使用官方 Yum 源
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install -y mysql-community-server

# 或 Ubuntu
apt update && apt install -y mysql-server

# 驗證安裝
mysql --version
# 輸出示例:mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

步驟 2:初始化與安全加固

systemctl start mysqld
systemctl enable mysqld

# 獲取臨時 root 密碼(MySQL 5.7+)
grep 'temporary password' /var/log/mysqld.log

# 登錄並修改密碼、刪除匿名用戶、禁止遠程 root
mysql -uroot -p'TempPass#123'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongPass@2024';
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

步驟 3:主庫配置 /etc/my.cnf

[mysqld]
# 基礎設置
server-id = 1
port = 3306
bind-address = 0.0.0.0          # 監聽所有網卡
datadir = /data/mysql
socket = /var/lib/mysql/mysql.sock

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# Binlog 核心配置
log-bin = /data/mysql/binlog/mysql-bin
binlog-format = ROW             # 推薦 ROW 格式,數據一致性最強
expire_logs_days = 7            # 自動清理 7 天前 Binlog
max_binlog_size = 100M
binlog_rows_query_log_events = ON  # 記錄原始 SQL,便於審計

# InnoDB 優化
innodb_buffer_pool_size = 2G    # 物理內存 60-70%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1                 # 雙 1 配置,保證數據零丟失

# 連接優化
max_connections = 1000
max_connect_errors = 100000
skip-name-resolve               # 跳過 DNS 解析,加速連接

步驟 4:創建複製用戶(主庫執行)

CREATE USER 'repl_user'@'10.4.5.%' IDENTIFIED BY 'ReplPass@2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.4.5.%';
FLUSH PRIVILEGES;

# 查看主庫 Binlog 位置
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      412 |              |                  |
+------------------+----------+--------------+------------------+

步驟 5:從庫配置 /etc/my.cnf

[mysqld]
server-id = 2
port = 3306
bind-address = 0.0.0.0
datadir = /data/mysql

# Relay Log 配置
relay-log = /data/mysql/relay/relay-bin
relay-log-index = /data/mysql/relay/relay-bin.index
log-slave-updates = ON          # 級聯從庫必開
read-only = ON                  # 防止誤寫入

# 複製優化(MySQL 5.7+)
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4      # 並行複製線程數
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = ON         # 防止 Relay Log 損壞

步驟 6:配置主從關聯並啟動(從庫執行)

CHANGE MASTER TO
  MASTER_HOST = '10.4.14.168',
  MASTER_PORT = 3306,
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'ReplPass@2024',
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 412,
  MASTER_AUTO_POSITION = 0,     # GTID 模式設為 1
  GET_MASTER_PUBLIC_KEY = 1;    # caching_sha2_password 必需

START SLAVE;

# 檢查狀態
SHOW SLAVE STATUS\G
# 關鍵指標:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0

步驟 7:驗證同步

# 主庫建表插數
CREATE DATABASE test_repl;
USE test_repl;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t1 VALUES (1, 'master_data');

# 從庫查詢
USE test_repl;
SELECT * FROM t1;
# 應返回同樣數據

常見故障與排查

現象 可能原因 解決方案
Slave_IO_Running: Connecting 網絡不通/防火牆/密碼錯誤 telnet 測試 3306、檢查 security group、驗證用戶權限
Slave_SQL_Running: No 數據衝突/主鍵重複/結構不一致 SHOW SLAVE STATUS 看 Last_Error,跳過錯誤或重構從庫
Seconds_Behind_Master 偏大 從庫硬件弱/大事務/並行複製未開 升級從庫配置、開啟並行複製、拆分大事務
GTID 模式下報錯 GTID 集合不匹配 RESET MASTER/SLAVE 重新同步,或使用 mysqlbinlog 修補

進階:GTID 模式部署(推薦 MySQL 5.6+)

在雙方 my.cnf 添加:

gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON

配置主從時簡化為:

CHANGE MASTER TO
  MASTER_HOST = '10.4.14.168',
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'ReplPass@2024',
  MASTER_AUTO_POSITION = 1;

GTID 實現自動定位,故障切換、多級從庫管理更簡單。

安全加固清單

  • 🔒 只允許內網 IP 訪問 3306,安全組/iptables 限制來源
  • 🔒 複製用戶只授 REPLICATION SLAVE 權限,按從庫 IP 段授權
  • 🔒 啟用 SSL 加密複製:MASTER_SSL=1 配合憑證
  • 🔒 定期輪換複製密碼,審計 Binlog 訪問日志
copyright © 萬盛學電腦網 all rights reserved