主庫寫入 → Binlog生產(chǎn) → 網(wǎng)絡(luò)傳輸 → Relay Log寫入 → SQL重放
    ↓         ↓           ↓           ↓           ↓
  應(yīng)用層    主庫IO     網(wǎng)絡(luò)層     從庫IO     從庫SQL

先量化 → 再定位 → 后優(yōu)化
  ↓        ↓       ↓
真實(shí)延遲  瓶頸層次  針對性方案

-- 基礎(chǔ)延遲查看
SHOW SLAVE STATUS\G

-- 關(guān)鍵字段解讀
Seconds_Behind_Master: 0              -- 從庫落后主庫秒數(shù)（可能不準(zhǔn)確）
Relay_Master_Log_File: mysql-bin.001  -- 當(dāng)前正在重放的主庫binlog文件
Exec_Master_Log_Pos: 1234567          -- 已執(zhí)行到的位置
Read_Master_Log_Pos: 1234567          -- 已讀取到的位置

-- 計(jì)算真實(shí)延遲（推薦）
SELECT 
    TIMESTAMPDIFF(SECOND, 
        FROM_UNIXTIME(@@global.sql_slave_skip_counter), 
        NOW()
    ) AS real_delay_seconds;

-- 方法1：基于binlog位置計(jì)算
SELECT 
    (Master_Log_File_Position - Exec_Master_Log_Pos) / 
    (主庫binlog生成速度) AS estimated_delay_seconds;

-- 方法2：基于心跳表（最準(zhǔn)確）
-- 主庫定期插入時(shí)間戳
INSERT INTO heartbeat_table (ts) VALUES (NOW());

-- 從庫查詢延遲
SELECT TIMESTAMPDIFF(SECOND, ts, NOW()) AS real_delay 
FROM heartbeat_table ORDER BY id DESC LIMIT 1;

延遲高？
   ↓
IO線程延遲？ ←─ Relay_Log_Space_Increase 快？
   ↓ 是              ↓ 是
網(wǎng)絡(luò)/主庫問題       從庫IO問題
   ↓ 否              ↓ 否
SQL線程延遲？ ←─ Seconds_Behind_Master 增長？
   ↓ 是
SQL執(zhí)行問題
   ↓ 否
其他問題

-- 查看線程狀態(tài)
SHOW SLAVE STATUS\G

-- 關(guān)鍵判斷：
-- 1. IO線程延遲特征
Relay_Master_Log_File != Master_Log_File  -- 讀取落后
Read_Master_Log_Pos - Exec_Master_Log_Pos 很大  -- 堆積多

-- 2. SQL線程延遲特征
Relay_Master_Log_File = Master_Log_File  -- 讀取跟上
但 Seconds_Behind_Master 很大  -- 執(zhí)行慢

# 1. 基礎(chǔ)ping測試
ping -c 10 主庫IP
# 正常：< 1ms（同機(jī)房），< 10ms（同城）
# 2. 帶寬測試
iperf3 -c 主庫IP -t 30
# 正常：> 100Mbps
# 3. 丟包率測試
ping -c 100 主庫IP | grep packet
# 正常：丟包率 < 0.1%
# 4. TCP連接質(zhì)量
netstat -s | grep retrans
# 正常：重傳率 < 0.01%

# 1. 啟用binlog壓縮（MySQL 8.0+）
SET GLOBAL binlog_transaction_compression = ON;
SET GLOBAL binlog_transaction_compression_level_zstd = 3;

# 2. 調(diào)整TCP參數(shù)
echo "net.ipv4.tcp_window_scaling = 1" >> /etc/sysctl.conf
echo "net.core.rmem_max = 16777216" >> /etc/sysctl.conf
echo "net.core.wmem_max = 16777216" >> /etc/sysctl.conf
sysctl -p

# 3. 使用專線/內(nèi)網(wǎng)
# 避免公網(wǎng)傳輸，使用VPC內(nèi)網(wǎng)或?qū)＞€

-- 查看relay log堆積情況
SHOW SLAVE STATUS\G

-- 關(guān)鍵指標(biāo)
Relay_Log_Space: 536870912  -- relay log總大?。ㄗ止?jié)）
Relay_Log_File: relay-bin.000123  -- 當(dāng)前relay log文件
Relay_Log_Pos: 1234567  -- 當(dāng)前位置

-- 計(jì)算堆積量
SELECT 
    (Read_Master_Log_Pos - Exec_Master_Log_Pos) / 1024 / 1024 AS堆積_MB;

