mysql 主從配置2025-08-28

?? 架構(gòu)說明

Master（主庫） → 寫操作
   ↓
Slave1（從庫1） ← 同步數(shù)據(jù)，只讀
Slave2（從庫2） ← 同步數(shù)據(jù)，只讀

? 所有從庫自動(dòng)從主庫同步 binlog，實(shí)現(xiàn)數(shù)據(jù)一致性。

?? 環(huán)境準(zhǔn)備

角色 IP 地址 server-id
Master 192.168.1.10 1
Slave1 192.168.1.11 2
Slave2 192.168.1.12 3

?? 所有服務(wù)器需能互相通信，防火墻開放 3306 端口。

? 一、配置 Master（主庫）

1. 編輯 MySQL 配置文件

# 文件路徑：/etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]

# 唯一標(biāo)識(shí)，主庫為 1
server-id = 1

# 開啟二進(jìn)制日志（binlog），用于主從復(fù)制
log-bin = mysql-bin

# 設(shè)置 binlog 格式為 ROW（推薦，更安全）
# 可選：STATEMENT, ROW, MIXED
binlog-format = ROW

# 設(shè)置 binlog 過期時(shí)間（單位：天）
expire_logs_days = 7

# 單個(gè) binlog 文件最大大小
max_binlog_size = 100M

# 每次事務(wù)提交都寫入磁盤（保證數(shù)據(jù)不丟失）
sync-binlog = 1

# InnoDB 日志同步策略（與 sync-binlog 配合使用）
innodb_flush_log_at_trx_commit = 1

# 可選：限制只允許特定網(wǎng)段的從庫連接
# bind-address = 192.168.1.10

2. 重啟 MySQL 服務(wù)

systemctl restart mysql

3. 登錄 MySQL，創(chuàng)建復(fù)制用戶

-- 創(chuàng)建一個(gè)專用于主從復(fù)制的用戶
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl123';

-- 授予復(fù)制權(quán)限（允許從庫連接并讀取 binlog）
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

?? 生產(chǎn)建議：使用強(qiáng)密碼，限制 IP 范圍。

4. 查看主庫狀態(tài)（記錄 binlog 位置）

SHOW MASTER STATUS;

記下輸出中的兩個(gè)關(guān)鍵值（稍后從庫配置需要）：

File: mysql-bin.000001
Position: 154

? 這兩個(gè)值表示當(dāng)前 binlog 的文件名和位置，從庫將從這里開始同步。

? 二、配置 Slave1（從庫1）

1. 編輯 MySQL 配置文件

# 文件路徑：/etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]

# 唯一標(biāo)識(shí)，不能與主庫和其他從庫重復(fù)
server-id = 2

# 開啟中繼日志（relay log），用于存儲(chǔ)從主庫讀取的 binlog）
relay-log = mysql-relay-bin

# 從庫只讀，防止誤寫（非常重要）
read-only = 1

# 允許從庫更新其自身的 binlog（用于級(jí)聯(lián)復(fù)制，可選）
# log-slave-updates = 1

# 可選：限制只允許管理員寫入（即使 read-only=0 時(shí)）
# super-read-only = 1

2. 重啟 MySQL 服務(wù)

systemctl restart mysql

3. 配置從庫連接主庫

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',          -- 主庫 IP
  MASTER_USER='repl',                  -- 復(fù)制用戶名
  MASTER_PASSWORD='repl123',           -- 復(fù)制用戶密碼
  MASTER_LOG_FILE='mysql-bin.000001',  -- 主庫 binlog 文件名（來自 SHOW MASTER STATUS）
  MASTER_LOG_POS=154;                  -- 主庫 binlog 位置（來自 SHOW MASTER STATUS）

-- 啟動(dòng)從庫復(fù)制線程
START SLAVE;

4. 查看從庫狀態(tài)

SHOW SLAVE STATUS\G

? 確保以下兩項(xiàng)為 Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

?? 如果不是 Yes，查看 Last_Error 字段排查問題。

? 三、配置 Slave2（從庫2）

配置與 Slave1 幾乎完全相同，只需改 server-id。

1. 編輯 MySQL 配置文件

[mysqld]
server-id = 3
relay-log = mysql-relay-bin
read-only = 1

2. 重啟 MySQL

systemctl restart mysql

3. 配置連接主庫（與 Slave1 相同）

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

4. 檢查狀態(tài)

SHOW SLAVE STATUS\G

? 確保：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

? 四、驗(yàn)證主從復(fù)制

1. 在 Master 上創(chuàng)建測試數(shù)據(jù)

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');

2. 在 Slave1 和 Slave2 上查看

SHOW DATABASES LIKE 'test_replication';
USE test_replication;
SELECT * FROM users;

