2. MySQL DBA复制与备份高级运维完整指南

MySQL 企业级复制与备份所有姿势详解

第一章 MySQL主从复制基础

1.1 复制的作用

MySQL主从复制是高可用架构中的核心技术，主要作用包括：

横向扩展读能力 - 通过多个从库分担读请求，提升系统整体读性能
关键操作分离 - 备份、数据分析等重负载操作可在从库执行，不影响主库性能
数据灾备 - 提供数据冗余，保障业务连续性
丰富的生态圈 - 支持多种复制拓扑和工具

1.2 复制原理

1.2.1 复制架构图

┌─────────────────────┐                    ┌─────────────────────┐
│      Master         │                    │       Slave         │
│                     │                    │                     │
│  ┌──────────────┐   │                    │   ┌─────────────┐  │
│  │ Data Changes │   │                    │   │  I/O Thread │  │
│  └──────┬───────┘   │                    │   └──────┬──────┘  │
│         │           │     Read           │          │         │
│         ▼           │  ◄─────────        │          │         │
│  ┌──────────────┐   │                    │          ▼ Write   │
│  │  Binary Log  │───┼────────────────────┼──►┌─────────────┐  │
│  └──────────────┘   │                    │   │  Relay Log  │  │
│                     │                    │   └──────┬──────┘  │
└─────────────────────┘                    │          │ Read    │
                                           │          ▼         │
                                           │   ┌─────────────┐  │
                                           │   │ SQL Thread  │  │
                                           │   └──────┬──────┘  │
                                           │          │ Replay  │
                                           │          ▼         │
                                           │   ┌─────────────┐  │
                                           │   │  Data Apply │  │
                                           │   └─────────────┘  │
                                           └─────────────────────┘

1.2.2 复制流程详解

核心三步走：

从库启动复制（START SLAVE）
- 创建IO Thread连接主库
- 建立持久化的复制连接
主库响应连接请求
- 创建Binlog Dump Thread
- 读取binlog中的二进制日志事件
- 将事件发送给从库IO Thread
- 从库IO Thread将事件写入Relay Log
从库SQL Thread重放
- 读取Relay Log中的事件
- 在从库上执行，实现数据同步

1.3 搭建主从复制实战

环境准备

主库：192.168.79.10
从库：192.168.79.20

1.3.1 配置文件设置

主库配置

[mysqld]
log-bin = mysql-bin
server-id = 1

从库配置

[mysqld]
server-id = 2

关键参数说明：

log-bin：开启二进制日志
server-id：全局唯一的服务器ID

重要提示：

修改server-id无需重启实例
开启binlog需要重启实例
MySQL 8.0中binlog默认开启，关闭需设置skip_log_bin或disable_log_bin

1.3.2 创建复制用户

在主库执行：

CREATE USER 'repl'@'192.168.79.20' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.79.20';

在从库测试连接：

mysql -h 192.168.79.10 -urepl -p123456

1.3.3 备份主库数据

使用mysqldump备份（单事务一致性）：

mysqldump -S /data/mysql/3306/data/mysql.sock \
  --single-transaction \
  --master-data=2 \
  -E -R --triggers \
  -A > full_backup.sql

传输到从库：

scp full_backup.sql 192.168.79.20:/backup

1.3.4 恢复从库数据

mysql -S /data/mysql/3306/data/mysql.sock < /backup/full_backup.sql

1.3.5 建立主从关系

获取位置点信息：

grep -m 1 "CHANGE MASTER TO" /backup/full_backup.sql
# 输出：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=715;

执行CHANGE MASTER TO：

CHANGE MASTER TO
  MASTER_HOST='192.168.79.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=715,
  GET_MASTER_PUBLIC_KEY = 1;  -- MySQL 8.0且使用caching_sha2_password时需要

参数说明：

MASTER_HOST：主库IP/主机名
MASTER_PORT：主库端口（默认3306）
MASTER_USER：复制用户
MASTER_PASSWORD：复制用户密码
MASTER_LOG_FILE：从哪个binlog文件开始
MASTER_LOG_POS：从哪个位置点开始
GET_MASTER_PUBLIC_KEY：MySQL 8.0使用caching_sha2_password时需要设置为1

1.3.6 启动复制

START SLAVE;
SHOW SLAVE STATUS\G

关键状态检查：

Slave_IO_Running: Yes - IO线程正常运行
Slave_SQL_Running: Yes - SQL线程正常运行
两者都为Yes表示主从复制搭建成功

1.4 复制状态监控

1.4.1 主库监控

SHOW PROCESSLIST;

可看到Binlog Dump线程连接信息。

1.4.2 从库监控

SHOW PROCESSLIST;

MySQL 8.0线程说明：

1个IO Thread（连接主库拉取binlog）
1个Coordinator Thread（协调器）
4个Worker Thread（SQL工作线程，默认配置）

1.4.3 查看复制用户密码

SELECT * FROM mysql.slave_master_info\G

可查看User_password字段获取密码。

1.4.4 查看从库重放信息

SELECT * FROM mysql.slave_relay_log_info\G

1.5 注意事项与最佳实践

安全性：

复制用户密码存储在mysql.slave_master_info表中，建议设置适当的权限
生产环境建议使用SSL加密复制连接

监控指标：

Seconds_Behind_Master：从库延迟秒数
Read_Master_Log_Pos：IO线程读取的主库binlog位置
Exec_Master_Log_Pos：SQL线程执行的主库binlog位置

常见问题：

IO线程无法连接：检查网络、防火墙、复制用户权限
SQL线程报错：通常是数据冲突或权限问题，检查Last_SQL_Error字段

第二章 GTID复制

2.1 GTID概述

2.1.1 什么是GTID

GTID（Global Transaction Identifier，全局事务ID）为每个事务分配一个全局唯一的标识符。

GTID格式：

source_id:transaction_id

source_id：事务产生的实例UUID（通常使用server_uuid）
transaction_id：事务序列号，从1开始自增

示例：

e34ad510-7919-11ee-bb08-525400d51a16:1-25855

2.2 GTID的优势

GTID的推出极大简化了MySQL复制和故障恢复：

全局唯一标识 - 避免了binlog文件名和位置点的复杂性
简化配置 - 无需手动跟踪binlog文件和位置
简化故障恢复 - 主从切换更加容易
避免重复应用 - 防止事务在主从间重复执行，提高数据一致性

2.3 开启GTID复制

2.3.1 关键参数

gtid-mode=on
enforce-gtid-consistency=1

参数详解：

gtid_mode（可在线调整从MySQL 5.7.6开始）

OFF：所有事务必须是匿名事务（非GTID）
OFF_PERMISSIVE：新事务是匿名，复制可接受GTID和匿名事务
ON_PERMISSIVE：新事务是GTID，复制可接受GTID和匿名事务
ON：所有事务必须是GTID事务

调整顺序： OFF → OFF_PERMISSIVE → ON_PERMISSIVE → ON

enforce_gtid_consistency

OFF：关闭检测
ON：开启检测，违反限制直接报错
WARN：开启检测，违反限制记录警告到错误日志

注意： gtid_mode=ON时，enforce_gtid_consistency必须为ON

2.3.2 建立GTID复制

CHANGE MASTER TO
  MASTER_HOST='192.168.79.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_AUTO_POSITION = 1,  -- 关键：自动定位，无需指定文件和位置
  GET_MASTER_PUBLIC_KEY = 1;

对比传统复制：

不需要指定MASTER_LOG_FILE和MASTER_LOG_POS
自动根据GTID集合同步

2.4 GTID相关参数详解

SHOW VARIABLES LIKE '%gtid%';

核心参数：

参数	说明	默认值
gtid_mode	是否开启GTID	OFF
enforce_gtid_consistency	强制GTID一致性	OFF
gtid_executed	已执行的GTID集	空
gtid_purged	已清除的GTID集	空
gtid_next	下一个GTID值	AUTOMATIC
gtid_executed_compression_period	压缩频率	0（8.0.23+按需压缩）

gtid_next的取值：

AUTOMATIC：自动获取下一个GTID（默认）
ANONYMOUS：匿名事务
UUID:NUMBER：显式设置GTID（用于跳过指定事务）

2.5 GTID复制的限制

需要注意的限制：

