FreeBSD 上 MySQL 主從資料同步部署實戰：單向複寫配置全紀錄

環境規劃

架構：單向複寫，Master 為寫入來源，Slave 主動拉取同步。

1. 安裝與基礎配置

# 兩台同步執行
cd /usr/ports/databases/mysql41-server
make install clean

# 複製中型配置範本
cp /usr/local/mysql/share/mysql/my-medium.cnf /usr/local/mysql/var/my.cnf

2. Master 設定 /usr/local/mysql/var/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = abc  # 只同步 abc 資料庫
# sql-bin-update-same  # 註：此參數非標準，建議移除

重啟：/usr/local/mysql/bin/mysqladmin shutdown && /usr/local/mysql/bin/mysqld_safe --user=mysql &

3. 建立複寫帳號（在 Master）

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2' IDENTIFIED BY 'slavepass';
FLUSH PRIVILEGES;

4. Slave 設定 /usr/local/mysql/var/my.cnf

[mysqld]
server-id = 2
master-host = 192.168.0.1
master-user = repl
master-password = slavepass
master-port = 3306
replicate-do-db = abc
# 可選：relay-log 相關參數

5. 初始資料同步

1. Master：FLUSH TABLES WITH READ LOCK; → SHOW MASTER STATUS; 記下 File/Position。
2. 備份 Master 資料（mysqldump --master-data=1 abc > abc.sql 或打包 datadir）。
3. 傳輸到 Slave 還原：mysql abc < abc.sql。
4. Master：UNLOCK TABLES;。

6. 啟動 Slave 複寫

/usr/local/mysql/bin/mysqladmin shutdown
/usr/local/mysql/bin/mysqld_safe --user=mysql &

mysql -u root -p
CHANGE MASTER TO
  MASTER_HOST='192.168.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='slavepass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS\G

驗證同步

-- Master 執行
CREATE TABLE test_sync (id INT PRIMARY KEY, val VARCHAR(20));
INSERT INTO test_sync VALUES (1, 'hello');

-- Slave 查詢
SELECT * FROM abc.test_sync;
-- 應立即看到資料

FreeBSD 特有注意事項

• 啟動腳本：/usr/local/etc/rc.d/mysql-server.sh，加入 mysql_enable="YES" 至 /etc/rc.conf。
• 防火牆 ipfw/pf 需開放 3306 兩向通訊。
• 檔案系統 UFS2 + Soft Updates，確保 fsync 行為正確。
• 定期 mysqlcheck -o abc 優化表。

常見問題

• Slave_IO_Running: Connecting：檢查網路、防火牆、帳號密碼、Master bind-address。
• Slave_SQL_Running: No：查 Last_SQL_Error，常見重複鍵、外鍵、字元集不符。
• 不同步特定資料庫：確認 binlog-do-db/replicate-do-db 設定一致，且無跨資料庫查詢。