? 如果能看到數(shù)據(jù)，說明復(fù)制成功！

? 五、常見問題排查

問題 解決方法
Slave_IO_Running: No 檢查網(wǎng)絡(luò)、用戶名密碼、主庫 binlog 是否開啟
Slave_SQL_Running: No 檢查是否有主鍵沖突、表結(jié)構(gòu)不一致
從庫延遲高 檢查主庫寫壓力、從庫性能、網(wǎng)絡(luò)帶寬
ERROR 1201: Failed to initialize slave 確保 server-id 唯一

? 六、生產(chǎn)建議

項(xiàng)目 建議
? 定期備份 在從庫上做 mysqldump 或 xtrabackup
? 監(jiān)控延遲 使用 Seconds_Behind_Master 或 Prometheus + Grafana
? 讀寫分離 應(yīng)用層將讀請(qǐng)求路由到從庫
? 高可用 結(jié)合 MHA / Orchestrator 實(shí)現(xiàn)自動(dòng)切換
? 安全 使用 SSL 加密主從通信（可選）


以下是 整合了所有性能、安全、高可用優(yōu)化項(xiàng)的 MySQL 一主兩從完整配置，包含：

  • 基礎(chǔ)主從復(fù)制
  • 半同步復(fù)制（Semi-Sync）
  • GTID 復(fù)制
  • 并行復(fù)制（MTS）
  • 安全參數(shù)
  • 性能調(diào)優(yōu)
  • 詳細(xì)中文注釋

適用于 MySQL 5.7 / 8.0，可直接用于生產(chǎn)環(huán)境。

? 最終目標(biāo)架構(gòu)

Master（主庫） → 寫操作
   ↓
Slave1（從庫1） ← 同步數(shù)據(jù)，只讀
Slave2（從庫2） ← 同步數(shù)據(jù)，只讀

?? 一、Master 主庫配置（/etc/my.cnf）

[mysqld]
# ========================
# 基本設(shè)置
# ========================
# 唯一標(biāo)識(shí)，主庫設(shè)為 1
server-id = 1

# 開啟二進(jìn)制日志（主從復(fù)制的基礎(chǔ)）
log-bin = mysql-bin

# 設(shè)置 binlog 格式為 ROW（推薦，更安全，避免復(fù)制異常）
binlog-format = ROW

# binlog 過期時(shí)間（單位：天）
expire_logs_days = 7

# 單個(gè) binlog 文件最大大小
max_binlog_size = 100M


# ========================
# 數(shù)據(jù)安全與持久性
# ========================
# 每次事務(wù)提交都同步 binlog 到磁盤（防止主庫宕機(jī)丟失數(shù)據(jù)）
sync-binlog = 1

# InnoDB 日志同步策略，與 sync-binlog 配合使用
# 值為 1：最安全（每次提交都刷日志），性能略低
innodb_flush_log_at_trx_commit = 1


# ========================
# GTID 復(fù)制（推薦）
# ========================
# 啟用 GTID（全局事務(wù) ID），便于故障切換和復(fù)制管理
gtid-mode = ON

# 強(qiáng)制 GTID 一致性（必須開啟）
enforce-gtid-consistency = ON


# ========================
# 半同步復(fù)制（Semi-Sync）
# ========================
# 安裝插件后啟用，確保至少一個(gè)從庫收到日志
# 防止主庫宕機(jī)時(shí)數(shù)據(jù)未同步導(dǎo)致丟失
rpl_semi_sync_master_enabled = 1

# 等待從庫確認(rèn)的超時(shí)時(shí)間（毫秒），超時(shí)后退化為異步復(fù)制
rpl_semi_sync_master_timeout = 10000  # 10秒


# ========================
# 性能優(yōu)化
# ========================
# 批量提交優(yōu)化：延遲微秒，等待多個(gè)事務(wù)一起提交，減少 I/O
binlog_group_commit_sync_delay = 10
binlog_group_commit_sync_no_delay_count = 10


# ========================
# 安全設(shè)置（可選）
# ========================
# 限制只允許特定 IP 連接（如從庫 IP 段）
# bind-address = 192.168.1.10

# 可選：啟用 SSL（需配置證書）
# ssl-ca = /path/to/ca.pem
# ssl-cert = /path/to/server-cert.pem
# ssl-key = /path/to/server-key.pem

? 修改后重啟 MySQL：systemctl restart mysql

?? 二、Slave1 和 Slave2 通用配置（/etc/my.cnf）

兩臺(tái)從庫配置相同，僅 server-id 不同（Slave1=2，Slave2=3）

