MySQL 基於日誌點的主從復制完整配置指南

MySQL 基於日誌點的主從復制完整配置指南

MySQL 復制機制主要分為基於日誌點的傳統復制與基於 GTID 的全域事務識別符復制兩大類。本文將詳細解析基於日誌點復制的完整部署流程，涵蓋從帳號建立、參數配置、數據初始化到驗證測試的各個關鍵步驟。

一、建立復制專用賬號

在主資料庫伺服器上建立具備 REPLICATION SLAVE 權限的專用帳號，建議限制來源 IP 段以增強安全性：

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

二、主資料庫伺服器配置

編輯 my.cnf（或 Windows 下的 my.ini），在 [mysqld] 區段加入以下核心參數：

[mysqld]
log-bin=mysql-bin          # 啟用二進制日誌並指定基礎名稱
server_id=100              # 集群內唯一標識，範圍 1~2³²-1

修改後重啟 MySQL 服務使配置生效。

三、從資料庫伺服器配置

[mysqld]
log-bin=mysql-bin
server_id=101
relay_log=mysql-relay-bin   # 中繼日誌名稱
log_slave_updates=ON        # 若此從庫需作為下一級主庫，必須開啟
read_only=ON                # 防止誤寫入，保護數據一致性

四、從庫數據初始化

提供兩種主流方案，根據業務停機容忍度選擇：

方案 A：mysqldump 邏輯備份（需加鎖，適合較小數據量）

mysqldump -uroot -p -P3308 \
  --single-transaction \
  --master-data=2 \
  --triggers --routines \
  --all-databases > all.sql

• --single-transaction：InnoDB 表確保一致性視圖，不阻塞寫入
• --master-data=2：在導出文件中以註釋形式記錄主庫當前 Binlog 文件名與位置

方案 B：xtrabackup 物理熱備（不阻塞，適合大數據量）

xtrabackup --backup --slave-info --target-dir=/backup/full

備份完成後，將文件傳輸至從庫並還原：

mysql -uroot -p -P3309 < all.sql   # 邏輯備份還原
# 或使用 xtrabackup --copy-back 還原物理備份

五、啟動復制鏈路

在從庫執行 CHANGE MASTER TO 指定主庫連接資訊與同步起點：

CHANGE MASTER TO
  MASTER_HOST='master_host_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl',
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=2162;

關鍵提示：MASTER_LOG_FILE 與 MASTER_LOG_POS 可從導出的 all.sql 文件頭部註釋中獲取（如圖所示）。

啟動復制線程並檢查狀態：

START SLAVE;
SHOW SLAVE STATUS\G

重點關注 Slave_IO_Running 與 Slave_SQL_Running 均為 Yes，且 Seconds_Behind_Master 為 0 或較小值。

查看線程狀態確認復制架構：

SHOW PROCESSLIST;

• 主庫：顯示 Binlog Dump 線程，負責向從庫推送日誌
• 從庫：包含 Slave_IO（接收日誌寫入中繼日誌）與 Slave_SQL（重放中繼日誌）兩個線程

CREATE TABLE test_repl (id INT PRIMARY KEY, val VARCHAR(20));
INSERT INTO test_repl VALUES (1, 'replication_test'), (2, 'binlog_pos');

SELECT * FROM test_repl;