CREATE TABLE ... SELECT语句
- MySQL 8.0.21之前不支持
- 8.0.21+作为原子DDL处理
临时表操作
- 事务、存储过程、函数、触发器中不允许CREATE/DROP TEMPORARY TABLE
- MySQL 8.0.13+，binlog格式为ROW或MIXED时限制取消
混合引擎事务
- 不能将InnoDB表和非InnoDB表放在同一事务中操作

2.6 设置gtid_purged注意事项

MySQL 8.0之前：

必须gtid_executed为空
需执行RESET MASTER

MySQL 8.0+：

无需gtid_executed为空
设置的gtid_purged不能包含gtid_executed中未清除的GTID
设置的gtid_purged必须是当前gtid_purged的超集

2.7 在线开启GTID复制

步骤1： 主从都执行（观察警告）

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

观察错误日志，确保无警告生成。

步骤2： 主从都执行

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

步骤3： 主从都执行

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

步骤4： 主从都执行

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

步骤5： 检查状态变量

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

确保为0。

步骤6： 等待从库匿名事务应用完毕

-- 主库查询当前位置
SHOW MASTER STATUS;

-- 从库执行等待
SELECT MASTER_POS_WAIT('mysql-bin.000007', 715);

步骤7： 主从都执行

SET @@GLOBAL.GTID_MODE = ON;

步骤8： 修改配置文件

gtid_mode=ON
enforce_gtid_consistency=ON

步骤9： 将复制模式切换为GTID

STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

2.8 在线关闭GTID复制

反向操作，步骤如下：

步骤1： 切换为位置点复制

STOP SLAVE;
SHOW SLAVE STATUS\G  -- 获取Relay_Master_Log_File和Exec_Master_Log_Pos
CHANGE MASTER TO 
  MASTER_AUTO_POSITION = 0,
  MASTER_LOG_FILE = 'mysql-bin.000006',
  MASTER_LOG_POS = 15688006;
START SLAVE;

步骤2-6： 逐步降级gtid_mode

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
-- 检查gtid_owned为空
SELECT @@GLOBAL.GTID_OWNED;
-- 等待所有GTID事务应用完
SET @@GLOBAL.GTID_MODE = OFF;

步骤7： 修改配置文件

gtid_mode=OFF
enforce_gtid_consistency=OFF

2.9 GTID最佳实践

建议：

新集群直接开启GTID
生产环境在线切换选择低峰期
定期检查gtid_executed和gtid_purged
使用GTID后避免手动操作binlog

监控指标：

Retrieved_Gtid_Set：接收过的GTID集
Executed_Gtid_Set：执行过的GTID集
Auto_Position：是否启用GTID自动定位

第三章半同步复制

3.1 复制模式对比

3.1.1 异步复制（Asynchronous Replication）

MySQL默认的复制模式。

特点：

主库执行完事务立即返回客户端
不关心从库是否接收和处理
性能最好，但可能丢失数据

风险场景：
主库crash时，已提交的事务可能未传输到从库，强制提升从库可能导致数据丢失。

3.1.2 全同步复制（Fully Synchronous Replication）

特点：

主库执行完事务后等待所有从库执行完成
数据一致性最强
性能影响严重

3.1.3 半同步复制（Semisynchronous Replication）

特点：

主库执行完事务后等待至少一个从库接收并写入relay log
平衡性能和安全性
延迟至少一个TCP/IP往返时间

适用场景：
低延迟网络环境。

3.2 半同步复制原理

3.2.1 传统半同步（AFTER_COMMIT）

Client → MySQL Engine
         ↓
    Storage Prepare
         ↓
    Write Binary Log
         ↓
    Storage Commit  ← 引擎层提交
         ↓
    [等待从库ACK]   ← 等待从库反馈
         ↓
    Return to Client

潜在问题：
引擎层提交后，其他会话可见事务。若此时主库crash切换到从库，客户端会发现切换前后数据不一致（切换前可见的数据丢失）。

3.2.2 无损复制（AFTER_SYNC）- MySQL 5.7+

Client → MySQL Engine
         ↓
    Storage Prepare
         ↓
    Write Binary Log
         ↓
    [等待从库ACK]   ← 在提交前等待
         ↓
    Storage Commit
         ↓
    Return to Client

优势：
等待从库反馈的阶段调整到引擎层提交之前，避免数据可见性问题。

控制参数：

-- MySQL 5.7+ 默认值，无损复制
rpl_semi_sync_master_wait_point = AFTER_SYNC

-- 传统半同步
rpl_semi_sync_master_wait_point = AFTER_COMMIT

3.3 安装半同步复制

3.3.1 加载插件

主库：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

验证：

SHOW PLUGINS;
-- 查看rpl_semi_sync_master或rpl_semi_sync_slave状态为ACTIVE

3.3.2 启动半同步复制

主库：

SET GLOBAL rpl_semi_sync_master_enabled = 1;

从库：

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

写入配置文件（永久生效）：

[mysqld]
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

3.3.3 重启从库IO线程

关键步骤：

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

如果不重启IO线程，仍然是异步复制。

3.3.4 验证半同步状态

主库：

SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
-- Value应为ON

从库：

SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
-- Value应为ON

两者都为ON才表示半同步复制正常运行。

3.4 半同步复制参数详解

主库参数

SHOW GLOBAL VARIABLES LIKE '%semi%';

关键参数：

参数	说明	默认值
rpl_semi_sync_master_enabled	是否启用半同步	OFF
rpl_semi_sync_master_timeout	等待超时时间（毫秒）	10000
rpl_semi_sync_master_wait_for_slave_count	需要等待的从库数量	1
rpl_semi_sync_master_wait_no_slave	从库数不足时是否继续半同步	ON
rpl_semi_sync_master_wait_point	等待点位置	AFTER_SYNC

rpl_semi_sync_master_timeout详解：

默认10秒
超时后自动降级为异步复制
错误日志会记录警告
从库恢复后自动升级回半同步
建议根据业务需求调整，不希望降级可设置较大值

rpl_semi_sync_master_wait_for_slave_count详解：

设置主库需要等待多少个从库反馈
默认为1（至少一个从库确认）
1主2从场景，设置为2可提高数据安全性

rpl_semi_sync_master_wait_no_slave详解：

ON（默认）：即使在线从库数不足wait_for_slave_count，状态仍显示ON（半同步）
OFF：在线从库数不足时，立即降级为异步复制

场景示例：

架构：1主2从，设置wait_for_slave_count=2
情况：一个从库挂掉
- wait_no_slave=ON：仍显示半同步（实际已降级）
- wait_no_slave=OFF：立即显示异步复制

从库参数

SHOW GLOBAL VARIABLES LIKE '%semi%';

参数：

rpl_semi_sync_slave_enabled：是否启用半同步
rpl_semi_sync_slave_trace_level：跟踪级别

3.5 半同步复制状态监控

SHOW STATUS LIKE '%Rpl_semi%';

关键指标：

状态变量	说明
Rpl_semi_sync_master_clients	当前半同步从库数量
Rpl_semi_sync_master_status	半同步状态（ON/OFF）
Rpl_semi_sync_master_yes_tx	半同步状态下执行的事务数
Rpl_semi_sync_master_no_tx	异步状态下执行的事务数
Rpl_semi_sync_master_no_times	降级为异步的次数
Rpl_semi_sync_master_tx_avg_wait_time	平均等待时间（微秒）

监控重点：

Rpl_semi_sync_master_clients：应等于预期的半同步从库数
Rpl_semi_sync_master_no_times：频繁增加说明网络或从库有问题
Rpl_semi_sync_master_tx_avg_wait_time：平均等待时间，反映网络延迟

3.6 注意事项

必要条件：

主从都必须开启半同步插件
从库响应条件是将binlog事件写入relay log

超时降级：

默认10秒无响应降级为异步
错误日志记录：Timeout waiting for reply of binlog
从库恢复后自动升级回半同步

适用场景：

低延迟网络环境
数据一致性要求高的业务
可接受适度性能损耗

不适用场景：

跨地域、高延迟网络
对性能极度敏感的业务
从库资源严重不足

第四章多源复制

4.1 多源复制概念

多源复制（Multi-Source Replication）指将多个主库的数据复制到同一个从库上。

应用场景：

数据灾备 - 多个主库的数据集中备份
分库分表汇总 - 将多个分片数据汇总到一个实例进行数据分析
数据聚合 - 多个实例数据聚集到一个实例

4.2 多源复制架构图

