一主 (Master) 寫入 → Binlog → 從 (Slave) 透過 I/O 執行緒拉取 → Relay Log → SQL 執行緒重放。MySQL 5.6+ 強烈建議啟用 **GTID (Global Transaction ID)** 模式,自動定位同步點,運維更簡單。
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
# 只同步特定庫 (可選)
binlog_do_db = wordpress
binlog_ignore_db = mysql,test,information_schema,performance_schema
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
重啟:systemctl restart mysqld
CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'StrongReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
# 新終端
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > /root/full_backup.sql
# 記錄輸出檔頭的 CHANGE MASTER TO 位點資訊
mysql> UNLOCK TABLES;
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = 1 # 防止誤寫入
super_read_only = 1 # 5.7.8+ 僅允許 SUPER 權限寫入
mysql -u root -p < /root/full_backup.sql
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl_user',
MASTER_PASSWORD='StrongReplPass123!',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1001741,
MASTER_CONNECT_RETRY=10;
mysql> START SLAVE;
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1 # 從庫若做級聯必開
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl_user',
MASTER_PASSWORD='StrongReplPass123!',
MASTER_PORT=3306,
MASTER_AUTO_POSITION = 1; -- 關鍵:自動用 GTID 定位
mysql> START SLAVE;
# 主庫
SHOW MASTER STATUS\G
# 從庫
SHOW SLAVE STATUS\G
-- 關鍵指標:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Retrieved_Gtid_Set / Executed_Gtid_Set (GTID 模式)
| 現象 | 排查方向 |
|---|---|
| IO_Running=Connecting | 網路不通、防火牆、帳號密碼錯、MASTER_HOST 錯、DNS 解析失敗 |
| SQL_Running=No | Last_SQL_Error 顯示具體錯誤 (主鍵衝突、欄位不存在、外鍵失敗);可用 SET GLOBAL sql_slave_skip_counter=1 跳過單條錯誤 (謹慎) |
| Seconds_Behind_Master 持續增長 | 從庫硬體較弱、長事務阻塞、大量 DDL、網路延遲;考慮並行複寫 slave_parallel_workers=4 |
# 兩邊 my.cnf 同步設定
default_time_zone = '+08:00'
# 或系統層面
timedatectl set-timezone Asia/Taipei
時區不一致會導致 NOW()、SYSDATE() 等函數在主從產生不同值,造成資料不一致。