# 1. iostat監(jiān)控
iostat -x 1 10
# 關(guān)鍵指標(biāo)
# %util: 磁盤利用率（>80%表示瓶頸）
# await: IO等待時(shí)間（<10ms正常）
# svctm: 服務(wù)時(shí)間（<5ms正常）
# 2. fio測試
fio --filename=/var/lib/mysql/test.io --direct=1 --rw=write \
    --bs=16k --size=1G --numjobs=1 --runtime=60 --group_reporting
# 3. 查看relay log寫入速度
watch -n 1 'ls -lh /var/lib/mysql/relay-bin.* | tail -5'

-- 1. 調(diào)整relay log相關(guān)參數(shù)
SET GLOBAL relay_log_recovery = ON;  -- 崩潰恢復(fù)更快
SET GLOBAL relay_log_purge = ON;     -- 及時(shí)清理

-- 2. 優(yōu)化磁盤IO
[mysqld]
# relay log優(yōu)化
relay_log_info_repository = TABLE
relay_log_recovery = ON
sync_relay_log = 10000  # 每10000個(gè)事件同步一次

# InnoDB優(yōu)化
innodb_flush_log_at_trx_commit = 2  # 從庫可設(shè)為2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

-- 3. 使用更快的存儲
# SSD/NVMe替代HDD
# RAID 10配置

-- 1. 查看當(dāng)前SQL線程狀態(tài)
SHOW PROCESSLIST;
-- 找到"Slave_SQL_Running_State"字段

-- 2. 開啟慢查詢?nèi)罩荆◤膸欤?
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1秒以上記錄
SET GLOBAL log_slow_slave_statements = ON;  -- 記錄復(fù)制的慢SQL

-- 3. 分析慢查詢?nèi)罩?
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

-- 4. 實(shí)時(shí)監(jiān)控SQL線程
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 1. 查看當(dāng)前執(zhí)行的事務(wù)
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- 2. 查看長時(shí)間運(yùn)行的SQL
SELECT 
    id, user, host, db, command, time, state, info
FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 60
ORDER BY time DESC;

-- 3. 分析binlog中的大事務(wù)
mysqlbinlog --base64-output=DECODE-ROWS \
    --start-position=1234567 \
    /var/lib/mysql/mysql-bin.000001 | \
    grep -A 100 "BEGIN" | head -200

-- 1. 查看InnoDB鎖等待
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 2. 查看元數(shù)據(jù)鎖（DDL阻塞）
SELECT * FROM performance_schema.metadata_locks 
WHERE OWNER_THREAD_ID != CONNECTION_ID();

-- 1. 啟用并行復(fù)制（MySQL 5.7+）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;  -- 根據(jù)CPU核心數(shù)設(shè)置
SET GLOBAL slave_preserve_commit_order = ON;

-- 2. 優(yōu)化SQL執(zhí)行
-- 主庫優(yōu)化：添加索引、拆分大事務(wù)、避免DDL高峰
-- 從庫優(yōu)化：調(diào)整buffer pool、優(yōu)化查詢緩存

-- 3. 調(diào)整復(fù)制參數(shù)
[mysqld]
# 并行復(fù)制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON

# SQL線程優(yōu)化
slave_transaction_retries = 10
slave_net_timeout = 60

# Buffer優(yōu)化
innodb_buffer_pool_size = 12G  # 物理內(nèi)存的70-80%
innodb_log_file_size = 2G

-- 單個(gè)事務(wù)包含大量操作
BEGIN;
-- 插入10萬條記錄
INSERT INTO orders SELECT * FROM temp_orders;
COMMIT;

-- 影響：從庫必須順序執(zhí)行，無法并行

-- 1. 拆分大事務(wù)
DELIMITER $$
CREATE PROCEDURE split_large_transaction()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100 DO
        START TRANSACTION;
        INSERT INTO orders 
        SELECT * FROM temp_orders 
        LIMIT 1000 OFFSET i*1000;
        COMMIT;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

-- 2. 使用批量插入優(yōu)化
INSERT INTO orders (col1, col2) VALUES 
    (val1, val2),
    (val3, val4),
    ...;

# 使用pt-online-schema-change
pt-online-schema-change \
    --alter "ADD COLUMN new_col INT" \
    --execute \
    D=your_db,t=your_table,h=localhost