┌──────────────┐
│  database 1  │
│   master 1   │──┐
└──────────────┘  │
                  │
┌──────────────┐  │      ┌──────────────┐     ┌──────────────┐
│  database 2  │  │      │              │     │ database 1   │
│   master 2   │──┼─────▶│    slave     │────▶│ database 2   │
└──────────────┘  │      │              │     │ database 3   │
                  │      └──────────────┘     └──────────────┘
┌──────────────┐  │
│  database 3  │  │
│   master 3   │──┘
└──────────────┘

4.3 搭建多源复制

4.3.1 前置要求

参数要求：

master-info-repository = TABLE
relay-log-info-repository = TABLE

说明：

MySQL 8.0默认为TABLE
MySQL 5.7需要显式设置

4.3.2 执行CHANGE MASTER TO

关键： 需显式指定CHANNEL值

-- 为第一个主库建立复制
CHANGE MASTER TO
  MASTER_HOST='192.168.244.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_AUTO_POSITION = 1
FOR CHANNEL 'source_1';

-- 为第二个主库建立复制
CHANGE MASTER TO
  MASTER_HOST='192.168.244.20',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_AUTO_POSITION = 1
FOR CHANNEL 'source_2';

4.4 搭建过程注意事项

4.4.1 主库复制模式可以不同

灵活性：

可以一个主库是GTID复制
另一个主库是基于binlog位置点的复制

要求：
从库的GTID_MODE必须设置为ON_PERMISSIVE或OFF_PERMISSIVE（不能是ON或OFF）。

4.4.2 基于现有主库搭建多源复制

备份工具选择：

全部使用逻辑备份 - mysqldump
第一个主库使用Xtrabackup - 物理备份

为什么不能全部使用Xtrabackup？

Xtrabackup是物理备份，拷贝所有文件
不同实例的系统表（ibdata）会冲突

4.4.3 GTID_PURGED设置问题

MySQL 8.0之前的问题：

导入第二个主库时报错：

ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决方案：

-- 从库执行
RESET MASTER;  -- 导入第一个主库后执行
-- 导入第二个主库
RESET MASTER;  -- 导入第二个主库后执行

-- 最后统一设置GTID_PURGED（取各主库GTID_PURGED的并集）
SET @@GLOBAL.GTID_PURGED='0ed33867-d1b0-11e8-8f58-000c2914fb06:1-9,ff693ae0-d1b2-11e8-b732-000c2927cfea:1-4';

MySQL 8.0的改进：

支持追加GTID集：

SET GLOBAL GTID_PURGED='+d7664b79-e0ee-11e8-b1ad-000c2927cfea:1';

mysqldump输出格式：

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '453a5124-020e-11ec-8719-000c29f66609:1-12';

注释说明：

/*!80000 '+'*/ 是版本控制注释
只有MySQL 8.0.00及以上版本才会执行'+'

4.4.4 复制mysql库的风险

强烈建议：

如果binlog格式是ROW，不建议复制mysql库
容易导致主从复制中断

4.5 多源复制管理

管理操作需指定CHANNEL：

-- 查看特定通道的状态
SHOW SLAVE STATUS FOR CHANNEL 'master-1'\G

-- 启动特定通道
START SLAVE FOR CHANNEL 'master-1';

-- 停止特定通道
STOP SLAVE FOR CHANNEL 'master-1';

-- 重置特定通道
RESET SLAVE FOR CHANNEL 'master-1';

-- 完全移除通道
RESET SLAVE ALL FOR CHANNEL 'master-1';

不指定CHANNEL：

默认对所有CHANNEL生效

查看所有通道：

SELECT * FROM performance_schema.replication_connection_configuration;

4.6 多源复制最佳实践

建议：

为每个通道使用有意义的命名（如：业务名称）
定期监控各通道的复制状态
合理规划从库资源（CPU、内存、磁盘）
避免多个主库同时写入相同的表

监控脚本示例：

-- 检查所有通道的IO和SQL线程状态
SELECT 
  CHANNEL_NAME,
  SERVICE_STATE AS IO_State,
  (SELECT SERVICE_STATE 
   FROM performance_schema.replication_applier_status_by_worker w
   WHERE w.CHANNEL_NAME = c.CHANNEL_NAME LIMIT 1) AS SQL_State
FROM performance_schema.replication_connection_status c;

第五章多线程复制、过滤复制和延迟复制

5.1 多线程复制（Parallel Replication）

5.1.1 配置参数

从库设置：

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON

参数详解：

slave_parallel_type（并行复制类型）

DATABASE：基于库级别的并行（MySQL 8.0.27前默认值）
LOGICAL_CLOCK：基于组提交的并行复制（推荐）

slave_parallel_workers（工作线程数）

设置Worker线程数量
原来的SQL线程变为1个Coordinator + N个Worker
推荐设置：16（根据CPU核心数调整）

slave_preserve_commit_order（保持提交顺序）

ON：事务在从库提交顺序与主库一致（推荐）
OFF：不保证顺序

注意： 调整这三个参数需重启复制生效。

5.1.2 WRITESET并行复制（MySQL 8.0+）

主库设置（进一步提升并行度）：

binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format = ROW

说明：

基于WRITESET的并行复制只在ROW格式下生效
通过检测事务修改的行集合来判断并行性
比LOGICAL_CLOCK有更高的并行度

5.1.3 并行复制监控

-- 查看Worker线程状态
SHOW PROCESSLIST;

-- 查看并行复制统计
SELECT * FROM performance_schema.replication_applier_status_by_worker;

5.2 过滤复制（Replication Filters）

5.2.1 CHANGE REPLICATION FILTER语法

CHANGE REPLICATION FILTER filter[, filter] [FOR CHANNEL channel]

filter: {
  REPLICATE_DO_DB = (db_list)
| REPLICATE_IGNORE_DB = (db_list)
| REPLICATE_DO_TABLE = (tbl_list)
| REPLICATE_IGNORE_TABLE = (tbl_list)
| REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
| REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
| REPLICATE_REWRITE_DB = (db_pair_list)
}

5.2.2 过滤规则类型

库级别过滤：

REPLICATE_DO_DB：只复制指定库
REPLICATE_IGNORE_DB：忽略指定库

表级别过滤：

REPLICATE_DO_TABLE：只复制指定表
REPLICATE_IGNORE_TABLE：忽略指定表

通配符过滤：

REPLICATE_WILD_DO_TABLE：支持正则匹配
REPLICATE_WILD_IGNORE_TABLE：支持正则匹配

库名重写：

REPLICATE_REWRITE_DB：将主库A库重写到从库B库

5.2.3 实际示例

只复制指定库：

CHANGE REPLICATION FILTER
  REPLICATE_DO_DB = (db1), 
  REPLICATE_IGNORE_DB = (db2);

只复制特定通道的指定库：

CHANGE REPLICATION FILTER 
  REPLICATE_DO_DB = (db1) 
FOR CHANNEL channel_1;

通配符过滤：

-- 只复制db1中old开头的表
CHANGE REPLICATION FILTER
  REPLICATE_WILD_DO_TABLE = ('db1.old%');

-- 忽略多个库中new开头的表
CHANGE REPLICATION FILTER
  REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');

库名重写：

-- 将主库dbA的操作重写到从库dbB，dbC重写到dbD
CHANGE REPLICATION FILTER
  REPLICATE_REWRITE_DB = ((dbA, dbB), (dbC, dbD));

清除过滤规则：

CHANGE REPLICATION FILTER
  REPLICATE_DO_DB = (), 
  REPLICATE_IGNORE_DB = ();

5.2.4 过滤复制注意事项

重要提示：

过滤规则写入配置文件持久化
修改后立即生效，无需重启
FOR CHANNEL指定特定通道（多源复制场景）

查看当前过滤规则：

SHOW SLAVE STATUS\G
-- 查看Replicate_Do_DB、Replicate_Ignore_DB等字段

5.3 延迟复制（Delayed Replication）

5.3.1 配置延迟复制

搭建时设置：

CHANGE MASTER TO
  MASTER_HOST='192.168.79.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=155,
  MASTER_DELAY=28800;  -- 延迟8小时（单位：秒）

运行时设置：

STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 28800;
START SLAVE;

5.3.2 延迟复制状态查看

SHOW SLAVE STATUS\G

关键字段：

