MySQL 資料庫複製入門：主從架構原理、同機不同庫複製、大數據量遷移實戰

MySQL 複制基於二進制日志，實現主庫異步同步至從庫。本文講解複制工作機制（三線程模型）、同機不同資料庫複製命令、大數據量壓縮遷移流程、常見報錯處理。

一、複制工作機制（三線程模型）

1. 主庫 Dump 線程： 讀取 binlog 發送給從庫。
2. 從庫 I/O 線程： 接收 binlog 寫入本地 Relay Log。
3. 從庫 SQL 線程： 讀取 Relay Log 重放事務，更新從庫數據。

二、同機不同資料庫複製（實時同步）

# 在 Terminal 執行（非 MySQL 命令列）
mysqldump Portal_DEV -u root -ppassword1$ --add-drop-table | mysql Portal_Optimize -u root -ppassword1$

• 管道直連，無中間檔案。
• --add-drop-table 確保目標庫表存在時重建。

常見報錯：Got a packet bigger than 'max_allowed_packet'

修改 my.cnf：

[mysqld]
max_allowed_packet = 32M

重啟 MySQL 後重試。

三、大數據量遷移（1GB+）壓縮方案

1. 導出壓縮

mysqldump -u root -p --quick Portal_DEV | gzip > Portal_DEV_$(date +%F).sql.gz

• --quick 流式讀取，避免客戶端 OOM。
• gzip 管道壓縮，節省磁碟與網路傳輸時間。

2. 導入解壓

gunzip < Portal_DEV_2024-01-15.sql.gz | mysql -u root -p Portal_Optimize

四、複制配置要點（主從搭建）

1. 主庫 my.cnf：log_bin=mysql-bin, server-id=1, binlog_format=ROW
2. 從庫 my.cnf：server-id=2, relay_log=relay-bin, read_only=1
3. 建立複制用戶：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'pwd';
4. 從庫執行：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='pwd',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;

五、總結

同機庫間複製用管道直連；大數據量必加 --quick | gzip；生產主從採用 ROW 格式、GTID 模式（MySQL 5.6+）、半同步複制（rpl_semi_sync_master=1）保障數據零丟失。監控 Seconds_Behind_Master=0 確保同步正常。