MySQL互為主從(雙向同步)部署超詳細(xì)教程
更新時(shí)間：2025年11月01日 09:59:02 作者：老吳的愛(ài)徒
MySQL互為主從是一種特殊的復(fù)制架構(gòu),其中兩個(gè)MySQL服務(wù)器互為主從,彼此之間互相同步數(shù)據(jù),這篇文章主要介紹了MySQL互為主從(雙向同步)部署的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下
（CentOS 6/7 適用）
一、環(huán)境說(shuō)明
| 服務(wù)器角色 | IP 地址 | 系統(tǒng)版本 | MySQL 版本 | 核心作用 |
|---|---|---|---|---|
| 服務(wù)器 A | 192.168.1.51 | CentOS 6/7 | MySQL 5.7.17 | 作為 B 的主庫(kù) + 作為 B 的從庫(kù) |
| 服務(wù)器 B | 192.168.1.52 | CentOS 6/7 | MySQL 5.7.17 | 作為 A 的主庫(kù) + 作為 A 的從庫(kù) |
目標(biāo)：實(shí)現(xiàn) A、B 雙向數(shù)據(jù)同步，確保兩邊數(shù)據(jù)實(shí)時(shí)一致，支持讀寫(xiě)分離或故障備份。
二、前置準(zhǔn)備（兩臺(tái)服務(wù)器均執(zhí)行）
1. 確認(rèn) MySQL 狀態(tài)
先驗(yàn)證 MySQL 版本一致性和服務(wù)運(yùn)行狀態(tài)，避免版本差異導(dǎo)致同步失敗。
# 1. 檢查MySQL版本（需均為5.7.17） mysql -V # 2. 確認(rèn)MySQL服務(wù)正常運(yùn)行 # CentOS 7 systemctl status mysqld # CentOS 6 service mysqld status
2. 關(guān)閉防火墻（測(cè)試環(huán)境簡(jiǎn)化）
生產(chǎn)環(huán)境可改為開(kāi)放 3306 端口，測(cè)試階段直接關(guān)閉防火墻更高效。
# CentOS 7 systemctl stop firewalld && systemctl disable firewalld # CentOS 6 service iptables stop && chkconfig iptables off
三、配置服務(wù)器 A（192.168.1.51）
1. 修改 MySQL 核心配置文件
編輯my.cnf，僅保留互為主從必需配置，減少冗余。
vim /etc/my.cnf
在[mysqld]區(qū)塊添加以下內(nèi)容：
[mysqld] server-id = 51 # 唯一ID，不可與B重復(fù)（建議用IP后兩位） log_bin = mysql-bin # 開(kāi)啟二進(jìn)制日志（主從同步核心依賴(lài)） log_slave_updates = 1 # 允許同步的數(shù)據(jù)再次生成日志（互為主從關(guān)鍵） auto_increment_increment = 2 # 自增步長(zhǎng)為2，避免主鍵沖突 auto_increment_offset = 1 # A生成奇數(shù)ID（1、3、5...）
2. 重啟 MySQL 使配置生效
# CentOS 7 systemctl restart mysqld # CentOS 6 service mysqld restart
3. 創(chuàng)建同步賬號(hào)（供服務(wù)器 B 連接）
登錄 MySQL，創(chuàng)建用于同步的賬號(hào)repluser，并授予復(fù)制權(quán)限。
# 登錄MySQL（輸入root密碼） mysql -u root -p
執(zhí)行 SQL 命令：
-- 允許服務(wù)器B（192.168.1.52）使用repluser賬號(hào)連接 GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.52' IDENTIFIED BY 'Sync@123'; -- 刷新權(quán)限，確保配置生效 FLUSH PRIVILEGES; -- 查看A的主庫(kù)狀態(tài)（記錄File和Position，后續(xù)配置B需用） SHOW MASTER STATUS;
記錄輸出結(jié)果（示例）：
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 438 | | | +------------------+----------+--------------+------------------+
四、配置服務(wù)器 B（192.168.1.52）
1. 修改 MySQL 核心配置文件
與服務(wù)器 A 類(lèi)似，僅調(diào)整server-id和auto_increment_offset。
vim /etc/my.cnf
在[mysqld]區(qū)塊添加：
[mysqld] server-id = 52 # 唯一ID，與A不同 log_bin = mysql-bin # 開(kāi)啟二進(jìn)制日志 log_slave_updates = 1 # 互為主從關(guān)鍵配置 auto_increment_increment = 2 # 自增步長(zhǎng)與A一致 auto_increment_offset = 2 # B生成偶數(shù)ID（2、4、6...）
2. 重啟 MySQL 服務(wù)
# CentOS 7 systemctl restart mysqld # CentOS 6 service mysqld restart
3. 創(chuàng)建同步賬號(hào)（供服務(wù)器 A 連接）
登錄 MySQL，創(chuàng)建給 A 用的同步賬號(hào)。
mysql -u root -p
執(zhí)行 SQL 命令：
-- 允許服務(wù)器A（192.168.1.51）使用repluser賬號(hào)連接 GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.51' IDENTIFIED BY 'Sync@123'; -- 刷新權(quán)限 FLUSH PRIVILEGES; -- 查看B的主庫(kù)狀態(tài)（記錄File和Position，后續(xù)配置A需用） SHOW MASTER STATUS;
記錄輸出結(jié)果（示例）：
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 438 | | | +------------------+----------+--------------+------------------+
五、配置雙向同步（關(guān)鍵步驟）
1. 服務(wù)器 A 同步服務(wù)器 B 的數(shù)據(jù)（A 作為 B 的從庫(kù)）
登錄服務(wù)器 A 的 MySQL，配置 B 為主庫(kù)信息。
mysql -u root -p
執(zhí)行 SQL（替換為 B 的SHOW MASTER STATUS結(jié)果）：
-- 停止現(xiàn)有從庫(kù)進(jìn)程（若之前配置過(guò)） STOP SLAVE; -- 配置B為主庫(kù) CHANGE MASTER TO MASTER_HOST = '192.168.1.52', # B的IP MASTER_USER = 'repluser', # B上創(chuàng)建的同步賬號(hào) MASTER_PASSWORD = 'Sync@123', # 同步密碼 MASTER_LOG_FILE = 'mysql-bin.000001', # B的File MASTER_LOG_POS = 438; # B的Position -- 啟動(dòng)從庫(kù)同步 START SLAVE;
2. 服務(wù)器 B 同步服務(wù)器 A 的數(shù)據(jù)（B 作為 A 的從庫(kù)）
登錄服務(wù)器 B 的 MySQL，配置 A 為主庫(kù)信息。
mysql -u root -p
執(zhí)行 SQL（替換為 A 的SHOW MASTER STATUS結(jié)果）：
-- 停止現(xiàn)有從庫(kù)進(jìn)程 STOP SLAVE; -- 配置A為主庫(kù) CHANGE MASTER TO MASTER_HOST = '192.168.1.51', # A的IP MASTER_USER = 'repluser', # A上創(chuàng)建的同步賬號(hào) MASTER_PASSWORD = 'Sync@123', # 同步密碼 MASTER_LOG_FILE = 'mysql-bin.000001', # A的File MASTER_LOG_POS = 438; # A的Position -- 啟動(dòng)從庫(kù)同步 START SLAVE;
六、驗(yàn)證同步狀態(tài)
1. 檢查從庫(kù)運(yùn)行狀態(tài)
在 A 和 B 上分別執(zhí)行以下命令，核心驗(yàn)證Slave_IO_Running和Slave_SQL_Running是否均為Yes。
SHOW SLAVE STATUS\G; # \G表示豎屏顯示，更易讀
關(guān)鍵輸出（需均為 Yes）：
Slave_IO_Running: Yes # 與主庫(kù)連接正常 Slave_SQL_Running: Yes # 同步日志執(zhí)行正常
2. 功能測(cè)試（雙向同步驗(yàn)證）
測(cè)試 1：A 寫(xiě)入數(shù)據(jù)，B 驗(yàn)證同步
- 在 A 上創(chuàng)建測(cè)試數(shù)據(jù)：
-- A上執(zhí)行
CREATE DATABASE IF NOT EXISTS sync_test;
USE sync_test;
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, info VARCHAR(50));
INSERT INTO t1 (info) VALUES ('來(lái)自A的測(cè)試數(shù)據(jù)'); # ID應(yīng)為1（奇數(shù)）- 在 B 上查看數(shù)據(jù)是否同步：
-- B上執(zhí)行 USE sync_test; SELECT * FROM t1; # 應(yīng)返回ID=1的記錄
測(cè)試 2：B 寫(xiě)入數(shù)據(jù)，A 驗(yàn)證同步
- 在 B 上插入數(shù)據(jù)：
-- B上執(zhí)行
INSERT INTO sync_test.t1 (info) VALUES ('來(lái)自B的測(cè)試數(shù)據(jù)'); # ID應(yīng)為2（偶數(shù)）- 在 A 上查看數(shù)據(jù)是否同步：
-- A上執(zhí)行 USE sync_test; SELECT * FROM t1; # 應(yīng)返回ID=1和ID=2的兩條記錄
七、常見(jiàn)問(wèn)題排查
Slave_IO_Running: Connecting
- 原因：IP 錯(cuò)誤、同步賬號(hào)密碼錯(cuò)誤、防火墻未關(guān)閉。
- 解決：核對(duì)
CHANGE MASTER TO中的 IP、賬號(hào)、密碼；確保防火墻已關(guān)閉。
Slave_SQL_Running: No
- 原因：主從數(shù)據(jù)不一致、主鍵沖突。
- 解決：確保初始數(shù)據(jù)一致；檢查
auto_increment配置是否正確（A 奇數(shù)、B 偶數(shù)）。
同步延遲
- 原因：數(shù)據(jù)量過(guò)大、服務(wù)器性能不足。
- 解決：優(yōu)化 MySQL 配置（如增大
innodb_buffer_pool_size）；避免大事務(wù)。
八、總結(jié)
- 互為主從的核心是雙向配置主從關(guān)系，A 同步 B、B 同步 A，通過(guò)
log_slave_updates = 1支撐雙向日志傳遞。 - 自增 ID 配置（步長(zhǎng) 2 + 偏移 1/2）是避免主鍵沖突的關(guān)鍵，確保 A、B 生成的 ID 完全不重疊。
- 驗(yàn)證時(shí)優(yōu)先檢查
Slave_IO_Running和Slave_SQL_Running，再通過(guò)實(shí)際數(shù)據(jù)寫(xiě)入測(cè)試同步功能。
總結(jié)
到此這篇關(guān)于MySQL互為主從(雙向同步)部署的文章就介紹到這了,更多相關(guān)MySQL互為主從部署內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了mysql zip 文件安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-04-04
mysql decimal數(shù)據(jù)類(lèi)型轉(zhuǎn)換的實(shí)現(xiàn)
這篇文章主要介紹了mysql decimal數(shù)據(jù)類(lèi)型轉(zhuǎn)換的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
Centos8安裝mysql8的詳細(xì)過(guò)程(免安裝版/或者二進(jìn)制包方式安裝)
這篇文章主要介紹了Centos8安裝mysql8的詳細(xì)過(guò)程(免安裝版/或者二進(jìn)制包方式安裝),使用二進(jìn)制包方式安裝首先檢查服務(wù)器上是否安裝有mysql然后開(kāi)始安裝配置，本文分步驟給大家講解的非常詳細(xì)，需要的朋友可以參考下2022-11-11
MySQL?Buffer?Pool如何提高頁(yè)的訪(fǎng)問(wèn)速度
本文主要介紹了MySQL?Buffer?Pool如何提高頁(yè)的訪(fǎng)問(wèn)速度，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
淺談mysql數(shù)據(jù)庫(kù)中的換行符與textarea中的換行符
下面小編就為大家?guī)?lái)一篇淺談mysql數(shù)據(jù)庫(kù)中的換行符與textarea中的換行符。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-01-01
MySQL 無(wú)監(jiān)聽(tīng)端口故障問(wèn)題排查記錄
文章解析MySQL運(yùn)維中因skip-grant-tables參數(shù)隱性啟用skip-networking導(dǎo)致端口未監(jiān)聽(tīng)的故障,指導(dǎo)如何排查及恢復(fù)網(wǎng)絡(luò)連接,強(qiáng)調(diào)參數(shù)關(guān)聯(lián)與安全機(jī)制,本文給大家介紹MySQL 無(wú)監(jiān)聽(tīng)端口故障問(wèn)題排查記錄,感興趣的朋友一起看看吧2025-07-07
MySQL更改默認(rèn)字符集為utf-8的全過(guò)程
這篇文章主要介紹了MySQL更改默認(rèn)字符集為utf-8的全過(guò)程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
這篇文章給大家介紹了MySql 8.0.11安裝配置教程，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，感興趣的朋友一起看看吧2018-05-05
prometheus監(jiān)控MySQL并實(shí)現(xiàn)可視化的操作詳解
mysqld_exporter?是一個(gè)用于監(jiān)控?MySQL?服務(wù)器的開(kāi)源工具,它是由?Prometheus?社區(qū)維護(hù)的一個(gè)官方?Exporter,本文給大家介紹了prometheus監(jiān)控MySQL并實(shí)現(xiàn)可視化的操作,文中通過(guò)代碼和圖文講解的非常詳細(xì),需要的朋友可以參考下2024-04-04
在SQL語(yǔ)句中,可以為表名稱(chēng)及字段(列)名稱(chēng)指定別名(Alias),別名是SQL標(biāo)準(zhǔn)語(yǔ)法,幾乎所有的數(shù)據(jù)庫(kù)系統(tǒng)都支持,下面這篇文章主要給大家介紹了關(guān)于MySQL如何為表和字段取別名的相關(guān)資料,需要的朋友可以參考下2022-11-11