SQL_Delay：期望延迟时间（MASTER_DELAY设置值）
SQL_Remaining_Delay：当前暂停的事务还需等待多久
Slave_SQL_Running_State：Waiting until MASTER_DELAY seconds after master executed event

5.3.3 延迟复制应用场景

主要用途：

误操作恢复 - 延迟8小时，有充足时间发现并恢复误操作
数据回滚 - 可随时停止复制，获取延迟时间点的数据
测试验证 - 模拟特定时间点的数据状态

最佳实践：

建议延迟1-8小时
配合监控告警及时发现问题
延迟从库不对外提供服务

第六章复制的常见管理操作

6.1 查看主库状态

SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

关键字段：

File：当前binlog文件
Position：当前binlog位置点

6.2 查看从库复制状态

SHOW SLAVE STATUS\G

核心字段说明：

连接信息：

Master_Host：主库IP/主机名
Master_User：复制用户
Master_Port：主库端口

位置点信息：

Master_Log_File：IO线程读取的主库binlog文件
Read_Master_Log_Pos：IO线程读取的主库binlog位置
Relay_Master_Log_File：SQL线程执行的主库binlog文件
Exec_Master_Log_Pos：SQL线程执行的主库binlog位置

运行状态：

Slave_IO_Running：IO线程状态（Yes/No/Connecting）
Slave_SQL_Running：SQL线程状态（Yes/No）

错误信息：

Last_IO_Error：IO线程最后的错误
Last_SQL_Error：SQL线程最后的错误

延迟信息：

Seconds_Behind_Master：从库延迟秒数
SQL_Delay：延迟复制的期望延迟时间
SQL_Remaining_Delay：还需等待的时间

GTID信息：

Retrieved_Gtid_Set：接收过的GTID集
Executed_Gtid_Set：执行过的GTID集
Auto_Position：是否启用GTID自动定位

6.3 建立复制（CHANGE MASTER TO）

完整语法（关键选项）：

CHANGE MASTER TO option [, option] ... [FOR CHANNEL channel]

option: {
  MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_LOG_FILE = 'source_log_name'
| MASTER_LOG_POS = source_log_pos
| MASTER_AUTO_POSITION = {0|1}
| MASTER_DELAY = interval
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| GET_MASTER_PUBLIC_KEY = {0|1}
}

修改复制配置：

-- 只修改密码
CHANGE MASTER TO MASTER_PASSWORD = '123456';

-- 重放指定relay log位置
CHANGE MASTER TO
  RELAY_LOG_FILE = 'node2-relay-bin.000004',
  RELAY_LOG_POS = 744;

6.4 启动复制（START SLAVE）

完整语法：

START SLAVE [thread_types] [until_option] [connection_options] [FOR CHANNEL channel]

thread_types:
  [IO_THREAD] [SQL_THREAD]

until_option:
  UNTIL {
    {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
  | MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
  | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
  | SQL_AFTER_MTS_GAPS
  }

常见用法：

启动所有线程：

START SLAVE;

只启动IO线程：

START SLAVE IO_THREAD;

只启动SQL线程：

START SLAVE SQL_THREAD;

应用到指定位置后停止（until_option）：

GTID场景：

-- 在应用到指定GTID前停止（不包括）
START SLAVE UNTIL SQL_BEFORE_GTIDS = 'bd6b3216-04d6-11ec-b76f-000c292c1f7b:31';

-- 在应用完指定GTID后停止（包括）
START SLAVE UNTIL SQL_AFTER_GTIDS = 'bd6b3216-04d6-11ec-b76f-000c292c1f7b:39';

位置点场景：

-- 基于主库binlog位置
START SLAVE UNTIL 
  MASTER_LOG_FILE = 'mysql-bin.000006',
  MASTER_LOG_POS = 15688006;

-- 基于从库relay log位置
START SLAVE UNTIL 
  RELAY_LOG_FILE = './node2-relay-bin.000004',
  RELAY_LOG_POS = 744;

注意事项：

until_option只对SQL线程有效
GTID复制也可指定位置点
执行STOP SLAVE会清空until_option

6.5 停止复制（STOP SLAVE）

STOP SLAVE [IO_THREAD | SQL_THREAD] [FOR CHANNEL channel]

示例：

-- 停止所有线程
STOP SLAVE;

-- 只停止IO线程
STOP SLAVE IO_THREAD;

-- 只停止SQL线程
STOP SLAVE SQL_THREAD;

6.6 查看主库的binlog列表

SHOW BINARY LOGS;

输出示例：

+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       180 | No        |
| mysql-bin.000002 |       425 | No        |
| mysql-bin.000003 |       180 | No        |
+------------------+-----------+-----------+

6.7 删除Binlog

语法：

PURGE BINARY LOGS {TO 'log_name' | BEFORE datetime_expr}

示例：

-- 删除mysql-bin.000004之前的binlog
PURGE BINARY LOGS TO 'mysql-bin.000004';

-- 删除指定时间点之前的binlog
PURGE BINARY LOGS BEFORE '2024-01-14 09:10:20';

注意事项：

第一个示例只会删除000004之前的binlog（不包括000004）
GTID复制中，PURGE操作会同步修改gtid_purged的值
确保从库已应用完对应的binlog再删除

6.8 切换日志

FLUSH BINARY LOGS;

关闭当前binlog，打开一个新的binlog。

6.9 查看Binlog内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

示例：

-- 查看指定binlog文件内容
SHOW BINLOG EVENTS IN 'mysql-bin.000004';

-- 从指定位置开始查看
SHOW BINLOG EVENTS IN 'mysql-bin.000004' FROM 157;

-- 限制输出行数
SHOW BINLOG EVENTS IN 'mysql-bin.000004' LIMIT 10;

6.10 跳过指定事务

6.10.1 基于位置点的复制

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

跳过1个事务。

6.10.2 GTID复制

注入空事务方式：

-- 假设需要跳过的GTID为：bd6b3216-04d6-11ec-b76f-000c292c1f7b:11691

STOP SLAVE;
SET SESSION gtid_next='bd6b3216-04d6-11ec-b76f-000c292c1f7b:11691';
BEGIN;
COMMIT;
SET SESSION gtid_next='AUTOMATIC';
START SLAVE;

关键： gtid_next取Executed_Gtid_Set的最大值+1。

6.11 操作不写入Binlog

SET SESSION sql_log_bin=0;
-- 执行操作
-- ...
SET SESSION sql_log_bin=1;

应用场景：

从库上的临时数据修复
不希望记录到binlog的管理操作

6.12 判断主库操作是否已在从库执行

位置点复制

使用MASTER_POS_WAIT函数：

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos [, timeout][, channel]);

示例：

-- 主库查询当前位置
SHOW MASTER STATUS;
-- 假设输出：mysql-bin.000007, 715

-- 从库执行（会阻塞直到达到指定位置或超时）
SELECT MASTER_POS_WAIT('mysql-bin.000007', 715);
-- 返回值：重放的事件数，-1表示超时

GTID复制

使用WAIT_FOR_EXECUTED_GTID_SET函数：

SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid_set[, timeout]);

示例：

SELECT WAIT_FOR_EXECUTED_GTID_SET('bd6b3216-04d6-11ec-b76f-000c292c1f7b:1-100', 10);

6.13 RESET MASTER、RESET SLAVE和RESET SLAVE ALL的区别

6.13.1 RESET MASTER

作用：

删除所有binlog，从头生成新binlog
清空binlog索引文件
GTID复制中，清空gtid_executed、gtid_purged及mysql.gtid_executed表

注意：

正常主从环境中，不要在主库执行RESET MASTER
容易导致主从中断，甚至数据不一致

指定起始序号：

RESET MASTER TO 100;  -- 第一个binlog为mysql-bin.000100

6.13.2 RESET SLAVE

作用：

删除所有relay log，生成新relay log
清空relay log索引文件
清空mysql.slave_relay_log_info表
清除mysql.slave_master_info表中binlog位置点信息
保留连接信息

注意：

执行前必须停止复制
可直接START SLAVE重启复制
GTID复制中没问题，位置点复制会从主库第一个binlog重新拉取（危险）

6.13.3 RESET SLAVE ALL

作用：

相对于RESET SLAVE，额外清空mysql.slave_master_info表的所有内容（包括连接信息）

注意：

重启复制只能执行CHANGE MASTER TO
不会清除GTID相关信息

