MySQL主從架構(gòu)原理、優(yōu)化與實(shí)踐指南全解析
更新時(shí)間：2026年04月08日 09:24:03 作者：Harvy_沒(méi)救了
這是一份關(guān)于MySQL主從架構(gòu)的深度解析指南,本文將從核心原理出發(fā),延伸至架構(gòu)優(yōu)化、延遲排查及高可用切換等實(shí)踐層面,幫助您構(gòu)建一個(gè)穩(wěn)定、高效的 MySQL 數(shù)據(jù)同步體系
文檔名稱
MySQL高可用架構(gòu)：主從復(fù)制原理與讀寫(xiě)分離實(shí)踐
MySQL 主從復(fù)制的核心是基于二進(jìn)制日志 的數(shù)據(jù)同步機(jī)制。簡(jiǎn)而言之，主庫(kù)將變更寫(xiě)入 Binlog，從庫(kù)通過(guò) IO 線程拉取并寫(xiě)入 Relay Log，再由 SQL 線程回放。
三大核心線程
- 主庫(kù) Dump 線程：主庫(kù)為每個(gè)從庫(kù)啟動(dòng)一個(gè)線程，負(fù)責(zé)讀取 Binlog 事件并發(fā)送給從庫(kù)。需要注意的是，讀取操作會(huì)在 Binlog 上加鎖，讀取完成后釋放，以保證日志的完整性。
- 從庫(kù) IO 線程：連接主庫(kù)并請(qǐng)求 Binlog，將接收到的數(shù)據(jù)寫(xiě)入本地的 Relay Log（中繼日志）。
- 從庫(kù) SQL 線程：讀取 Relay Log 中的事件，在從庫(kù)本地重放（Replay），從而更新數(shù)據(jù)。
一、核心概念體系
1.1 主從復(fù)制基礎(chǔ)架構(gòu)
-- 架構(gòu)示意圖
Master (主庫(kù)) → Binary Log → Relay Log → Slave (從庫(kù))
↓
Write Operations Read Operations
1.2 復(fù)制類型對(duì)比
| 復(fù)制類型 | 數(shù)據(jù)一致性 | 性能影響 | 適用場(chǎng)景 |
|---|---|---|---|
| 異步復(fù)制 | 最終一致 | 低延遲 | 讀寫(xiě)分離 |
| 半同步復(fù)制 | 較強(qiáng)一致 | 中等延遲 | 金融交易 |
| 全同步復(fù)制 | 強(qiáng)一致 | 高延遲 | 數(shù)據(jù)強(qiáng)一致 |
二、主從復(fù)制工作原理
2.1 二進(jìn)制日志(Binlog)格式
-- 查看當(dāng)前binlog格式 SHOW VARIABLES LIKE 'binlog_format'; -- 三種格式對(duì)比 -- 1. STATEMENT: 記錄SQL語(yǔ)句 -- 2. ROW: 記錄行數(shù)據(jù)變更(推薦) -- 3. MIXED: 混合模式
2.2 復(fù)制工作流程
-- Master端配置示例 -- my.cnf配置 [mysqld] server-id = 1 log-bin = mysql-bin binlog_format = ROW expire_logs_days = 7 max_binlog_size = 100M sync_binlog = 1 -- 創(chuàng)建復(fù)制用戶 CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl123456'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; -- 查看Master狀態(tài) SHOW MASTER STATUS; /* +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | +------------------+----------+--------------+------------------+ */
2.3 Slave端配置
-- my.cnf配置
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read_only = 1
log_slave_updates = 1
-- 配置復(fù)制鏈路
CHANGE MASTER TO
MASTER_HOST = 'master_host',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'Repl123456',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 107,
MASTER_CONNECT_RETRY = 60,
MASTER_HEARTBEAT_PERIOD = 10;
-- 啟動(dòng)復(fù)制
START SLAVE;
-- 查看復(fù)制狀態(tài)
SHOW SLAVE STATUS\G
/*
Slave_IO_Running: Yes # I/O線程狀態(tài)
Slave_SQL_Running: Yes # SQL線程狀態(tài)
Seconds_Behind_Master: 0 # 復(fù)制延遲秒數(shù)
Last_IO_Error: # 最后IO錯(cuò)誤
Last_SQL_Error: # 最后SQL錯(cuò)誤
*/
三、主從延遲分析與優(yōu)化
3.1 延遲原因深度分析
3.1.1 硬件資源瓶頸
-- 監(jiān)控指標(biāo)查詢 -- 磁盤(pán)IO性能 SHOW GLOBAL STATUS LIKE 'Innodb_data_%'; -- 網(wǎng)絡(luò)延遲檢測(cè) -- Master執(zhí)行 SELECT NOW(); -- Slave執(zhí)行對(duì)比時(shí)間差 -- CPU負(fù)載 SHOW PROCESSLIST;
3.1.2 配置參數(shù)影響
-- 關(guān)鍵參數(shù)檢查 SHOW VARIABLES LIKE '%sync_binlog%'; -- 建議=1 SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%'; -- 建議=1 SHOW VARIABLES LIKE '%slave_parallel_workers%'; -- 并行復(fù)制 SHOW VARIABLES LIKE '%slave_parallel_type%'; -- LOGICAL_CLOCK
3.2 延遲監(jiān)控方案
-- 創(chuàng)建延遲監(jiān)控表
CREATE TABLE replication_monitor (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
check_time DATETIME DEFAULT CURRENT_TIMESTAMP,
slave_host VARCHAR(50),
seconds_behind_master INT,
slave_io_running VARCHAR(3),
slave_sql_running VARCHAR(3),
last_io_error TEXT,
last_sql_error TEXT,
KEY idx_check_time (check_time)
);
-- 監(jiān)控存儲(chǔ)過(guò)程
DELIMITER $$
CREATE PROCEDURE monitor_replication_lag()
BEGIN
DECLARE v_seconds_behind_master INT;
DECLARE v_slave_io_running VARCHAR(3);
DECLARE v_slave_sql_running VARCHAR(3);
DECLARE v_last_io_error TEXT;
DECLARE v_last_sql_error TEXT;
SELECT
Seconds_Behind_Master,
Slave_IO_Running,
Slave_SQL_Running,
Last_IO_Error,
Last_SQL_Error
INTO
v_seconds_behind_master,
v_slave_io_running,
v_slave_sql_running,
v_last_io_error,
v_last_sql_error
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = '';
INSERT INTO replication_monitor
(slave_host, seconds_behind_master, slave_io_running,
slave_sql_running, last_io_error, last_sql_error)
VALUES
(@@hostname, v_seconds_behind_master, v_slave_io_running,
v_slave_sql_running, v_last_io_error, v_last_sql_error);
END$$
DELIMITER ;
-- 創(chuàng)建定時(shí)事件
CREATE EVENT IF NOT EXISTS monitor_replication_event
ON SCHEDULE EVERY 30 SECOND
DO CALL monitor_replication_lag();
3.3 優(yōu)化策略實(shí)踐
3.3.1 并行復(fù)制配置
-- MySQL 5.7+ 并行復(fù)制配置 STOP SLAVE; SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK'; SET GLOBAL slave_parallel_workers = 8; -- 根據(jù)CPU核心數(shù)調(diào)整 START SLAVE; -- 查看并行復(fù)制狀態(tài) SHOW VARIABLES LIKE 'slave_parallel%'; SELECT * FROM performance_schema.replication_applier_status_by_worker;
3.3.2 大事務(wù)優(yōu)化
-- 1. 拆分大事務(wù) -- 不推薦 START TRANSACTION; INSERT INTO large_table SELECT * FROM huge_source; -- 百萬(wàn)行 COMMIT; -- 推薦：分批提交 SET autocommit = 0; INSERT INTO large_table SELECT * FROM huge_source LIMIT 10000; COMMIT; -- 循環(huán)處理... -- 2. 使用pt-online-schema-change避免DDL鎖表 -- 示例命令 pt-online-schema-change \ --alter="ADD INDEX idx_name (name)" \ D=test,t=large_table \ --execute
3.3.3 網(wǎng)絡(luò)優(yōu)化配置
-- 增大復(fù)制緩沖區(qū) SET GLOBAL slave_net_timeout = 60; -- 默認(rèn)60秒 SET GLOBAL slave_compressed_protocol = ON; -- 開(kāi)啟壓縮 -- Master端增大binlog緩存 SET GLOBAL binlog_cache_size = 4M; SET GLOBAL max_binlog_size = 512M;
四、讀寫(xiě)分離實(shí)現(xiàn)方案
4.1 應(yīng)用層分離方案
// Spring Boot + MyBatis 多數(shù)據(jù)源配置示例
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("master", masterDataSource());
targetDataSources.put("slave", slaveDataSource());
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
return dynamicDataSource;
}
}
// 自定義路由數(shù)據(jù)源
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// 根據(jù)注解選擇數(shù)據(jù)源
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
// 自定義注解
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
String value() default "master";
}
// 使用示例
@Service
public class UserService {
@DataSource("master")
public void createUser(User user) {
// 寫(xiě)操作，使用主庫(kù)
userMapper.insert(user);
}
@DataSource("slave")
public User getUserById(Long id) {
// 讀操作，使用從庫(kù)
return userMapper.selectById(id);
}
}
4.2 中間件方案（ProxySQL示例）
-- 1. 安裝后配置后端服務(wù)器
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES
(10, 'master_host', 3306), -- 寫(xiě)組
(20, 'slave1_host', 3306), -- 讀組
(20, 'slave2_host', 3306);
-- 2. 配置監(jiān)控用戶
UPDATE global_variables
SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables
SET variable_value='monitor_password'
WHERE variable_name='mysql-monitor_password';
-- 3. 配置路由規(guī)則
-- 寫(xiě)操作路由到hostgroup 10
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
(1, 1, '^INSERT', 10, 1),
(2, 1, '^UPDATE', 10, 1),
(3, 1, '^DELETE', 10, 1),
(4, 1, '^SELECT.*FOR UPDATE', 10, 1);
-- 讀操作路由到hostgroup 20
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
(5, 1, '^SELECT', 20, 1);
-- 4. 加載配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
-- 5. 配置讀寫(xiě)分離用戶
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('app_user', 'password', 10);
4.3 延遲感知路由
// 延遲感知的負(fù)載均衡策略
public class LagAwareLoadBalance {
private List<SlaveInfo> slaves = new ArrayList<>();
private static final int MAX_LAG_THRESHOLD = 5; // 最大允許延遲5秒
public DataSource selectSlave() {
List<SlaveInfo> availableSlaves = slaves.stream()
.filter(s -> s.getLagSeconds() <= MAX_LAG_THRESHOLD)
.collect(Collectors.toList());
if (availableSlaves.isEmpty()) {
// 所有從庫(kù)延遲過(guò)高，降級(jí)到主庫(kù)
return masterDataSource;
}
// 加權(quán)隨機(jī)選擇（延遲越低權(quán)重越高）
return selectByWeight(availableSlaves);
}
private DataSource selectByWeight(List<SlaveInfo> slaves) {
int totalWeight = slaves.stream()
.mapToInt(s -> calculateWeight(s.getLagSeconds()))
.sum();
int random = new Random().nextInt(totalWeight);
int current = 0;
for (SlaveInfo slave : slaves) {
current += calculateWeight(slave.getLagSeconds());
if (random < current) {
return slave.getDataSource();
}
}
return slaves.get(0).getDataSource();
}
private int calculateWeight(int lagSeconds) {
// 延遲越低，權(quán)重越高
return Math.max(10 - lagSeconds, 1);
}
}
五、故障處理與恢復(fù)
5.1 常見(jiàn)故障處理
-- 1. 主從復(fù)制中斷 -- 查看錯(cuò)誤信息 SHOW SLAVE STATUS\G -- 常見(jiàn)錯(cuò)誤1：主鍵沖突 -- 解決方案：跳過(guò)錯(cuò)誤 STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE; -- 或指定GTID跳過(guò) STOP SLAVE; SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; START SLAVE; -- 2. 數(shù)據(jù)不一致修復(fù) -- 使用pt-table-checksum檢查 pt-table-checksum \ --replicate=test.checksums \ --databases=your_db \ h=master_host,u=user,p=password -- 使用pt-table-sync修復(fù) pt-table-sync \ --execute \ --replicate test.checksums \ h=master_host,u=user,p=password \ h=slave_host,u=user,p=password -- 3. 重新同步數(shù)據(jù) -- 方法1：使用mysqldump -- Master端 mysqldump --master-data=2 --single-transaction -uroot -p dbname > dbname.sql -- Slave端 STOP SLAVE; source dbname.sql; START SLAVE; -- 方法2：使用xtrabackup # Master備份 xtrabackup --backup --target-dir=/backup/master/ # Slave恢復(fù) xtrabackup --prepare --target-dir=/backup/master/ xtrabackup --copy-back --target-dir=/backup/master/
5.2 高可用架構(gòu)
-- MHA (Master High Availability) 配置示例 -- 1. 配置SSH免密登錄 -- 2. 編輯配置文件 /etc/mha/app1.cnf [server default] manager_workdir=/var/log/mha/app1 manager_log=/var/log/mha/app1/manager.log ssh_user=mysql user=repl password=Repl123456 repl_user=repl repl_password=Repl123456 [server1] hostname=master_host candidate_master=1 [server2] hostname=slave1_host candidate_master=1 [server3] hostname=slave2_host no_master=1 -- 3. 啟動(dòng)MHA監(jiān)控 masterha_manager --conf=/etc/mha/app1.cnf
六、監(jiān)控與告警體系
6.1 Prometheus監(jiān)控配置
# prometheus.yml 配置
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['master_host:9104', 'slave1_host:9104']
params:
collect[]:
- global_status
- slave_status
- info_schema.innodb_metrics
# mysqld_exporter啟動(dòng)
./mysqld_exporter \
--collect.slave_status \
--collect.info_schema.innodb_metrics \
--collect.global_status \
--web.listen-address=":9104"
6.2 Grafana監(jiān)控面板
// 關(guān)鍵監(jiān)控指標(biāo)
{
"panels": [
{
"title": "復(fù)制延遲",
"targets": [{
"expr": "mysql_slave_status_seconds_behind_master",
"legendFormat": "{{instance}}"
}],
"thresholds": [
{"color": "green", "value": 0},
{"color": "yellow", "value": 5},
{"color": "red", "value": 30}
]
},
{
"title": "復(fù)制線程狀態(tài)",
"targets": [{
"expr": "mysql_slave_status_slave_io_running",
"legendFormat": "IO線程 {{instance}}"
}]
}
]
}
6.3 告警規(guī)則
# alertmanager配置
groups:
- name: mysql_alerts
rules:
- alert: HighReplicationLag
expr: mysql_slave_status_seconds_behind_master > 30
for: 2m
labels:
severity: warning
annotations:
summary: "MySQL復(fù)制延遲過(guò)高"
description: "實(shí)例 {{ $labels.instance }} 復(fù)制延遲已達(dá) {{ $value }} 秒"
- alert: SlaveNotRunning
expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0
for: 1m
labels:
severity: critical
annotations:
summary: "MySQL從庫(kù)復(fù)制停止"
七、最佳實(shí)踐總結(jié)
7.1 配置清單
-- 主庫(kù)配置要點(diǎn) [mysqld] server-id = 1 log-bin = mysql-bin binlog_format = ROW expire_logs_days = 7 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 -- 從庫(kù)配置要點(diǎn) [mysqld] server-id = 2 relay-log = mysql-relay-bin read_only = 1 log_slave_updates = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 4
7.2 維護(hù)腳本
#!/bin/bash
# 復(fù)制狀態(tài)檢查腳本
check_replication() {
local host=$1
local user=$2
local password=$3
result=$(mysql -h$host -u$user -p$password -e "SHOW SLAVE STATUS\G")
io_running=$(echo "$result" | grep "Slave_IO_Running" | awk '{print $2}')
sql_running=$(echo "$result" | grep "Slave_SQL_Running" | awk '{print $2}')
lag=$(echo "$result" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$io_running" = "Yes" ] && [ "$sql_running" = "Yes" ]; then
echo "Replication is running. Lag: ${lag} seconds"
if [ $lag -gt 30 ]; then
echo "WARNING: High replication lag detected!"
return 2
fi
return 0
else
echo "ERROR: Replication is broken!"
return 1
fi
}
# 定期檢查
while true; do
check_replication "slave_host" "monitor" "password"
sleep 60
done
7.3 性能優(yōu)化檢查表
- 使用ROW格式的binlog
- 開(kāi)啟并行復(fù)制
- 合理設(shè)置innodb_buffer_pool_size
- 監(jiān)控和優(yōu)化慢查詢
- 定期清理無(wú)用binlog
- 配置適當(dāng)?shù)木W(wǎng)絡(luò)超時(shí)時(shí)間
- 實(shí)現(xiàn)延遲感知的路由策略
- 建立完善的監(jiān)控告警體系
結(jié)論
MySQL主從架構(gòu)是企業(yè)級(jí)應(yīng)用的基礎(chǔ)架構(gòu)，合理配置和優(yōu)化主從復(fù)制、有效管理主從延遲、智能實(shí)現(xiàn)讀寫(xiě)分離是保障系統(tǒng)穩(wěn)定性和擴(kuò)展性的關(guān)鍵。本文提供了從基礎(chǔ)配置到高級(jí)優(yōu)化的完整解決方案，結(jié)合實(shí)際案例代碼，可以幫助開(kāi)發(fā)者構(gòu)建高性能、高可用的數(shù)據(jù)庫(kù)架構(gòu)。
建議根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景選擇合適的復(fù)制策略和讀寫(xiě)分離方案，并建立完善的監(jiān)控體系，確保數(shù)據(jù)庫(kù)服務(wù)的穩(wěn)定可靠。
到此這篇關(guān)于MySQL主從架構(gòu)原理、優(yōu)化與實(shí)踐指南全解析的文章就介紹到這了,更多相關(guān)mysql主從架構(gòu)原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
從MySQL5.5開(kāi)始，MySQL以插件的形式支持半同步復(fù)制。如何理解半同步呢？今天我們就來(lái)詳細(xì)講解下，希望大家能夠喜歡。2017-03-03
MySQL中Xtrabackup高效部署實(shí)現(xiàn)主從復(fù)制方案
本文主要介紹了使用PerconaXtrabackup進(jìn)行大規(guī)模數(shù)據(jù)庫(kù)（>50GB）主從復(fù)制的部署流程,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2026-04-04
這篇文章主要介紹了MySQL 雙活同步復(fù)制四種方案,主從復(fù)制分成三步，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-08-08
在SQL中,SUM函數(shù)是用于計(jì)算指定字段的總和的聚合函數(shù),這篇文章將給大家詳細(xì)介紹了SUM函數(shù)在SQL中的值處理原則,文中有詳細(xì)的代碼示例供大家參考,具有一定的參考價(jià)值,需要的朋友可以參考下2023-12-12
關(guān)于case when語(yǔ)句的報(bào)錯(cuò)問(wèn)題詳解
SQL CASE 表達(dá)式是一種通用的條件表達(dá)式，類似于其它語(yǔ)言中的 if/else 語(yǔ)句，下面這篇文章主要給大家介紹了關(guān)于case when語(yǔ)句的報(bào)錯(cuò)問(wèn)題的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友可以參考下2018-08-08
使用limit,offset分頁(yè)場(chǎng)景時(shí)為什么會(huì)慢
這篇文章主要介紹了使用limit,offset分頁(yè)場(chǎng)景時(shí)為什么會(huì)慢，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-11-11
mysql中刪除重復(fù)記錄的方法有很多種，下面這篇文章主要給大家總結(jié)了在MySQL中查詢、刪除重復(fù)記錄的方法大全，文中給出了詳細(xì)的示例代碼供大家參考學(xué)習(xí)，需要的朋友下面來(lái)一起看看吧。2017-06-06
mysql連接器之mysql-connector-java問(wèn)題
這篇文章主要介紹了mysql連接器之mysql-connector-java問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
本文介紹mysql自增ID的起始值修改與設(shè)置方法2013-11-11
這篇文章主要介紹了體系化探討令人頭疼的JOIN運(yùn)算,本文將對(duì)JOIN運(yùn)算進(jìn)行體系化深入的探討，根據(jù)自己工作經(jīng)驗(yàn)及參考業(yè)界經(jīng)典案例，針對(duì)性地提出語(yǔ)法簡(jiǎn)化和性能優(yōu)化的方法論，需要的朋友可以參考下2022-07-07