優勢:非同步、解耦、可多從庫、支援異地災備。
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
# 可選:只同步特定庫
# binlog_do_db = biz_db
systemctl restart mysqld
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass#2024';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
-- 方法 A:mysqldump (小資料量、可接受短暫鎖表)
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; -- 記錄 File, Position
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > master_full.sql
UNLOCK TABLES;
-- 方法 B:xtrabackup (大資料量、完全不鎖表,推薦)
innobackupex --user=root --password=xxx --slave-info /backup/
[mysqld]
server-id = 2
relay_log = relay-bin
log_slave_updates = 1 # 若此 Slave 還要做 Master
read_only = 1
skip_slave_start = 1 # 手動啟動,便於檢查
systemctl restart mysqld
mysql -u root -p < master_full.sql
# 或 xtrabackup 恢復後 apply-log
CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'StrongPass#2024',
MASTER_LOG_FILE = 'mysql-bin.000003',
MASTER_LOG_POS = 73,
MASTER_CONNECT_RETRY = 30;
START SLAVE;
SHOW SLAVE STATUS\G
-- 關鍵欄位:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error / Last_SQL_Error: (空白)
| 現象 | 可能原因 | 處理 |
|---|---|---|
| IO_Running=Connecting | 網路不通/帳號密碼錯/防火牆 | telnet master 3306、檢查 grants、error log |
| SQL_Running=No | SQL 執行錯誤 (主鍵衝突、欄位缺失) | 查 Last_SQL_Error,修正資料後 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; |
| Seconds_Behind_Master 偏大 | Slave 機能不足/大事務/長查詢阻塞 | 升級 Slave 規格、優化慢查詢、並行複制 (slave_parallel_workers) |
GTID_MODE=ON,自動定位位置,故障切換更簡單。slave_parallel_workers=4 + slave_parallel_type=LOGICAL_CLOCK。rpl_semi_sync_master=ON,確保至少一個 Slave 收到 Binlog 再回應 Commit,降低資料遺失風險。Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running。