[mysqld]
# ========================
# 基本設(shè)置
# ========================
# 唯一標(biāo)識(shí)，不能與主庫和其他從庫重復(fù)
server-id = 2    # Slave2 改為 3

# 開啟中繼日志（存儲(chǔ)從主庫讀取的 binlog）
relay-log = mysql-relay-bin

# 從庫只讀，防止誤寫（非常重要）
read-only = 1

# 超級(jí)用戶也只讀（進(jìn)一步防止誤操作）
super-read-only = 1


# ========================
# GTID 復(fù)制
# ========================
# 啟用 GTID
gtid-mode = ON
enforce-gtid-consistency = ON


# ========================
# 并行復(fù)制（Multi-Threaded Slave, MTS）
# ========================
# 使用邏輯時(shí)鐘并行復(fù)制（基于事務(wù)組，效率高）
slave-parallel-type = LOGICAL_CLOCK

# 設(shè)置并行工作線程數(shù)（建議 4~8，根據(jù) CPU 核數(shù)調(diào)整）
slave-parallel-workers = 4

# 將主從復(fù)制信息存儲(chǔ)在表中（更安全）
master-info_repository = TABLE
relay-log-info_repository = TABLE

# 啟用 relay log 自動(dòng)恢復(fù)（防止崩潰后出錯(cuò)）
relay_log_recovery = ON


# ========================
# 半同步復(fù)制（從庫端）
# ========================
# 啟用半同步從庫插件
rpl_semi_sync_slave_enabled = 1


# ========================
# 安全設(shè)置
# ========================
# 可選：啟用 SSL（需證書）
# ssl-ca = /path/to/ca.pem
# ssl-cert = /path/to/client-cert.pem
# ssl-key = /path/to/client-key.pem

? 修改后重啟 MySQL：systemctl restart mysql

?? 三、主庫創(chuàng)建復(fù)制用戶（主庫執(zhí)行）

-- 創(chuàng)建專用復(fù)制用戶，限制 IP 范圍
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

-- 授予復(fù)制權(quán)限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

?? 四、從庫配置復(fù)制（Slave1 和 Slave2 執(zhí)行）

-- 停止舊的復(fù)制（如有）
STOP SLAVE;

-- 配置復(fù)制（使用 GTID，無需指定 File 和 Position）
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',           -- 主庫 IP
  MASTER_USER = 'repl',                   -- 復(fù)制用戶名
  MASTER_PASSWORD = 'StrongPass123!',     -- 密碼
  MASTER_AUTO_POSITION = 1,               -- 啟用 GTID 自動(dòng)定位
  MASTER_SSL = 0;                         -- 如果啟用 SSL，改為 1 并配置證書

-- 啟動(dòng)復(fù)制
START SLAVE;

? 五、驗(yàn)證復(fù)制狀態(tài)（從庫執(zhí)行）

SHOW SLAVE STATUS\G

? 確保以下關(guān)鍵項(xiàng)：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0        -- 延遲為 0 表示同步正常
Retrieved_Gtid_Set: ...         -- 有 GTID 記錄
Executed_Gtid_Set: ...          -- 已執(zhí)行 GTID

?? 如果 Slave_IO_Running 為 Connecting，檢查網(wǎng)絡(luò)、用戶權(quán)限、防火墻。

?? 六、啟用半同步插件（首次需手動(dòng)執(zhí)行）

主庫執(zhí)行：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 已在配置中啟用，重啟后自動(dòng)加載

從庫執(zhí)行：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 重啟 IO 線程使生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

? 七、總結(jié)：這套配置的優(yōu)勢

優(yōu)化項(xiàng) 效果
? GTID 復(fù)制更智能，切換更簡單
? 半同步 防止數(shù)據(jù)丟失
? 并行復(fù)制 提升從庫同步速度
? read-only 防止從庫誤寫
? sync-binlog + innodb_flush 保證數(shù)據(jù)持久性
? relay_log_recovery 防止 relay log 損壞
? master-info_repository = TABLE 更安全的元數(shù)據(jù)存儲(chǔ)

?? 后續(xù)建議

  1. 部署 ProxySQL 實(shí)現(xiàn)讀寫分離
  2. 使用 MHA / Orchestrator 實(shí)現(xiàn)自動(dòng)故障轉(zhuǎn)移
  3. 在 Slave 上做備份（如 xtrabackup）
  4. 監(jiān)控系統(tǒng)：Prometheus + Grafana + mysqld_exporter

如果你需要我：

  • 生成 自動(dòng)化部署腳本（Shell/Ansible）
  • 提供 MHA 配置模板
  • 提供 ProxySQL 讀寫分離配置
  • 打包為 Docker Compose 方案

歡迎告訴我，我可以為你定制完整的 MySQL 高可用解決方案 ??

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容