# 使用gh-ost
gh-ost \
    --user="user" \
    --password="pass" \
    --host=localhost \
    --database="your_db" \
    --table="your_table" \
    --alter="ADD COLUMN new_col INT" \
    --execute

-- 主從配置對比腳本
SELECT 
    '主庫' AS server,
    @@innodb_buffer_pool_size AS buffer_pool,
    @@innodb_log_file_size AS log_file_size,
    @@max_connections AS max_connections
UNION ALL
SELECT 
    '從庫',
    @@innodb_buffer_pool_size,
    @@innodb_log_file_size,
    @@max_connections;

-- 1. 復(fù)制狀態(tài)查看
SHOW SLAVE STATUS\G
SHOW MASTER STATUS\G
SHOW PROCESSLIST\G

-- 2. 性能模式監(jiān)控
SELECT * FROM performance_schema.replication_connection_status;
SELECT * FROM performance_schema.replication_applier_status;
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 3. InnoDB狀態(tài)
SHOW ENGINE INNODB STATUS\G

-- 4. 鎖信息
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

# prometheus.yml
- job_name: 'mysql'
  static_configs:
    - targets: ['主庫IP:9104', '從庫IP:9104']

# 安裝PMM
docker run -d \
  -p 443:443 \
  -v pmm-data:/srv \
  --name pmm-server \
  --restart always \
  percona/pmm-server:2

# 安裝客戶端
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@pmm-server
pmm-admin add mysql --username=root --password=your_password

#!/bin/bash
# mysql_replication_diagnose.sh

echo "=== MySQL主從延遲診斷報(bào)告 ==="
echo "時(shí)間: $(date '+%Y-%m-%d %H:%M:%S')"

# 1. 基礎(chǔ)信息
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Relay_Master_Log_File|Exec_Master_Log_Pos"

# 2. 延遲計(jì)算
DELAY=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
echo "當(dāng)前延遲: ${DELAY}s"

# 3. 線程狀態(tài)
echo "--- 線程狀態(tài) ---"
mysql -e "SHOW PROCESSLIST\G" | grep -A 5 "Slave"

# 4. 磁盤IO
echo "--- 磁盤IO狀態(tài) ---"
iostat -x 1 3 | tail -20

# 5. 網(wǎng)絡(luò)延遲
echo "--- 網(wǎng)絡(luò)延遲 ---"
ping -c 5 $(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Master_Host" | awk '{print $2}')

echo "=== 診斷完成 ==="

# my.cnf 生產(chǎn)環(huán)境標(biāo)準(zhǔn)配置
[mysqld]
# 基礎(chǔ)配置
server-id = 101  # 主庫
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
# 復(fù)制優(yōu)化
sync_binlog = 1000  # 主庫可適當(dāng)降低
innodb_flush_log_at_trx_commit = 1  # 主庫保持1
# 并行復(fù)制（從庫）
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
# Relay Log優(yōu)化
relay_log_info_repository = TABLE
relay_log_recovery = ON
sync_relay_log = 10000
# Buffer優(yōu)化
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
# 監(jiān)控
performance_schema = ON
log_slow_slave_statements = ON
long_query_time = 1

# alert_rules.yml
groups:
  - name: mysql_replication
    rules:
      - alert: MySQLReplicationDelay
        expr: mysql_slave_seconds_behind_master > 10
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "MySQL主從延遲過高"
          description: "從庫 {{ $labels.instance }} 延遲 {{ $value }} 秒"
      - alert: MySQLReplicationStopped
        expr: mysql_slave_sql_running == 0 or mysql_slave_io_running == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL復(fù)制停止"
          description: "從庫 {{ $labels.instance }} 復(fù)制線程停止"
      - alert: MySQLRelayLogAccumulation
        expr: rate(mysql_slave_relay_log_pos[5m]) < 10000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL Relay Log堆積"
          description: "從庫 {{ $labels.instance }} Relay Log堆積"

#!/bin/bash
# auto_heal_replication.sh
DELAY_THRESHOLD=60
MAX_RETRY=3
while true; do
    # 獲取當(dāng)前延遲
    DELAY=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
    # 檢查延遲是否超標(biāo)
    if [ "$DELAY" -gt "$DELAY_THRESHOLD" ]; then
        echo "$(date): 檢測到延遲 $DELAY 秒，開始自動(dòng)修復(fù)"
        # 檢查SQL線程狀態(tài)
        SQL_RUNNING=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')
        if [ "$SQL_RUNNING" == "No" ]; then
            echo "SQL線程停止，嘗試重啟"
            mysql -e "STOP SLAVE; START SLAVE;"
        fi
        # 檢查是否有錯(cuò)誤
        LAST_ERROR=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Last_SQL_Error" | awk '{$1=$2=""; print $0}')
        if [ -n "$LAST_ERROR" ]; then
            echo "檢測到錯(cuò)誤: $LAST_ERROR"
            # 跳過錯(cuò)誤（謹(jǐn)慎使用）
            mysql -e "STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;"
        fi
    fi
    sleep 60
done

發(fā)現(xiàn)延遲告警
    ↓
確認(rèn)延遲真實(shí)性（心跳表驗(yàn)證）
    ↓
定位瓶頸層次（IO/SQL/網(wǎng)絡(luò)）
    ↓
針對性處理
    ├─ 網(wǎng)絡(luò)問題 → 聯(lián)系網(wǎng)絡(luò)團(tuán)隊(duì)
    ├─ IO問題 → 優(yōu)化磁盤、調(diào)整參數(shù)
    └─ SQL問題 → 優(yōu)化查詢、啟用并行
    ↓
驗(yàn)證修復(fù)效果
    ↓
記錄故障報(bào)告
    ↓
優(yōu)化預(yù)防措施

-- 建立性能基線表
CREATE TABLE replication_baseline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    record_time DATETIME,
    avg_delay_seconds DECIMAL(10,2),
    max_delay_seconds DECIMAL(10,2),
    io_thread_status VARCHAR(20),
    sql_thread_status VARCHAR(20),
    relay_log_size_mb DECIMAL(10,2),
    network_latency_ms DECIMAL(10,2),
    INDEX idx_record_time (record_time)
);