对比总结：

操作	删除relay log	清除位置点	清除连接信息	清除GTID信息
RESET SLAVE	是	是	否	否
RESET SLAVE ALL	是	是	是	否

第七章 MySQL主从延迟原因分析与解决

7.1 Seconds_Behind_Master计算逻辑

7.1.1 实现原理

计算公式：

Seconds_Behind_Master = 从库当前系统时间 
                      - SQL线程当前重放事务在主库的开始时间戳
                      - 主从系统时间差

关键变量：

time(0)：从库当前系统时间
mi->rli->last_master_timestamp：SQL线程当前重放事务在主库的开始时间戳（binlog中事件的时间戳）
mi->clock_diff_with_master：IO线程启动时主从系统时间差

7.1.2 Seconds_Behind_Master为0的条件

SQL线程已重放完所有relay log
Slave_IO_Running为Yes

7.1.3 Seconds_Behind_Master为NULL的情况

Slave_SQL_Running为No
Slave_SQL_Running为Yes，但重放完所有relay log时Slave_IO_Running不为Yes

7.1.4 注意事项

重要提示：

IO线程启动后调整过系统时间，需重启复制
否则会影响Seconds_Behind_Master计算结果
MySQL 8.0.14+，binlog中的时间戳是事务开始执行时间（之前是记录日志时间）

7.2 主从延迟分析方法

7.2.1 系统资源检查

CPU监控：

top

关键指标：

%us：用户态CPU时间占比
%sy：内核态CPU时间占比
%id：空闲CPU时间占比
%wa：等待IO的CPU时间占比

判断标准：

CPU使用率（1-%id）超过90%需关注
数据库应用CPU很少成为瓶颈，除非有大量慢SQL

磁盘IO监控：

iostat -xm 1

关键指标：

r/s：每秒读请求数
w/s：每秒写请求数
rMB/s：每秒读取数据量
wMB/s：每秒写入数据量
await：IO请求平均耗时（包含队列等待时间）
%util：磁盘饱和度

重点关注：

await：反映磁盘响应速度
%util：接近100%可能表示瓶颈（RAID/SSD除外）

7.2.2 主从复制状态检查

主库：

SHOW MASTER STATUS;

关注：File和Position

从库：

SHOW SLAVE STATUS\G

关键对比：

第一对（IO线程延迟）：

(File, Position) vs (Master_Log_File, Read_Master_Log_Pos)
前者大于后者，说明IO线程存在延迟

第二对（SQL线程延迟）：

(Master_Log_File, Read_Master_Log_Pos) vs (Relay_Master_Log_File, Exec_Master_Log_Pos)
前者大于后者，说明SQL线程存在延迟

7.2.3 主库binlog写入量

监控方法：

查看binlog生成速度（如多少分钟生成一个）
评估主库写入压力

7.3 IO线程延迟原因与解决

7.3.1 网络延迟

判断方法：

检查网络带宽是否受限

解决方案：

SET GLOBAL slave_compressed_protocol = ON;

启用binlog压缩传输。

7.3.2 磁盘IO瓶颈

解决方案：

调整从库的双一设置
从库关闭binlog

注意：

MySQL 5.6中开启GTID强制要求开启binlog
MySQL 5.7+无此限制

7.3.3 网卡问题

现象：

一台主机上的所有从库都延迟
其他集群的从库正常

排查方法：

scp large_file remote_host:/tmp/

测试网络传输速度。

解决方案：

联系系统管理员检查网卡
更换网卡或主机

7.4 SQL线程延迟原因与解决

7.4.1 主库写入量过大，SQL线程单线程重放

现象：

从库磁盘IO无明显瓶颈
Relay_Master_Log_File, Exec_Master_Log_Pos持续变化
主库写入量过大（如SATA SSD下binlog生成速度快于5分钟一个）

解决方案：

# 开启并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON

# 主库配置（提升并行度）
binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format = ROW

7.4.2 STATEMENT格式下的慢SQL

现象：

一段时间内Relay_Master_Log_File, Exec_Master_Log_Pos没有变化

案例：

-- 表无索引，1000万数据
DELETE FROM sbtest.sbtest1 WHERE id <= 100;
-- 主库执行：7.52秒
-- Seconds_Behind_Master最大：7秒

解决方案：

优化SQL（添加索引）
开启慢查询日志记录从库重放慢SQL：

SET GLOBAL log_slow_slave_statements = ON;

7.4.3 表无索引且binlog格式为ROW

现象：

Relay_Master_Log_File, Exec_Master_Log_Pos长时间不变化

原理：

主库操作只需一次全表扫描
从库ROW格式下，每条记录操作都需全表扫描

案例：

-- 表无索引，1000万数据，binlog_format=ROW
DELETE FROM sbtest.sbtest1 WHERE id <= 100;
-- 主库执行：7.53秒
-- Seconds_Behind_Master最大：723秒（100倍差距！）

解决方案：

方案1：在从库临时添加索引

-- 选择区分度高的列添加索引
ALTER TABLE sbtest.sbtest1 ADD INDEX idx_id(id);

方案2：使用HASH_SCAN算法

-- MySQL 5.7+支持
SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';

效果对比：

无优化：Seconds_Behind_Master最大723秒
使用HASH_SCAN：Seconds_Behind_Master最大53秒

7.4.4 大事务

定义：

ROW格式下操作涉及记录数较多的事务

测试数据（1000万数据表，有主键）：

记录数	主库执行时长(s)	Seconds_Behind_Master最大(s)
50000	0.76	1
200000	3.10	8
500000	17.32	39
1000000	63.47	122

结论：
记录数越多，延迟越严重。

解决方案：

-- 分批执行，每次处理小批量
UPDATE sbtest.sbtest1 SET c=repeat('X',120) WHERE id<=1000;
UPDATE sbtest.sbtest1 SET c=repeat('X',120) WHERE id>1000 AND id<=2000;
-- ...

7.4.5 从库上有查询操作

影响：

消耗系统资源
锁等待

典型场景：
从库的查询操作堵塞主库的DDL操作

示例：

-- 从库processlist
| 18 | root        | localhost | NULL | Query   |   19 | User sleep              
     | select id,sleep(1) from sbtest.sbtest1 |
| 20 | system user |           |      | Query   |   13 | Waiting for table metadata lock 
     | alter table sbtest.sbtest1 add c2 int |

解决方案：

控制从库查询负载
使用独立的分析库
设置查询超时时间

7.4.6 从库上存在备份

典型场景：
备份的全局读锁阻塞SQL线程重放

示例：

-- 从库processlist
| 20 | system user |           |      | Query   |    2 | Waiting for global read lock 
     | alter table sbtest.sbtest1 add c1 int |
| 28 | root        | localhost | NULL | Query   |   17 | Waiting for table flush       
     | flush tables with read lock |

解决方案：

使用Xtrabackup等不加锁的备份工具
备份时避开业务高峰期
从库使用--single-transaction备份

7.4.7 磁盘IO瓶颈

解决方案：

调整从库的双一设置：

SET GLOBAL sync_binlog = 1000;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

从库关闭binlog：

disable_log_bin = 1

7.5 主从延迟总结

延迟原因分类图：

主从延迟的常见原因及解决方法
├── IO线程延迟
│   ├── 网络延迟 → 开启slave_compressed_protocol
│   ├── 磁盘IO存在瓶颈 → 调整双一设置或关闭binlog
│   └── 网卡存在问题 → 可通过scp传输文件测试确认
│
└── SQL线程延迟
    ├── 主库写入量过大 → 开启并行复制
    ├── STATEMENT格式下的慢SQL → 开启log_slow_slave_statements，优化SQL
    ├── ROW格式且表无索引 → 添加索引或设置slave_rows_search_algorithms
    ├── 大事务 → 分批执行
    ├── 从库上有查询操作 → 控制查询负载
    ├── 从库上存在备份 → 使用不加锁备份工具，选择低峰期
    └── 磁盘IO存在瓶颈 → 调整双一设置或关闭binlog

第八章 MySQL日志体系

8.1 错误日志（Error Log）

8.1.1 配置参数

SHOW VARIABLES LIKE 'log_error';
-- 输出：/data/mysql/3306/data/mysqld.err

关键参数：

参数	说明	默认值
log_error	错误日志文件路径
log_timestamps	日志时间戳时区	UTC
log_error_verbosity	日志详细程度	2

