前言
MySQL 主從複製（Master-Slave Replication）是一種常見的資料庫高可用性架構，透過將主伺服器的資料同步至一個或多個從伺服器，實現資料備份、讀寫分離及故障轉移等功能。本文將詳細說明如何在 Ubuntu 22.04 環境下設定 MySQL 主從複製。
架構概述
主從複製的運作原理如下：
- 主伺服器（Master）：處理所有寫入操作，並將變更記錄於二進位日誌（Binary Log）
- 從伺服器（Slave）：讀取主伺服器的二進位日誌，並重播（Replay）這些變更以保持資料同步
- I/O Thread：從伺服器上的執行緒，負責從主伺服器讀取二進位日誌
- SQL Thread：從伺服器上的執行緒，負責執行讀取到的日誌事件
環境準備
伺服器規劃
| 角色 | 主機名稱 | IP 位址 | MySQL 版本 |
|---|
| Master | mysql-master | 192.168.1.10 | 8.0.x |
| Slave | mysql-slave | 192.168.1.11 | 8.0.x |
安裝 MySQL
在主從伺服器上分別執行以下指令安裝 MySQL：
1
2
3
4
5
6
7
8
9
10
11
12
| # 更新套件清單
sudo apt update
# 安裝 MySQL Server
sudo apt install mysql-server -y
# 啟動並設定開機自動啟動
sudo systemctl start mysql
sudo systemctl enable mysql
# 確認 MySQL 服務狀態
sudo systemctl status mysql
|
主伺服器設定
修改 MySQL 設定檔
編輯主伺服器的 MySQL 設定檔 /etc/mysql/mysql.conf.d/mysqld.cnf：
1
| sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
|
在 [mysqld] 區段中新增或修改以下設定：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| [mysqld]
# 伺服器唯一識別碼，主從伺服器必須不同
server-id = 1
# 啟用二進位日誌
log_bin = /var/log/mysql/mysql-bin.log
# 指定要複製的資料庫（可選，不指定則複製所有資料庫）
binlog_do_db = mydb
# 設定二進位日誌格式
binlog_format = ROW
# 綁定位址，允許遠端連線
bind-address = 0.0.0.0
# 二進位日誌保留天數
expire_logs_days = 7
|
重啟 MySQL 服務以套用設定：
1
| sudo systemctl restart mysql
|
建立複製專用帳號
登入 MySQL 並建立供從伺服器使用的複製帳號：
1
2
3
4
5
6
7
8
| -- 建立複製專用帳號
CREATE USER 'repl_user'@'192.168.1.11' IDENTIFIED WITH mysql_native_password BY 'YourStrongPassword123!';
-- 授予複製權限
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.11';
-- 重新載入權限
FLUSH PRIVILEGES;
|
取得二進位日誌位置
執行以下指令查看主伺服器的二進位日誌位置：
1
2
3
4
5
| -- 鎖定資料表（如有大量資料需要先備份）
FLUSH TABLES WITH READ LOCK;
-- 查看主伺服器狀態
SHOW MASTER STATUS;
|
輸出範例：
1
2
3
4
5
| +------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 856 | mydb | |
+------------------+----------+--------------+------------------+
|
請記下 File 和 Position 的值，稍後設定從伺服器時會用到。
如需備份現有資料：
1
2
| # 在另一個終端機執行備份
sudo mysqldump -u root -p --all-databases --master-data > /tmp/master_backup.sql
|
備份完成後解除鎖定：
從伺服器設定
修改 MySQL 設定檔
編輯從伺服器的 MySQL 設定檔：
1
| sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
|
1
2
3
4
5
6
7
8
9
10
11
12
| [mysqld]
# 伺服器唯一識別碼，必須與主伺服器不同
server-id = 2
# 啟用中繼日誌
relay_log = /var/log/mysql/mysql-relay-bin.log
# 指定要複製的資料庫（需與主伺服器一致）
replicate_do_db = mydb
# 設定唯讀模式（建議）
read_only = 1
|
重啟 MySQL 服務：
1
| sudo systemctl restart mysql
|
匯入主伺服器資料（如有備份）
1
| sudo mysql -u root -p < /tmp/master_backup.sql
|
設定主從複製
登入從伺服器的 MySQL：
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- 停止從伺服器（如已在運行）
STOP SLAVE;
-- 設定主伺服器連線資訊
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl_user',
MASTER_PASSWORD='YourStrongPassword123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=856;
-- 啟動複製程序
START SLAVE;
|
驗證複製狀態
在從伺服器上執行：
確認以下關鍵欄位：
1
2
3
| Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
|
Slave_IO_Running: Yes 表示 I/O 執行緒正常運作Slave_SQL_Running: Yes 表示 SQL 執行緒正常運作Seconds_Behind_Master: 0 表示從伺服器與主伺服器同步
測試複製功能
在主伺服器建立測試資料：
1
2
3
| USE mydb;
CREATE TABLE test_repl (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_repl VALUES (1, 'Hello Replication');
|
在從伺服器確認資料已同步：
1
2
| USE mydb;
SELECT * FROM test_repl;
|
常見問題排解
問題一：Slave_IO_Running 為 No
可能原因：
- 網路連線問題
- 複製帳號權限不足
- 防火牆阻擋 3306 埠
解決方案：
1
2
3
4
5
| # 確認防火牆設定
sudo ufw allow 3306/tcp
# 測試連線
mysql -h 192.168.1.10 -u repl_user -p
|
問題二：Slave_SQL_Running 為 No
可能原因：
解決方案：
1
2
3
4
5
6
7
| -- 查看錯誤訊息
SHOW SLAVE STATUS\G
-- 跳過錯誤（謹慎使用）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
|
問題三：複製延遲過大
解決方案：
1
2
| -- 啟用並行複製
SET GLOBAL slave_parallel_workers = 4;
|
安全性建議
- 使用強密碼並定期更換
- 限制複製帳號僅能從特定 IP 連線
- 考慮啟用 SSL 加密複製連線
- 定期監控複製狀態
參考資料
結語
MySQL 主從複製是建立高可用性資料庫架構的基礎。透過本文的步驟，您已成功設定了基本的主從複製環境。建議在正式環境中加入監控機制，並定期檢查複製狀態，以確保資料的一致性與可用性。