MySqll線上主從集群設置詳細方案
更新時間：2025年10月30日 09:34:30 作者：不見長安在
本文詳細介紹了如何在生產(chǎn)環(huán)境中搭建MySQL主從復制架構(gòu),包括環(huán)境準備、配置步驟、驗證及運維注意事項,,此外,還特別介紹了如何在不停服的情況下搭建主從復制,確保業(yè)務連續(xù)性,感興趣的朋友跟隨小編一起看看吧
以下是線上搭建 MySQL 主從復制（Master-Slave）的詳細方案，適用于生產(chǎn)環(huán)境，涵蓋環(huán)境準備、配置步驟、驗證及運維注意事項：
一、環(huán)境準備
1. 服務器規(guī)劃
| 角色 | IP地址 | 操作系統(tǒng) | MySQL版本 | 備注 |
|---|---|---|---|---|
| Master | 192.168.1.10 | CentOS 7 | 8.0.36 | 主庫，業(yè)務寫入節(jié)點 |
| Slave | 192.168.1.11 | CentOS 7 | 8.0.36 | 從庫，只讀，用于備份/查詢 |
2. 前置條件
- 主從服務器 MySQL 版本一致（避免因版本差異導致的復制兼容性問題）。
- 主從服務器時間同步（建議通過 NTP 服務，
ntpd或chronyd）。 - 主庫開啟
binlog日志（主從復制依賴 binlog）。 - 主從服務器網(wǎng)絡互通，Master 需開放 3306 端口（或自定義端口），Slave 能訪問 Master 的該端口。
- 關閉或配置防火墻（
firewalld或iptables），允許主從通信：
# Master 開放 3306 端口 firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload
二、Master 主庫配置
1. 修改 MySQL 配置文件（my.cnf或mysqld.cnf）
配置文件路徑通常為 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，添加以下參數(shù)：
[mysqld] # 開啟 binlog，指定日志文件前綴 log_bin = /var/lib/mysql/mysql-bin # 服務器唯一 ID（1-255，主從不能重復） server-id = 10 # binlog 格式（推薦 row 模式，記錄數(shù)據(jù)行變更，兼容性好） binlog_format = ROW # 不需要同步的數(shù)據(jù)庫（可選，多個用逗號分隔） binlog-ignore-db = information_schema binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = sys # 自動清理 7 天前的 binlog（避免磁盤占滿） expire_logs_days = 7 # 同步時忽略主從的服務器 ID 沖突（可選） log-slave-updates = 0
2. 重啟 MySQL 服務
systemctl restart mysqld # 確認狀態(tài) systemctl status mysqld
3. 主庫創(chuàng)建用于復制的賬號
登錄 Master MySQL，創(chuàng)建一個僅用于從庫復制的賬號（限制 IP 為 Slave 地址，增強安全性）：
-- 登錄主庫 mysql -u root -p -- 創(chuàng)建復制賬號（用戶名：repl，密碼：Repl@123456，允許從 192.168.1.11 連接） CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'Repl@123456'; -- 授予復制權限 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11'; -- 刷新權限 FLUSH PRIVILEGES;
4. 鎖定主庫并獲取 binlog 信息
為避免配置期間主庫數(shù)據(jù)變更，先鎖定主庫（僅禁止寫入，允許讀?。?/p>
-- 鎖定主庫 FLUSH TABLES WITH READ LOCK; -- 查看當前 binlog 文件名和位置（記錄 File 和 Position，后續(xù)從庫配置需要） SHOW MASTER STATUS;
輸出示例：
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | information_schema,mysql,performance_schema,sys | | +------------------+----------+--------------+------------------+-------------------+
記錄 File（如 mysql-bin.000001）和 Position（如 154）。
三、Slave 從庫配置
1. 備份主庫數(shù)據(jù)并恢復到從庫
主庫鎖定后，通過 mysqldump 備份數(shù)據(jù)（確保從庫初始數(shù)據(jù)與主庫一致）：
# 在 Master 服務器執(zhí)行，備份所有庫（排除忽略的系統(tǒng)庫） mysqldump -u root -p --all-databases --ignore-db=information_schema --ignore-db=mysql --ignore-db=performance_schema --ignore-db=sys > master_data.sql
將備份文件傳到 Slave 服務器：
scp master_data.sql root@192.168.1.11:/tmp/
在 Slave 服務器恢復數(shù)據(jù)：
# 登錄 Slave MySQL，先創(chuàng)建與主庫一致的數(shù)據(jù)庫（如果有自定義庫） mysql -u root -p CREATE DATABASE IF NOT EXISTS your_db; exit # 恢復數(shù)據(jù) mysql -u root -p < /tmp/master_data.sql
恢復完成后，解鎖主庫（在 Master MySQL 中執(zhí)行）：
UNLOCK TABLES;
2. 修改 Slave 配置文件（my.cnf）
[mysqld] # 從庫唯一 ID（與主庫不同） server-id = 11 # 從庫日志（可選，用于級聯(lián)復制，即從庫作為其他庫的主庫時需要） relay_log = /var/lib/mysql/mysql-relay-bin # 從庫只讀（禁止寫入，增強數(shù)據(jù)一致性） read_only = 1 # 忽略同步的數(shù)據(jù)庫（與主庫保持一致） replicate-ignore-db = information_schema replicate-ignore-db = mysql replicate-ignore-db = performance_schema replicate-ignore-db = sys
3. 重啟 Slave MySQL 服務
systemctl restart mysqld systemctl status mysqld
4. 配置從庫連接主庫
登錄 Slave MySQL，配置主從復制參數(shù)（使用主庫的 binlog 信息和復制賬號）：
-- 登錄從庫 mysql -u root -p -- 停止從庫復制進程（如果之前配置過） STOP SLAVE; -- 配置主庫信息 CHANGE MASTER TO MASTER_HOST = '192.168.1.10', -- 主庫 IP MASTER_USER = 'repl', -- 復制賬號用戶名 MASTER_PASSWORD = 'Repl@123456', -- 復制賬號密碼 MASTER_LOG_FILE = 'mysql-bin.000001', -- 主庫 binlog 文件名（從 SHOW MASTER STATUS 獲?。? MASTER_LOG_POS = 154; -- 主庫 binlog 位置（從 SHOW MASTER STATUS 獲取） -- 啟動從庫復制進程 START SLAVE;
四、驗證主從復制
1. 查看從庫狀態(tài)
在 Slave MySQL 中執(zhí)行：
SHOW SLAVE STATUS\G
重點關注以下兩個參數(shù)，均為 Yes 表示復制正常：
Slave_IO_Running: Yes（從庫 I/O 線程正常，負責讀取主庫 binlog 到本地 relay log）Slave_SQL_Running: Yes（從庫 SQL 線程正常，負責執(zhí)行 relay log 中的 SQL）
2. 數(shù)據(jù)同步測試
在 Master 中創(chuàng)建測試表并插入數(shù)據(jù)：
CREATE DATABASE IF NOT EXISTS test_db; USE test_db; CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(20)); INSERT INTO test_table VALUES (1, 'test');
在 Slave 中查詢，確認數(shù)據(jù)已同步：
USE test_db; SELECT * FROM test_table; -- 若輸出 (1, 'test')，則復制成功
五、運維注意事項
- binlog 管理：
- 主庫定期清理 binlog（通過
expire_logs_days或手動PURGE BINARY LOGS），避免磁盤占滿。 - 從庫延遲時，不要輕易刪除主庫未同步的 binlog。
- 主庫定期清理 binlog（通過
- 從庫延遲監(jiān)控：
- 關注
SHOW SLAVE STATUS中的Seconds_Behind_Master（延遲秒數(shù)），正常應為 0。 - 可通過 Prometheus + Grafana 監(jiān)控主從延遲，設置告警閾值（如延遲 > 30 秒）。
- 關注
- 主從切換：
- 若主庫故障，需手動將從庫提升為主庫：
-- 在 Slave 執(zhí)行 STOP SLAVE; RESET SLAVE ALL; -- 清除主從配置 -- 從庫設置為可寫（如需） SET GLOBAL read_only = 0;
- 安全加固：
- 復制賬號限制 IP（如
repl@192.168.1.11），避免全網(wǎng)訪問。 - 定期更換復制賬號密碼。
- 主從庫開啟 SSL 加密復制（可選，增強傳輸安全性）。
- 復制賬號限制 IP（如
- 版本升級：
- 升級時先升級從庫，再升級主庫（避免主庫版本高于從庫導致的兼容性問題）。
通過以上步驟，可搭建一套穩(wěn)定的 MySQL 主從復制架構(gòu)，實現(xiàn)數(shù)據(jù)備份和讀寫分離基礎。生產(chǎn)環(huán)境中建議結(jié)合監(jiān)控工具（如 Percona Monitoring and Management）實時跟蹤復制狀態(tài)。
在生產(chǎn)環(huán)境中，為了避免業(yè)務中斷，通常需要在不停服的情況下搭建 MySQL 主從復制。核心思路是：通過在線備份主庫數(shù)據(jù)（不鎖表或僅短暫鎖表），確保從庫初始化數(shù)據(jù)與主庫一致，同時記錄主庫的 binlog 位置，最終實現(xiàn)主從同步。以下是詳細步驟：
mysql不停服主從復制
一、環(huán)境準備（同前，補充不停服關鍵點）
| 角色 | IP地址 | 操作系統(tǒng) | MySQL版本 | 備注 |
|---|---|---|---|---|
| Master | 192.168.1.10 | CentOS 7 | 8.0.36 | 主庫，業(yè)務正常運行 |
| Slave | 192.168.1.11 | CentOS 7 | 8.0.36 | 從庫，全新安裝或待同步 |
關鍵前提：
- 主庫已開啟
binlog（若未開啟，需臨時開啟，但會重啟 MySQL，建議提前規(guī)劃）。 - 主從服務器時間同步（
chronyd或ntpd），網(wǎng)絡互通（3306 端口開放）。 - 確保主庫有足夠磁盤空間用于備份（
mysqldump或xtrabackup備份文件）。
二、Master 主庫配置（不停服核心步驟）
1. 確認主庫 binlog 已開啟
登錄主庫，檢查 binlog 狀態(tài)：
mysql -u root -p SHOW VARIABLES LIKE 'log_bin'; -- 若 Value 為 ON，說明已開啟 SHOW VARIABLES LIKE 'server_id'; -- 確保 server_id 已設置（非 0）
若未開啟 binlog，需修改 my.cnf 并重啟主庫（會短暫停服，建議避開業(yè)務高峰）：
[mysqld] log_bin = /var/lib/mysql/mysql-bin server-id = 10 # 主庫唯一 ID binlog_format = ROW # 推薦 row 模式
重啟后再次確認 binlog 已開啟。
2. 創(chuàng)建復制賬號（不停服）
在主庫創(chuàng)建用于從庫復制的賬號（限制從庫 IP，避免安全風險）：
-- 登錄主庫 mysql -u root -p -- 創(chuàng)建賬號（用戶名 repl，允許從 192.168.1.11 連接） CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'Repl@123456'; -- 授予復制權限 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11'; -- 刷新權限 FLUSH PRIVILEGES;
3. 在線備份主庫數(shù)據(jù)（核心：不鎖表或短暫鎖表）
為避免鎖表影響業(yè)務，推薦使用 Percona XtraBackup（支持熱備份，不鎖表），或 mysqldump 加 --single-transaction 參數(shù)（適用于 InnoDB 引擎，非阻塞備份）。
方案 1：使用 Percona XtraBackup（推薦，適合大庫）
XtraBackup 是開源工具，支持 InnoDB 熱備份，備份過程不鎖表，適合生產(chǎn)環(huán)境。
安裝 XtraBackup（主庫執(zhí)行）：
# 安裝依賴 yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm percona-release enable-only tools release yum install -y percona-xtrabackup-80 # 對應 MySQL 8.0
全量備份主庫（不鎖表，后臺執(zhí)行）：
# 創(chuàng)建備份目錄 mkdir -p /data/backup/mysql_full chown -R mysql:mysql /data/backup # 執(zhí)行備份（記錄 binlog 位置，后續(xù)從庫需要） xtrabackup --user=root --password=你的主庫密碼 --backup --target-dir=/data/backup/mysql_full/
備份完成后，查看備份目錄下的 xtrabackup_binlog_info 文件，記錄 binlog 文件名 和 Position（例如：mysql-bin.000003 156），這是主從同步的起點。
方案 2：使用 mysqldump（適合小庫，簡單易用）
mysqldump 加 --single-transaction 參數(shù)可實現(xiàn) InnoDB 引擎的非阻塞備份（MyISAM 仍會鎖表，需注意）。
備份主庫數(shù)據(jù)并記錄 binlog 位置：
# 先獲取當前 binlog 位置（備份開始時的狀態(tài)） mysql -u root -p -e "SHOW MASTER STATUS\G" > /tmp/master_status.txt # 備份所有庫（--single-transaction 確保 InnoDB 一致性，不鎖表） mysqldump -u root -p --all-databases --single-transaction --flush-logs --master-data=2 > /data/backup/master_data.sql
--flush-logs：備份后刷新 binlog，生成新的 binlog 文件（便于后續(xù)定位）。--master-data=2：在備份文件中記錄 binlog 位置（注釋狀態(tài)，不執(zhí)行），可打開master_data.sql查看（搜索CHANGE MASTER TO）。
三、Slave 從庫配置（基于主庫備份恢復）
1. 初始化從庫（確保與主庫版本一致）
從庫需安裝與主庫相同版本的 MySQL，且初始狀態(tài)為空（若已有數(shù)據(jù)，需先清理）：
# 停止從庫 MySQL systemctl stop mysqld # 清理默認數(shù)據(jù)目錄（注意：會刪除所有數(shù)據(jù)，謹慎操作） rm -rf /var/lib/mysql/*
2. 恢復主庫備份到從庫
若使用 XtraBackup 備份：
將備份文件傳到從庫：
scp -r /data/backup/mysql_full root@192.168.1.11:/data/backup/
在從庫恢復備份：
# 準備備份（確保數(shù)據(jù)一致性） xtrabackup --prepare --target-dir=/data/backup/mysql_full/ # 恢復到從庫數(shù)據(jù)目錄 xtrabackup --copy-back --target-dir=/data/backup/mysql_full/ # 修改權限（MySQL 需有權限訪問） chown -R mysql:mysql /var/lib/mysql/
若使用 mysqldump 備份：
將備份文件傳到從庫：
scp /data/backup/master_data.sql root@192.168.1.11:/tmp/
在從庫恢復數(shù)據(jù)：
# 啟動從庫 MySQL（確保數(shù)據(jù)目錄為空） systemctl start mysqld # 恢復備份（若備份文件大，可加 & 后臺執(zhí)行） mysql -u root -p < /tmp/master_data.sql
3. 配置從庫 my.cnf
[mysqld] server-id = 11 # 從庫唯一 ID，與主庫不同 relay_log = /var/lib/mysql/mysql-relay-bin # 中繼日志 read_only = 1 # 從庫只讀（root 可寫，若需嚴格只讀，可設置 super_read_only=1） replicate-ignore-db = information_schema # 忽略系統(tǒng)庫（可選） replicate-ignore-db = mysql replicate-ignore-db = performance_schema replicate-ignore-db = sys
重啟從庫 MySQL：
systemctl restart mysqld
4. 配置從庫連接主庫（關鍵：使用備份時的 binlog 位置）
登錄從庫 MySQL，設置主從同步參數(shù)：
mysql -u root -p -- 停止可能存在的復制進程 STOP SLAVE; -- 配置主庫信息（替換為實際值） CHANGE MASTER TO MASTER_HOST = '192.168.1.10', -- 主庫 IP MASTER_USER = 'repl', -- 復制賬號 MASTER_PASSWORD = 'Repl@123456', -- 復制密碼 MASTER_LOG_FILE = 'mysql-bin.000003', -- 備份時記錄的 binlog 文件名 MASTER_LOG_POS = 156; -- 備份時記錄的 Position -- 啟動從庫復制 START SLAVE;
四、驗證主從同步（不停服驗證）
1. 檢查從庫狀態(tài)
在從庫執(zhí)行：
SHOW SLAVE STATUS\G
重點關注：
Slave_IO_Running: Yes（I/O 線程正常，負責拉取主庫 binlog）Slave_SQL_Running: Yes（SQL 線程正常，負責執(zhí)行中繼日志）Seconds_Behind_Master: 0（無延遲，若為 NULL 或正值，需排查問題）
2. 業(yè)務數(shù)據(jù)驗證
在主庫執(zhí)行寫入操作（模擬業(yè)務）：
-- 主庫創(chuàng)建測試數(shù)據(jù) CREATE DATABASE IF NOT EXISTS test_sync; USE test_sync; CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20)); INSERT INTO t1 VALUES (1, '不停服同步測試');
在從庫查詢，確認數(shù)據(jù)已同步：
USE test_sync; SELECT * FROM t1; -- 若返回 (1, '不停服同步測試')，則成功
五、關鍵注意事項（不停服場景）
- 備份期間的數(shù)據(jù)一致性：
- XtraBackup 或
mysqldump --single-transaction僅保證 InnoDB 引擎的一致性，MyISAM 表仍會鎖表（建議業(yè)務盡量使用 InnoDB）。 - 若有 MyISAM 表，需在業(yè)務低峰期執(zhí)行，或短暫鎖表（
FLUSH TABLES WITH READ LOCK）后備份，鎖表時間越短越好。
- XtraBackup 或
- 主庫 binlog 保留：
- 從備份完成到從庫啟動同步前，主庫的 binlog 不能被刪除（否則從庫會因缺少 binlog 導致同步失敗）。
- 臨時關閉主庫的 binlog 自動清理（
SET GLOBAL expire_logs_days = 0;），同步正常后再恢復。
- 從庫延遲處理：
- 若從庫延遲（
Seconds_Behind_Master增大），檢查主庫寫入壓力是否過大，或從庫配置（如innodb_buffer_pool_size）是否過低。 - 可通過
pt-query-digest分析主庫慢查詢，優(yōu)化后減少從庫壓力。
- 若從庫延遲（
- 安全加固：
- 復制賬號密碼使用強密碼，并定期更換。
- 主從通信建議開啟 SSL 加密（在
CHANGE MASTER TO中添加MASTER_SSL=1）。
- 監(jiān)控配置：
- 部署監(jiān)控工具（如 Percona PMM、Zabbix），監(jiān)控
Slave_IO_Running、Slave_SQL_Running和延遲時間，設置告警（如延遲 > 60 秒）。
- 部署監(jiān)控工具（如 Percona PMM、Zabbix），監(jiān)控
通過以上步驟，可在不中斷主庫業(yè)務的情況下完成 MySQL 主從復制搭建，適用于生產(chǎn)環(huán)境的平滑擴容或災備需求。核心是利用熱備份工具確保數(shù)據(jù)一致性，并精準記錄 binlog 位置作為同步起點。
到此這篇關于mysql線上主從集群設置的文章就介紹到這了,更多相關mysql線上主從集群內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
本文主要介紹了Java開發(fā)中SQL優(yōu)化的條件下推技術,通過提前將過濾條件下推至子查詢/視圖執(zhí)行,減少中間結(jié)果量,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-09-09
IDEA的database插件無法連接mysql的解決辦法(08001錯誤)
用navicat鏈接數(shù)據(jù)庫正常,mysql控制臺操作正常,但是用IDEA的數(shù)據(jù)庫插件鏈接一直報 08001 錯誤,本文就給大家介紹一下IDEA的database插件無法連接mysql報08001錯誤的解決辦法,需要的朋友可以參考下2024-07-07
這篇文章主要為大家介紹了MySQL全文索引like模糊匹配查詢慢解決方法，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-11-11
本文介紹了在MySQL中實現(xiàn)中止回滾的方法,通過使用SAVEPOINT和ROLLBACKTOSAVEPOINT,在事務中實現(xiàn)部分回滾的效果,下面就來詳細的介紹一下mysql?中止回滾的實現(xiàn),感興趣的可以了解一下2026-01-01
此文章主要講述的是MySQL DELETE語法的詳細解析，以及一些在實際操作中值得我們大家注意的相關事項的描述，以下就是正文的主要內(nèi)容2013-03-03
MySQL中文漢字轉(zhuǎn)拼音的自定義函數(shù)和使用實例(首字的首字母)
這篇文章主要介紹了MySQL中文漢字轉(zhuǎn)拼音的自定義函數(shù)和使用實例,需要的朋友可以參考下2014-06-06
這篇文章主要為大家詳細介紹了navicat連接mysql報錯1251的解決方法，文中解決方法介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-07-07
MySQL實現(xiàn)批量更新不同表中的數(shù)據(jù)
這篇文章主要介紹了MySQL實現(xiàn)批量更新不同表中的數(shù)據(jù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-05-05
Mariadb數(shù)據(jù)庫的備份與恢復過程詳細介紹
MariaDB是一個流行的開源關系型數(shù)據(jù)庫管理系統(tǒng),備份與恢復數(shù)據(jù)是數(shù)據(jù)庫管理中非常重要的一個環(huán)節(jié),能夠保證數(shù)據(jù)的安全性和可靠性,這篇文章主要介紹了Mariadb數(shù)據(jù)庫的備份與恢復過程的相關資料,需要的朋友可以參考下2025-10-10
MySQL是一個開源的、關系型數(shù)據(jù)庫管理系統(tǒng),在開發(fā)過程中被廣泛使用,有時候我們可能會遇到MySQL連接不上本地服務器的問題,這篇文章主要介紹了MySQL部署后連接被拒絕問題的排查與解決方法,需要的朋友可以參考下2025-11-11