log_error_verbosity级别：

级别	记录的消息类型
1	ERROR
2	ERROR, WARNING
3	ERROR, WARNING, INFORMATION

建议配置：

log_timestamps = SYSTEM  # 使用系统时区，便于阅读
log_error_verbosity = 2  # 记录错误和警告

8.1.2 查看错误日志

方法1：直接查看文件

tail -f /data/mysql/3306/data/mysqld.err

方法2：通过performance_schema查询

SELECT * FROM performance_schema.error_log 
ORDER BY LOGGED DESC LIMIT 10;

8.2 通用查询日志（General Query Log）

8.2.1 配置参数

SHOW VARIABLES LIKE '%general%';

关键参数：

general_log：是否开启（默认OFF）
general_log_file：日志文件路径

注意：

记录所有查询语句（包括连接、断开连接）
对性能有负面影响
生产环境不建议开启

8.2.2 启用通用查询日志

SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/data/mysql/3306/data/mysql.log';

适用场景：

故障排查
审计需求
开发测试环境

8.3 慢查询日志（Slow Query Log）

8.3.1 配置参数

slow_query_log = ON
slow_query_log_file = /data/mysql/3306/data/mysql-slow.log
long_query_time = 1  # 阈值1秒

额外参数：

log_queries_not_using_indexes：记录未使用索引的查询
log_slow_admin_statements：记录管理命令（ALTER TABLE等）
log_slow_slave_statements：记录从库重放的慢SQL（MySQL 5.6.11+）
min_examined_row_limit：检查行数少于此值不记录
log_throttle_queries_not_using_indexes：限制未使用索引查询的记录频率（每分钟）
log_slow_extra：记录更详细的性能指标（MySQL 8.0.14+）

8.3.2 启用慢查询日志

动态启用：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

永久配置：