-- 定時(shí)記錄基線數(shù)據(jù)
DELIMITER $$
CREATE EVENT record_replication_baseline
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    INSERT INTO replication_baseline 
    SELECT 
        NOW(),
        AVG(Seconds_Behind_Master),
        MAX(Seconds_Behind_Master),
        Slave_IO_Running,
        Slave_SQL_Running,
        Relay_Log_Space / 1024 / 1024,
        -- 網(wǎng)絡(luò)延遲需要外部腳本獲取
        0
    FROM information_schema.slave_status;
END$$
DELIMITER ;

# 1. 基礎(chǔ)狀態(tài)
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind|Position"
# 2. 真實(shí)延遲（心跳表）
mysql -e "SELECT TIMESTAMPDIFF(SECOND, ts, NOW()) AS delay FROM heartbeat ORDER BY id DESC LIMIT 1;"
# 3. Relay Log堆積
mysql -e "SHOW SLAVE STATUS\G" | awk '/Read_Master_Log_Pos|Exec_Master_Log_Pos/{print $2}' | awk 'NR==1{a=$1} NR==2{print "堆積: " (a-$1)/1024/1024 " MB"}'
# 4. 線程狀態(tài)
mysql -e "SHOW PROCESSLIST\G" | grep -A 3 "Slave"
# 5. 網(wǎng)絡(luò)延遲
ping -c 5 $(mysql -N -e "SHOW SLAVE STATUS\G" | grep Master_Host | awk '{print $2}')
# 6. 磁盤IO
iostat -x 1 5 | grep -A 5 "Device"

# MySQL 8.0 主從復(fù)制生產(chǎn)配置
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
# 基礎(chǔ)配置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
tmpdir = /tmp
# 網(wǎng)絡(luò)配置
bind-address = 0.0.0.0
port = 3306
max_connections = 500
# 復(fù)制配置（主庫）
server-id = 101
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1000
binlog_group_commit_sync_delay = 100
binlog_transaction_compression = ON
binlog_transaction_compression_level_zstd = 3
# 復(fù)制配置（從庫）
# server-id = 102
# relay_log = mysql-relay-bin
# read_only = ON
# super_read_only = ON
# 并行復(fù)制（從庫）
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
relay_log_info_repository = TABLE
relay_log_recovery = ON
sync_relay_log = 10000
log_slow_slave_statements = ON
# InnoDB配置
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1  # 從庫可設(shè)為2
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_file_per_table = ON
# 性能優(yōu)化
performance_schema = ON
thread_cache_size = 100
table_open_cache = 4000
query_cache_type = 0
query_cache_size = 0
# 監(jiān)控配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
# 安全配置
skip_name_resolve = ON
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
quote-names
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[isamchk]
key_buffer_size = 16M