Ubuntu 配置 MySQL 主從複製完整實戰筆記

環境資訊

前置條件

1. 主從兩台資料庫版本一致、字元集一致（建議 utf8mb4）。
2. 主庫已建立 backup 資料庫並有初始資料。
3. 網路互通，防火牆開放 3306。

步驟 1：主庫配置 /etc/my.cnf

[mysqld]
# 啟用二進制日誌（必須）
log_bin = mysql-bin

# 伺服器唯一 ID（1 ~ 2^32-1）
server-id = 1

# 二進制日誌格式：MIXED 推薦（兼容 Statement/Row）
binlog_format = mixed

# 只同步指定資料庫
binlog-do-db = backup

# 忽略系統庫
binlog-ignore-db = mysql

修改後重啟：service mysql restart

步驟 2：主庫建立複製帳號

GRANT REPLICATION SLAVE ON *.* TO 'suxh'@'%' IDENTIFIED BY '111111';
FLUSH PRIVILEGES;

步驟 3：取得主庫二進制日誌位置

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記錄 File (如 mysql-bin.000001) 和 Position (如 107)
UNLOCK TABLES;

步驟 4：備份主庫資料並傳輸到從庫

# 主庫打包（排除 mysql 系統庫、日誌、狀態檔）
tar -czf /tmp/backup.tar.gz /var/lib/mysql/backup

# 傳輸到從庫
scp /tmp/backup.tar.gz 192.168.63.134:/tmp/

# 從庫解壓（需先停止 mysql）
service mysql stop
tar -xzf /tmp/backup.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/backup
service mysql start

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

[mysqld]
server-id = 2
# 從庫 5.1.7+ 不需 master-host 等參數，僅需 server-id
# 如需只讀（推薦）
read_only = 1

重啟：service mysql restart

步驟 6：從庫建立複製連線

CHANGE MASTER TO
  MASTER_HOST='192.168.63.133',
  MASTER_USER='suxh',
  MASTER_PASSWORD='111111',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

START SLAVE;

步驟 7：驗證複製狀態

SHOW SLAVE STATUS\G
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

常見問題排查

Slave_IO_Running: Connecting

檢查網路、防火牆、帳號密碼、主庫 bind-address=0.0.0.0。

Slave_SQL_Running: No

查看 Last_SQL_Error，常見為主鍵衝突、外鍵缺失。可用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; 跳過（謹慎）。

Seconds_Behind_Master 偏大

檢查從庫硬體、長查詢、鎖爭用、網路延遲。考慮並行複製（MySQL 5.6+ slave_parallel_workers）。

進階配置建議

• sync_binlog=1 + innodb_flush_log_at_trx_commit=1：最高資料安全性。
• expire_logs_days=7：自動清理舊 binlog。
• relay_log_purge=1 + relay_log_space_limit：控制中繼日誌空間。
• 監控：pt-heartbeat、Prometheus + mysqld_exporter。

結語

MySQL 主從複製是高可用、讀寫分離、備份卸載的基石。掌握GTID（5.6+）、半同步複製、多源複製（5.7+）等進階特性，能構建更健壯的資料庫架構。