[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mysql/3306/data/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_slow_slave_statements = ON

8.3.3 慢查询日志分析

工具： mysqldumpslow、pt-query-digest

# 查看慢查询最多的10条SQL
mysqldumpslow -s c -t 10 /data/mysql/3306/data/mysql-slow.log

# 使用pt-query-digest分析
pt-query-digest /data/mysql/3306/data/mysql-slow.log > slow_report.txt

8.3.4 注意事项

重要提示：

MySQL 8.0.14+，慢查询日志中的时间戳是SQL开始执行的时间
MySQL 8.0.14之前，时间戳是SQL记录到日志的时间（执行结束后）

8.4 二进制日志（Binary Log）

8.4.1 配置参数

log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7  # MySQL 5.7
binlog_expire_logs_seconds = 604800  # MySQL 8.0，7天
max_binlog_size = 1073741824  # 1GB
sync_binlog = 1

binlog_format选项：

STATEMENT：记录SQL语句
ROW：记录行变化（推荐）
MIXED：混合模式

binlog_row_image选项：

FULL：记录所有列（默认）
MINIMAL：只记录变化的列

sync_binlog说明：

0：不主动刷新，由操作系统决定
1：每次事务提交刷新（最安全，推荐）
N：每N个事务刷新一次

8.4.2 binlog管理命令

查看binlog列表：

SHOW BINARY LOGS;

查看当前binlog：

SHOW MASTER STATUS;

查看binlog内容：

SHOW BINLOG EVENTS IN 'mysql-bin.000001';

切换binlog：

FLUSH BINARY LOGS;

删除binlog：

PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

8.4.3 binlog重要参数（MySQL 8.0+）

binlog_rows_query_log_events：

SET GLOBAL binlog_rows_query_log_events = ON;

ROW格式下记录原始SQL语句，便于分析。

binlog_transaction_dependency_tracking：

-- 提升并行复制效率
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET_SESSION';

8.5 Relay Log（中继日志）

8.5.1 配置参数

SHOW VARIABLES LIKE '%relay%';

关键参数：

参数	说明	默认值
relay_log	relay log文件名	mysql-relay-bin
relay_log_basename	relay log完整路径
relay_log_index	relay log索引文件
relay_log_info_repository	存储位置	TABLE
relay_log_purge	自动清理	ON
relay_log_recovery	启动时恢复	OFF
max_relay_log_size	最大大小	0（使用max_binlog_size）
sync_relay_log	刷新频率	10000
sync_relay_log_info	信息刷新频率	10000

建议配置：

relay_log_info_repository = TABLE
relay_log_purge = ON
relay_log_recovery = ON  # 从库异常重启后自动恢复

8.6 日志输出控制

log_output参数：

SET GLOBAL log_output = 'FILE';  -- 输出到文件
SET GLOBAL log_output = 'TABLE';  -- 输出到表
SET GLOBAL log_output = 'FILE,TABLE';  -- 同时输出
SET GLOBAL log_output = 'NONE';  -- 禁用

注意：

影响General Log和Slow Query Log
不影响Error Log和Binary Log

第九章 MySQL备份策略

9.1 备份分类

9.1.1 物理备份 VS 逻辑备份

物理备份

优点：

备份、恢复速度快（尤其恢复速度，直接关系RTO）
无需实例在线（冷备场景）

缺点：

备份文件大
恢复时对平台、操作系统、MySQL版本有要求（必须一致或兼容）
只能本地发起备份
无法收缩空间（即使存在大量"空洞"）
无法备份MEMORY表

逻辑备份

优点：

可移植性强（跨平台、跨版本）
灵活（可只恢复特定库或表）
对表存储引擎无要求
备份文件较小
可远程发起备份
恢复后能有效收缩空间

缺点：

备份、恢复速度慢（尤其恢复速度）
备份会"污染"Buffer Pool（热点数据被驱逐）

9.1.2 离线备份 VS 在线备份

离线备份（冷备）

实例关闭状态下进行
只能物理备份
对业务影响大

在线备份（热备）

实例运行过程中进行
可物理备份或逻辑备份
线上一般使用在线备份

9.1.3 全量备份 VS 增量备份

全量备份

备份整个实例的全量数据

增量备份

只备份上次备份以来发生"变化"的数据

实现方式：

物理备份：判断数据页LSN是否变化（如XtraBackup）
逻辑备份：较难实现真正的增量（基于时间字段不可靠）

9.2 MySQL备份工具

9.2.1 物理备份工具

XtraBackup

Percona公司开源
适用于MySQL、MariaDB、Percona Server
官网：https://www.percona.com/downloads#percona-xtrabackup

版本：

XtraBackup 2.4：适用于MySQL 5.6和5.7
XtraBackup 8.0：适用于MySQL 8.0

mysqlbackup

MySQL企业级备份工具（MySQL Enterprise Backup）
适用于MySQL企业版（收费）

Clone Plugin

MySQL 8.0.17引入
内置克隆插件

实现原理：
三者基本相同，都是在备份过程中拷贝物理文件和redo log，最后利用InnoDB Crash Recovery恢复到备份结束时的一致性状态。

9.2.2 逻辑备份工具

mysqldump

MySQL安装包自带
单线程备份
最常用

mydumper

多线程逻辑备份工具
支持行级别并行备份
GitHub：https://github.com/maxbube/mydumper

mysqlpump

MySQL 5.7引入
支持表级别并行备份

MySQL Shell

MySQL 8.0.21引入util.dumpInstance()
支持行级别并行备份
要求：备份实例 >= 5.6，恢复实例 >= 5.7

9.2.3 逻辑备份工具对比

实现原理：
所有逻辑备份工具本质都是通过SELECT * FROM TABLE备份数据，通过全局读锁 + REPEATABLE READ隔离级别实现一致性备份。

导出格式差异：

mysqldump、mydumper、mysqlpump：INSERT语句格式

INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');

MySQL Shell：CSV格式

1,aaa
2,bbb
3,ccc

恢复工具：

mysqldump、mysqlpump → mysql客户端（单线程恢复）
mydumper → myloader（多线程恢复）
util.dumpInstance() → util.loadDump()（多线程恢复，使用LOAD DATA LOCAL INFILE）

9.3 备份策略建议

9.3.1 全量 + 增量备份策略

推荐策略：

每周一次全量物理备份（如周日凌晨）
每天一次增量物理备份
实时备份binlog

恢复流程：

恢复最近的全量备份
依次应用增量备份
应用binlog到指定时间点

9.3.2 备份时间选择

建议：

业务低峰期（如凌晨2-4点）
避开批处理任务时间
从库备份，避免影响主库

9.3.3 备份验证

必须定期验证：

备份文件完整性校验
定期恢复演练
恢复时间测试（RTO评估）

9.3.4 备份存储

多副本策略：

本地保留最近3-7天备份
远程存储长期备份
异地灾备（跨地域）

第十章 mysqldump使用详解

10.1 常见用法

10.1.1 全库备份

mysqldump --all-databases \
  --master-data=2 \
  --single-transaction \
  --triggers \
  --routines \
  --events \
  > all_databases.sql

关键参数说明：

--all-databases：备份所有库
--master-data=2：记录binlog位置点（注释形式）
--single-transaction：一致性快照（仅适用于InnoDB）
--triggers：备份触发器
--routines：备份存储过程和函数
--events：备份事件调度器

注意：

--master-data=2 --single-transaction只能保证事务表（InnoDB）的一致性
如需保证非事务表（MyISAM）一致性，需使用--lock-all-tables

10.1.2 备份指定库

mysqldump --databases db1 db2 --result-file=db1_db2.sql

注意：
备份每个db时，会对该库下所有表执行锁表操作。

10.1.3 备份指定表

多种写法：

# 方式1
mysqldump --databases db1 --tables t1 t2 > mysql_backup.sql

# 方式2
mysqldump db1 t1 t2 > mysql_backup.sql

# 方式3
mysqldump db1 --tables t1 t2 > mysql_backup.sql

限制：

不支持db_name.tbl_name格式
无法同时备份不同库的多张表

10.1.4 压缩备份

mysqldump --all-databases --master-data=2 --single-transaction | gzip > all_databases.gz

恢复压缩备份：

gunzip < all_databases.gz | mysql

10.1.5 忽略指定表

mysqldump --databases db1 db2 \
  --ignore-table=db1.t1 \
  --ignore-table=db2.t3 \
  > mysql_backup.sql

注意：
--ignore-table后面只能接一张表，忽略多张表需指定多次。

10.1.6 导出表的部分数据

mysqldump db1 t1 --where='id=1' > db1_t1.sql

对应SQL：

SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` WHERE id=1

10.1.7 导入备份文件

mysql < mysql_backup.sql

10.1.8 本地备份，远程导入

mysqldump --databases db1 | mysql -h 192.168.79.10 -uroot -p123456

10.2 mysqldump实现原理

10.2.1 核心流程

开启general log跟踪：

SET GLOBAL general_log=ON;

执行备份：

mysqldump --all-databases --master-data=2 --single-transaction --triggers --routines --events > all_databases.sql

关键步骤：

FLUSH /!40101 LOCAL / TABLES
- 关闭所有打开的表
- 强制关闭所有正在使用的表
- 刷新Prepared Statement缓存
FLUSH TABLES WITH READ LOCK
- 加全局读锁
- 先执行FLUSH TABLE可大大降低被阻塞的时间
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
- 设置隔离级别为RR（可重复读）
START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /
- 开启一致性快照
- 等价于：开启事务 + 对所有表执行一次SELECT
- 保证备份时任意时间点的数据与快照时刻一致
- 只在RR隔离级别下有效
SHOW MASTER STATUS
- 记录当前binlog位置点
- 由--master-data决定
UNLOCK TABLES
- 释放全局读锁

备份数据：

-- 核心备份语句
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

SAVEPOINT机制：

每备份一张表前设置SAVEPOINT
备份完成后回滚到SAVEPOINT
目的：释放已备份表的元数据锁，允许DDL操作

10.3 总结

mysqldump特点：

通过SELECT * FROM TABLE实现数据备份
单线程备份
START TRANSACTION WITH CONSISTENT SNAPSHOT保证一致性，但会导致回滚段增大（DML操作会保存前镜像）
备份文件可通过mysql客户端导入
如果开启GTID且备份时未指定--set-gtid-purged=OFF，生成的备份文件中会将SQL_LOG_BIN设置为0

最佳实践：

在从库备份，避免影响主库
定期验证备份可用性
生产环境考虑使用多线程备份工具（mydumper或MySQL Shell）

第十一章克隆插件(Clone Plugin)

11.1 Clone Plugin概述

Clone Plugin是MySQL 8.0.17引入的重大特性。

核心价值：

一条命令快速添加新节点
适用于Group Replication和普通主从复制
极大简化运维操作

11.2 安装Clone Plugin

11.2.1 动态加载

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

11.2.2 配置文件加载

[mysqld]
plugin-load-add=mysql_clone.so

11.2.3 验证安装

SHOW PLUGINS;
-- 查看clone插件Status为ACTIVE

11.3 本地克隆（Local Clone）

11.3.1 概念

将本地MySQL实例的数据拷贝到本地服务器的一个目录中。

架构图：

┌─────────────────────────┐
│  Local MySQL Server     │
│       Instance          │
│                         │
│  CLONE LOCAL ...        │
│         │               │
│         ▼               │
│  ┌─────────────┐        │
│  │    Data     │        │
│  │  Directory  │        │
│  └─────────────┘        │
│                         │
│         ↓               │
│  ┌─────────────┐        │
│  │    Clone    │        │
│  │  Directory  │        │
│  └─────────────┘        │
└─────────────────────────┘

11.3.2 操作步骤

1. 创建克隆用户：

CREATE USER 'clone_user'@'%' IDENTIFIED BY 'clone_pass';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';

说明：
BACKUP_ADMIN权限允许执行LOCK INSTANCE FOR BACKUP命令。

2. 创建克隆目录：

mkdir -p /data/backup
chown -R mysql:mysql /data/backup/

3. 执行本地克隆：

CLONE LOCAL DATA DIRECTORY='/data/backup/3307';

克隆目录要求：

必须是绝对路径
父目录（/data/backup）必须存在且MySQL有写权限
最后一级目录（3307）不能存在

4. 基于备份集启动实例：

/usr/local/mysql/bin/mysqld --no-defaults \
  --datadir=/data/backup/3307 \
  --user=mysql \
  --port=3307 &

11.4 远程克隆（Remote Clone）

11.4.1 概念

将远程MySQL实例（Donor）的数据克隆到本地实例（Recipient）。

架构图：

┌─────────────────────┐   CLONE INSTANCE ...   ┌─────────────────────┐
│  Local MySQL Server │  ──────────────────▶   │  Remote MySQL Server│
│     Instance        │                         │      Instance       │
│   (Recipient)       │                         │      (Donor)        │
│                     │                         │                     │
│  ┌───────────────┐  │                         │  ┌───────────────┐  │
│  │     Data      │  │◀────────────────────────│  │     Data      │  │
│  │   Directory   │  │                         │  │   Directory   │  │
│  │  (Recipient)  │  │                         │  │    (Donor)    │  │
│  └───────────────┘  │                         │  └───────────────┘  │
└─────────────────────┘                         └─────────────────────┘

11.4.2 操作步骤

1. Donor实例创建克隆用户：

CREATE USER 'donor_user'@'%' IDENTIFIED BY 'donor_pass';
GRANT BACKUP_ADMIN ON *.* TO 'donor_user'@'%';
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

2. Recipient实例创建克隆用户：

CREATE USER 'recipient_user'@'%' IDENTIFIED BY 'recipient_pass';
GRANT CLONE_ADMIN ON *.* TO 'recipient_user'@'%';
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

3. Recipient实例设置Donor白名单：

SET GLOBAL clone_valid_donor_list = '192.168.79.10:3306';

4. Recipient实例发起克隆：

CLONE INSTANCE FROM 'donor_user'@'192.168.79.10':3306 
IDENTIFIED BY 'donor_pass';

注意：

克隆命令在Recipient上发起
Recipient需要重启，建议通过mysqld_safe等进程管理工具管理

11.4.3 远程克隆完整语法

CLONE INSTANCE FROM 'user'@'host':port
IDENTIFIED BY 'password'
[DATA DIRECTORY [=] 'clone_dir']
[REQUIRE [NO] SSL];

参数说明：

host, port：Donor的IP和端口
user, password：Donor上的克隆用户和密码（需要BACKUP_ADMIN权限）
DATA DIRECTORY：备份目录（不指定则克隆到Recipient的数据目录）
REQUIRE [NO] SSL：是否开启SSL通信

11.4.4 远程克隆执行流程

依次执行以下步骤：

获取备份锁（Backup Lock）
- 同时在Donor和Recipient上获取
- 备份锁与DDL互斥
Drop用户表空间
- 清空Recipient的用户数据
从Donor拷贝数据
- 传输数据文件
重启Recipient实例
- 自动重启应用数据

11.5 查看克隆进度

11.5.1 查看克隆状态

SELECT * FROM performance_schema.clone_status\G

关键字段：

STATE：Completed/In Progress/Failed
BEGIN_TIME：开始时间
END_TIME：结束时间
SOURCE：源实例地址
BINLOG_FILE：对应的binlog文件
BINLOG_POSITION：对应的binlog位置
GTID_EXECUTED：执行过的GTID集

11.5.2 查看克隆进度

SELECT * FROM performance_schema.clone_progress;

阶段说明：

DROP DATA：删除数据
FILE COPY：拷贝文件
PAGE COPY：拷贝数据页
REDO COPY：拷贝redo log
FILE SYNC：同步文件
RESTART：重启实例
RECOVERY：恢复数据

关键指标：

ESTIMATE：预估数据量
DATA：已处理数据量
NETWORK：网络传输量
DATA_SPEED：数据处理速度
NETWORK_SPEED：网络传输速度

11.6 基于克隆数据搭建从库

11.6.1 GTID复制

无需关心位置点：

CHANGE MASTER TO 
  MASTER_HOST = 'master_host_name',
  MASTER_PORT = master_port_num,
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'repl_password',
  MASTER_AUTO_POSITION = 1;
START SLAVE;

11.6.2 基于位置点的复制

从performance_schema获取位置点：

SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;

执行CHANGE MASTER TO：

CHANGE MASTER TO 
  MASTER_HOST = 'master_host_name',
  MASTER_PORT = master_port_num,
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'repl_password',
  MASTER_LOG_FILE = 'mysql-bin.000002',
  MASTER_LOG_POS = 2731;
START SLAVE;

11.7 Clone Plugin的要求和限制

11.7.1 通用限制

克隆期间阻塞DDL - MySQL 8.0.27之前，克隆会阻塞Donor的DDL；8.0.27+不再阻塞
不拷贝配置参数和Binlog - 只拷贝数据文件
只拷贝InnoDB表数据 - 其他存储引擎只拷贝表结构
DATA DIRECTORY绝对路径问题 - 表通过DATA DIRECTORY设置绝对路径时需注意
不允许通过MySQL Router连接Donor
不能使用X Protocol端口

11.7.2 远程克隆特殊要求

必须一致：

MySQL版本（包括小版本）
主机操作系统和位数（32位/64位）
字符集参数（character_set_server、collation_server、character_set_filesystem）
innodb_page_size

其他要求：

Recipient必须有足够磁盘空间
同一时间只能执行一个克隆操作
Recipient需要通过mysqld_safe等工具管理（需要自动重启）

11.8 Clone Plugin最佳实践

建议：

用于快速搭建从库或MGR节点
定期使用本地克隆做备份验证
远程克隆前检查版本和配置一致性
监控克隆进度，预估完成时间

监控脚本：

-- 实时监控克隆进度
SELECT 
  ID,
  STAGE,
  STATE,
  BEGIN_TIME,
  ESTIMATE,
  DATA,
  ROUND(DATA/ESTIMATE*100, 2) AS 'PROGRESS(%)'
FROM performance_schema.clone_progress
WHERE STATE = 'In Progress';

附录：参数版本差异与最佳实践

A.1 MySQL 5.7 vs 8.0 关键差异

binlog相关：

MySQL 5.7：binlog默认关闭
MySQL 8.0：binlog默认开启，关闭需设置skip_log_bin

GTID相关：

MySQL 5.7.6+：支持在线开启GTID
MySQL 8.0：gtid_purged支持追加语法（+UUID:NUMBER）

半同步复制：

MySQL 5.7：引入无损复制（AFTER_SYNC）
MySQL 8.0：无损复制成为默认选项

并行复制：

MySQL 5.6：DATABASE级别并行
MySQL 5.7：LOGICAL_CLOCK（组提交）
MySQL 8.0：WRITESET_SESSION（写集合，更高并行度）

A.2 生产环境推荐配置

复制配置（MySQL 8.0）：

# 主库
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64

# 从库
server-id = 2
gtid-mode = ON
enforce-gtid-consistency = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
relay_log_recovery = ON

# 半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_timeout = 10000

日志配置：

log_timestamps = SYSTEM
log_error_verbosity = 2
slow_query_log = ON
long_query_time = 1
log_slow_slave_statements = ON
log_queries_not_using_indexes = ON
binlog_expire_logs_seconds = 604800  # 7天

备份配置：

# 推荐在从库关闭binlog
disable_log_bin = 1

A.3 监控指标

复制监控：

Slave_IO_Running、Slave_SQL_Running
Seconds_Behind_Master
Master_Log_File vs Relay_Master_Log_File
Read_Master_Log_Pos vs Exec_Master_Log_Pos
Retrieved_Gtid_Set vs Executed_Gtid_Set

半同步监控：

Rpl_semi_sync_master_status
Rpl_semi_sync_master_clients
Rpl_semi_sync_master_yes_tx
Rpl_semi_sync_master_no_tx
Rpl_semi_sync_master_no_times

延迟监控：

Seconds_Behind_Master
主从位置点差距
主库binlog生成速度

posted @ 2026-01-08 11:03 农夫运维阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

农夫运维

追逐影子的人，本身就是影子！

2. MySQL DBA复制与备份高级运维完整指南

MySQL 企业级复制与备份所有姿势详解

第一章 MySQL主从复制基础

1.1 复制的作用

1.2 复制原理

1.2.1 复制架构图

1.2.2 复制流程详解

1.3 搭建主从复制实战

环境准备

1.3.1 配置文件设置

1.3.2 创建复制用户

1.3.3 备份主库数据

1.3.4 恢复从库数据

1.3.5 建立主从关系

1.3.6 启动复制

1.4 复制状态监控

1.4.1 主库监控

1.4.2 从库监控

1.4.3 查看复制用户密码

1.4.4 查看从库重放信息

1.5 注意事项与最佳实践

第二章 GTID复制

2.1 GTID概述

2.1.1 什么是GTID

2.2 GTID的优势

2.3 开启GTID复制

2.3.1 关键参数

2.3.2 建立GTID复制

2.4 GTID相关参数详解

2.5 GTID复制的限制

2.6 设置gtid_purged注意事项

2.7 在线开启GTID复制

2.8 在线关闭GTID复制

2.9 GTID最佳实践

第三章 半同步复制

3.1 复制模式对比

3.1.1 异步复制（Asynchronous Replication）

3.1.2 全同步复制（Fully Synchronous Replication）

3.1.3 半同步复制（Semisynchronous Replication）

3.2 半同步复制原理

3.2.1 传统半同步（AFTER_COMMIT）

3.2.2 无损复制（AFTER_SYNC）- MySQL 5.7+

3.3 安装半同步复制

3.3.1 加载插件

3.3.2 启动半同步复制

3.3.3 重启从库IO线程

3.3.4 验证半同步状态

3.4 半同步复制参数详解

主库参数

从库参数

3.5 半同步复制状态监控

3.6 注意事项

第四章 多源复制

4.1 多源复制概念

4.2 多源复制架构图

4.3 搭建多源复制

4.3.1 前置要求

4.3.2 执行CHANGE MASTER TO

4.4 搭建过程注意事项

4.4.1 主库复制模式可以不同

4.4.2 基于现有主库搭建多源复制

4.4.3 GTID_PURGED设置问题

4.4.4 复制mysql库的风险

4.5 多源复制管理

4.6 多源复制最佳实践

第五章 多线程复制、过滤复制和延迟复制

5.1 多线程复制（Parallel Replication）

5.1.1 配置参数

5.1.2 WRITESET并行复制（MySQL 8.0+）

5.1.3 并行复制监控

5.2 过滤复制（Replication Filters）

5.2.1 CHANGE REPLICATION FILTER语法

5.2.2 过滤规则类型

5.2.3 实际示例

5.2.4 过滤复制注意事项

5.3 延迟复制（Delayed Replication）

5.3.1 配置延迟复制

5.3.2 延迟复制状态查看

第三章半同步复制

第四章多源复制

第五章多线程复制、过滤复制和延迟复制

第六章复制的常见管理操作