5. MySQL 数据库主从、高可用详解

MySQL 数据库主从、高可用详解

一、主从复制

1.1 主从同步原理

主从复制核心由 3 个线程 + 4 个文件 协同完成：

组件 所属 职责
Binlog Dump Thread 主库 将 binlog 事件推送给从库
Slave IO Thread 从库 请求并接收主库 binlog，写入 relaylog
Slave SQL Thread 从库 读取 relaylog，重放 SQL 语句
binlog 主库 记录所有数据变更事件
relaylog 从库 存储 IO 线程拉取的 binlog 副本
master.info 从库 记录连接主库的账号信息和 binlog 位置
relay-log.info 从库 记录 SQL 线程重放到的 relaylog 位置

数据流向： 主库 binlog → IO 线程拉取 → relaylog → SQL 线程重放 → 从库数据

1.2 主从构建五步流程

-- 第一步：创建复制专用用户（主库）
CREATE USER repl@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.0.%';

-- 第二步：主库全备 + 从库恢复（操作系统层面）
-- mysqldump -uroot -p123456 -A --master-data=2 --single-transaction > /tmp/full.sql
-- --single-transaction：InnoDB 表保证备份一致性（数据库中的数据 == 备份中的数据）【补】
-- scp /tmp/full.sql 10.0.0.52:/tmp/
-- mysql -uroot -p123456 < /tmp/full.sql

-- 第三步：从库配置连接主库信息
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='binlog.000008',   -- 全备文件中记录的位置点
  MASTER_LOG_POS=673,                -- 全备文件中记录的位置点
  MASTER_CONNECT_RETRY=10;

-- 第四步：启动从库同步
START SLAVE;

-- 第五步：验证主从状态
SHOW SLAVE STATUS\G
-- Slave_IO_Running:  Yes
-- Slave_SQL_Running: Yes

命令行快速监控：

mysql -uroot -p123456 -S /tmp/mysql3307.sock -e "show slave status\G" | grep "Yes"

1.3 主从故障分析

IO 线程故障（Slave_IO_Running: No）：

故障原因 排查方法
网络不通 ping / telnet 检查连通性
复制用户权限/密码错误 主库重新授权验证
主库 binlog 被清理 查看主库 binlog 列表，重新全备构建
server-id 冲突 检查主从 server_id 是否唯一
防火墙拦截 检查 3306 端口是否放通

SQL 线程故障（Slave_SQL_Running: No）：

故障原因 排查方法
从库已有冲突数据 查看 Relay_Master_Log_File + Exec_Master_Log_Pos 定位 binlog 事件
主从数据不一致 Last_Error 字段查看具体错误
DDL 操作冲突 主从表结构不一致导致重放失败

通用思路：SHOW SLAVE STATUS\G → 定位 Last_IO_Error / Last_SQL_Error → 按错误类型处理

1.4 延时从库

让从库落后主库一段时间，用于误操作的数据回溯恢复。

-- 从库配置延时（单位：秒）
CHANGE MASTER TO
  MASTER_DELAY = 3600;   -- 延时1小时

-- 停止 SQL 线程（保持 IO 线程继续拉取 binlog）
STOP SLAVE SQL_THREAD;

1.5 过滤复制

控制从库只同步指定的数据库，忽略其他库的变更。

需求示例： 只同步 ppt 和 word 两个数据库

永久配置（从库 my.cnf）：

[mysqld]
replicate_do_db=ppt
replicate_do_db=word

临时配置（在线生效）：

STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (word, ppt);
START SLAVE SQL_THREAD;

企业建议：永久和临时配置同时设置，避免重启丢失。

二、半同步复制与 GTID 扩展

2.1 半同步复制

复制模式对比：

复制模式 原理 数据安全 性能
异步复制 主库写完 binlog 立即返回 可能丢数据 最快
半同步复制 等待至少一个从库确认接收后返回 不丢数据 中等
全同步复制 所有从库确认后才返回 最安全 最差

插件安装与配置：

-- 【主库】安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 【从库】安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 【从库】重启 IO 线程使插件生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 验证状态
-- 主库：SHOW STATUS LIKE 'rpl_semi_sync_master_status';
-- 从库：SHOW STATUS LIKE 'rpl_semi_sync_slave_status';

永久配置（my.cnf）：

# ===== 主库配置 =====
rpl_semi_sync_master_enabled = ON           -- 启用半同步
rpl_semi_sync_master_timeout = 1000         -- 超时（毫秒），超时降级为异步
rpl_semi_sync_master_wait_for_slave_count = 1    -- 至少等待N个从库确认
rpl_semi_sync_master_wait_point = AFTER_SYNC     -- after_sync 模式（推荐）

# 事务组提交优化（避免 dump 线程串行提交导致延时）
binlog_group_commit_sync_delay = 1
binlog_group_commit_sync_no_delay_count = 1000

# ===== 从库配置 =====
rpl_semi_sync_slave_enabled = ON

2.2 GTID 复制

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

格式：source_id:transaction_id
示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23

优势： 自动识别位置点，无需手动指定日志文件和位置，便于异常恢复。

GTID 主从配置（主从 my.cnf 均需）：

[mysqld]
server_id = 51                          # 每台不同
log_bin = /data/3306/data/mysql-bin
binlog_format = row
gtid-mode = on                          -- 开启 GTID
enforce-gtid-consistency = true         -- 强制 GTID 一致性
log-slave-updates = 1                   -- 从库也记录 binlog

GTID 主从构建：

-- 【主库】全备
-- mysqldump -uroot -p123456 -A --master-data=2 --single-transaction > /tmp/full.sql

-- 【主库】创建复制用户
CREATE USER repl@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.0.%';

-- 【从库】恢复全备后配置主从（核心差异：无需指定日志位置）
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.51',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '123456',
  MASTER_AUTO_POSITION = 1;            -- GTID 模式，自动找位置

START SLAVE;

2.3 Clone Plugin 克隆操作

MySQL 8.0 Clone Plugin 用于高效的物理级数据拷贝。

场景 说明
本地克隆 数据备份
远程克隆 数据迁移（云主机→物理主机）

角色定义： 捐赠者（Donor）= 被克隆主机 | 接收者（Recipient）= 克隆后主机

本地克隆：

-- 安装插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- 创建克隆用户
CREATE USER clone_user@'%' IDENTIFIED BY 'password';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user';

-- 执行克隆（操作系统层面需先创建目录并授权）
-- mkdir -p /data/test && chown -R mysql.mysql /data/
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

-- 查看克隆进度
SELECT stage, state, end_time FROM performance_schema.clone_progress;

-- 启动克隆实例验证
-- mysqld_safe --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=off &

远程克隆：

-- 【捐赠者】创建用户
CREATE USER test_jz@'%' IDENTIFIED BY 'password';
GRANT BACKUP_ADMIN ON *.* TO 'test_jz';

-- 【接收者】创建用户 + 设置白名单
CREATE USER test_js@'%' IDENTIFIED BY 'password';
GRANT CLONE_ADMIN ON *.* TO 'test_js';
SET GLOBAL clone_valid_donor_list = '10.0.0.51:3306';

-- 【接收者】执行远程克隆
CLONE INSTANCE FROM test_jz@'10.0.0.51':3306 IDENTIFIED BY 'password';

常见错误：

错误信息 原因
ERROR 3707: Restart server failed 克隆后接收者用户被删除，无法连接
接收用户无法登录 数据库自动反向解析导致连接失败

克隆辅助主从构建（免交互脚本）：

# 【主库】安装插件+创建用户
ssh root@10.0.0.51 mysql -e \
  "INSTALL PLUGIN clone SONAME 'mysql_clone.so';
   CREATE USER test@'%' IDENTIFIED BY '123456';
   GRANT BACKUP_ADMIN ON *.* TO 'test'@'%';"

# 【从库】安装插件+设置白名单+远程克隆
mysql -e \
  "INSTALL PLUGIN clone SONAME 'mysql_clone.so';
   CREATE USER test@'%' IDENTIFIED BY '123456';
   GRANT CLONE_ADMIN ON *.* TO 'test'@'%';
   SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';"

mysql -utest -p123456 -h10.0.0.52 -P3306 \
  -e "CLONE INSTANCE FROM test@'10.0.0.51':3306 IDENTIFIED BY '123456';"

# 【从库】克隆后重启并配置主从
systemctl start mysqld
mysql -uroot -p123456 -e \
  "CHANGE MASTER TO master_host='10.0.0.51',master_user='repl',
   master_password='123456',master_auto_position=1;
   START SLAVE"

# 验证（输出2行 Yes 表示正常）
mysql -uroot -p123456 -e "SHOW SLAVE STATUS\G" | grep -i "yes" | wc -l

2.4 多源复制

将多个主库的数据汇总同步到一个从库（数据中台/大数据采集场景）。

架构： Master_1（51）+ Master_2（52）→ Slave（53）

所有节点需开启 GTID，然后：

-- 【所有主库】创建复制用户（关闭 binlog 记录）
SET SQL_LOG_BIN = 0;
CREATE USER repl@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.0.%';
SET SQL_LOG_BIN = 1;

-- 【从库】通过 FOR CHANNEL 区分多个主库
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.51',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '123456',
  MASTER_AUTO_POSITION = 1
  FOR CHANNEL 'Master_1';

CHANGE MASTER TO
  MASTER_HOST = '10.0.0.52',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '123456',
  MASTER_AUTO_POSITION = 1
  FOR CHANNEL 'Master_2';

START SLAVE FOR CHANNEL 'Master_1';
START SLAVE FOR CHANNEL 'Master_2';

关键语法：FOR CHANNEL '通道名'，每个主库对应独立通道，可单独启停。

三、MGR 组复制

3.1 MGR 概述

MGR（MySQL Group Replication）是 MySQL 官方提供的强一致性主从复制方案。

特性 说明
一致性 基于 Paxos 协议，事务在组内所有节点提交一致
高可用 单节点故障自动剔除，服务不中断
节点要求 最少 3 台（2N+1），最多 9 台
两种模式 单主模式（默认）/ 多主模式

3.2 MGR 通用配置（所有节点 my.cnf）

[mysqld]
server_id = 51                          # 每台节点不同
log_bin = /data/3306/mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = 1
skip_name_resolve
default_authentication_plugin = mysql_native_password
binlog_checksum = NONE                  # 禁用 binlog 校验（MGR 要求）
mysqlx = off

report_host = 10.0.0.51                 # 每台节点不同
report_port = 3306

# 事务依赖追踪（提升并行复制性能）
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64

# MGR 组配置（所有节点 group_name 必须一致）
loose-group_replication_group_name = "eb8441e9-8aef-4a86-a4bc-5beea315f04f"
loose-group_replication_start_on_boot = OFF   # 开机不自动加入
loose-group_replication_local_address = "10.0.0.51:33061"  # 每台端口不同
loose-group_replication_group_seeds = "10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"
loose-group_replication_bootstrap_group = OFF  # 仅引导时设 ON

# 从库额外配置
master_info_repository = TABLE
relay_log_info_repository = TABLE

3.3 单主模式构建

-- 【所有节点】安装 MGR 插件 + 设置密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 【所有节点】创建复制用户（关闭 binlog）
SET SQL_LOG_BIN = 0;
CREATE USER repl@'%' IDENTIFIED BY '123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN = 1;

-- 【主库 DB01】引导启动（创建 MGR 组）
CHANGE MASTER TO master_user='repl', master_password='123'
  FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- 【从库 DB02/DB03】加入组
RESET MASTER;
CHANGE MASTER TO master_user='repl', master_password='123'
  FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

-- 验证组状态
SELECT * FROM performance_schema.replication_group_members;

异常重置：

STOP GROUP_REPLICATION;
RESET MASTER;
SET SQL_LOG_BIN = 0;
CHANGE MASTER TO master_user='repl', master_password='123'
  FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

3.4 多主模式构建

方式一：直接配置

# 所有节点 my.cnf 中添加
group_replication_single_primary_mode = 0     -- 关闭单主模式
group_replication_enforce_update_everywhere_checks = 1  -- 严格一致性检查

方式二：从单主切换为多主

-- 【所有节点】
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_single_primary_mode = OFF;
SET GLOBAL group_replication_enforce_update_everywhere_checks = 1;

-- 【第一个节点】引导启动
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- 【其他节点】重新加入（自动成为主库）
START GROUP_REPLICATION;

-- 验证所有节点都为主库
SELECT * FROM performance_schema.replication_group_members;
模式 特点 适用场景
单主 只有1个节点可写，其他只读 默认推荐，简单稳定
多主 所有节点都可读写 高并发写入场景

四、MHA 高可用

4.1 架构概述

组件 说明
MHA Manager 管理节点，监控主库状态，负责故障检测和切换。可独立部署或部署在从库上
MHA Node 数据节点，运行在每台 MySQL 服务器上，执行数据补偿和 VIP 漂移

4.2 工作原理（7 大核心功能）

序号 功能 说明
① 监控 通过 /usr/bin/masterha_master_monitor 脚本，定时连接指定主库，识别主库运行状态 【补】
② 选主 利用 4 个数组（根据 candidate_master、check_repl_delay、log_bin、read_only 等权重）进行从库选主，选择数据最接近主库的从库成为新主 【补】
③ 数据补偿 ① 从库同步主库 binlog ② 从库彼此同步 relaylog ③ Manager 额外 binlog 补偿
④ 应用透明 VIP 漂移，对前端应用透明切换
⑤ 报警 故障切换后通知管理员（MHA 切换是一次性的）
⑥ 额外补偿 Manager 实时同步主库 binlog，从库可获取补偿
⑦ 自愈 原主修复后自动回归（仅云平台 RDS 支持）

4.3 环境准备与部署

第一步：数据库初始化 + 主从构建（参考第一章）

第二步：SSH 互信（所有节点）

rm -rf /root/.ssh && ssh-keygen
cd /root/.ssh && mv id_rsa.pub authorized_keys
scp -r /root/.ssh 10.0.0.52:/root
scp -r /root/.ssh 10.0.0.53:/root
ssh 10.0.0.51 date && ssh 10.0.0.52 date && ssh 10.0.0.53 date

第三步：MHA 软件安装

# 【所有节点】命令软链接
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

# 【所有节点】安装 Node
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

# 【Manager 节点】安装 Manager
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch \
  perl-Parallel-ForkManager perl-Time-HiRes
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

第四步：创建 MHA 用户 + 配置文件

-- 【主库】创建 MHA 管理用户
CREATE USER mha@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'mha';
GRANT ALL PRIVILEGES ON *.* TO mha@'10.0.0.%';

mkdir -p /etc/mha /var/log/mha/app1

# cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306
EOF

第五步：环境检测 + 启动

masterha_check_ssh --conf=/etc/mha/app1.cnf       # 测试 SSH 互信
masterha_check_repl --conf=/etc/mha/app1.cnf       # 测试主从复制

# 启动 MHA Manager
nohup masterha_manager --conf=/etc/mha/app1.cnf \
  --remove_dead_master_conf --ignore_last_failover \
  < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

# 检查状态
masterha_check_status --conf=/etc/mha/app1.cnf
# app1 (pid:3936) is running(0:PING_OK), master:10.0.0.51

4.4 VIP 漂移配置

cd /usr/local/bin/
chmod +x /usr/local/bin/* && dos2unix /usr/local/bin/*
cp master_ip_failover master_ip_failover.bak

# 编辑 master_ip_failover 关键行
my $vip = '10.0.0.50/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $ssh_Bcast_arp = "/sbin/arping -I ens33 -c 3 -A 10.0.0.50";

# MHA 配置文件加载脚本
vim /etc/mha/app1.cnf
# 添加：master_ip_failover_script=/usr/local/bin/master_ip_failover

# 手动绑定 VIP 并重启 MHA
ifconfig ens33:1 10.0.0.50/24
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf \
  --remove_dead_master_conf --ignore_last_failover \
  < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

4.5 故障切换与原主修复

自动切换流程： 监控发现异常 → 选主 → 数据补偿 → VIP 漂移 → 从库重定向 → 报警

原主修复步骤（MHA 切换是一次性的，需手动修复）：

-- 1. 启动原主：systemctl start mysqld
-- 2. 恢复数据（备份或克隆）

-- 3. 重建主从关系（指向新主）
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.52', MASTER_USER = 'repl',
  MASTER_PASSWORD = '123456', MASTER_AUTO_POSITION = 1;
START SLAVE;

# 4. 重新加入 MHA 集群
masterha_conf_host --command=add --conf=/etc/mha/app1.cnf \
  --hostname=10.0.0.51 --block=server1 --params="port=3306"

# 5. 恢复额外补偿
mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha \
  --raw --stop-never mysql-bin.00000X &

# 6. 重新启动 MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf \
  --remove_dead_master_conf --ignore_last_failover \
  < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
masterha_check_status --conf=/etc/mha/app1.cnf

4.6 在线切换（主库非故障时主动切换）

# 配置在线切换脚本
cd /usr/local/bin/
cp master_ip_online_change master_ip_online_change.bak

# 编辑 master_ip_online_change 关键行
my $vip = "10.0.0.50";
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key $vip down";
my $ssh_Bcast_arp = "/sbin/arping -I ens33 -c 3 -A 10.0.0.50";

# 加载脚本并重启 MHA
vim /etc/mha/app1.cnf
# 添加：master_ip_online_change_script=/usr/local/bin/master_ip_online_change

masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf \
  --remove_dead_master_conf --ignore_last_failover \
  < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

# 执行在线切换（主库 52 → 51）
masterha_stop --conf=/etc/mha/app1.cnf
masterha_master_switch --conf=/etc/mha/app1.cnf \
  --master_state=alive \
  --new_master_host=10.0.0.51 \
  --orig_master_is_new_slave \
  --running_updates_limit=10000

-- 在当前主库执行
FLUSH NO_WRITE_TO_BINLOG TABLES;

切换后验证： ① MHA 是否重新启动 ② VIP 是否切换到新主 ③ 主从关系是否正常

五、ProxySQL 读写分离

5.1 架构概述

端口 用途
6032 管理端口，对 ProxySQL 进行功能配置
6033 业务端口，应用程序通过此端口访问

5.2 安装

rpm -ivh proxysql-2.4.6-1-centos7.x86_64.rpm
systemctl start proxysql
netstat -lntup | grep proxy    # 确认 6032/6033 端口

5.3 配置五步走

-- 连接 ProxySQL 管理端口
mysql -uadmin -padmin -h127.0.0.1 -P6032

-- ===== 第一步：配置复制组 =====
-- 【MySQL 主库】SET GLOBAL read_only = 0;
-- 【MySQL 从库】SET GLOBAL read_only = 1;

INSERT INTO mysql_replication_hostgroups
  (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'proxy');
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;

-- ===== 第二步：添加 MySQL 服务器 =====
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (10, '10.0.0.50', 3306);           -- VIP 地址=写组

INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (20, '10.0.0.52', 3306);           -- 从库=读组

INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (20, '10.0.0.53', 3306);           -- 从库=读组
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;

-- ===== 第三步：配置监控 =====
-- 【MySQL 主库】
-- CREATE USER monitor@'%' IDENTIFIED WITH mysql_native_password BY '123';
-- GRANT REPLICATION CLIENT ON *.* TO monitor@'%';

SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = '123';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

SELECT * FROM mysql_server_connect_log;          -- 连接日志
SELECT * FROM mysql_server_ping_log;              -- 心跳日志
SELECT * FROM mysql_server_replication_lag_log;   -- 延时日志

-- ===== 第四步：创建应用用户 =====
-- 【MySQL 主库】
-- CREATE USER root@'%' IDENTIFIED WITH mysql_native_password BY '123';
-- GRANT ALL ON *.* TO root@'%';

INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('root', '123', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- ===== 第五步：读写分离规则 =====
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^select.*for update$', 10, 1);   -- select for update → 写组

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^select', 20, 1);                 -- 普通 select → 读组

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

5.4 读写分离测试

# 写操作 → 主库
mysql -uroot -p123 -P6033 -h127.0.0.1 \
  -e "begin; select @@server_id; commit"

# 读操作 → 读组轮询
mysql -uroot -p123 -P6033 -h127.0.0.1 \
  -e "select @@server_id"

ProxySQL 监控识别机制：

MySQL 状态 ProxySQL 行为
read_only = 0 划分到写组（识别失败降为读组）
read_only = 1 划分到读组（误判可能划入写组）

六、MIC 集群（MySQL InnoDB Cluster）

6.1 架构概述

MIC 是 MySQL 8.0 原生的高可用 + 读写分离方案：

组件 职责
MGR 底层主从同步（强一致性）
MySQL Shell（mysqlsh） 管理工具，创建/管理 MGR 集群
MySQL Router 路由中间件，自动读写分离

6.2 软件安装

tar xf mysql-shell-8.0.32-linux-glibc2.12-x86-64bit.tar.gz
tar xf mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-shell-8.0.32-linux-glibc2.12-x86-64bit mysqlsh
ln -s mysql-router-8.0.32-linux-glibc2.12-x86_64 mysql-router

# 环境变量
vim /etc/profile
# export PATH=/usr/local/mysql/bin:/usr/local/mysqlsh/bin:/usr/local/mysql-router/bin:$PATH

6.3 数据库初始化

所有节点 my.cnf 额外配置：

master_info_repository = TABLE
relay_log_info_repository = TABLE
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
binlog_checksum = NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
loose-group_replication_ip_whitelist = "master,slave01,slave02,manager"

6.4 MySQL Shell 管理 MGR 集群

-- 【所有节点】
ALTER USER root@'localhost' IDENTIFIED WITH mysql_native_password BY '123';

// 【所有节点】初始化实例配置
mysqlsh
\connect root@localhost:3306
dba.configureLocalInstance()
dba.checkInstanceConfiguration("root@localhost:3306")
// 输出 OK 表示通过

// 【主库】创建 MGR 集群
\connect root@master:3306
var cluster = dba.createCluster('xiaoQ')
// 等价于手动：change master to ... + bootstrap + start group_replication
cluster.status()

// 【从库】加入集群
var cluster = dba.getCluster('xiaoQ')
cluster.addInstance('root@slave01:3306')
cluster.addInstance('root@slave02:3306')
// 等价于手动：change master to ... + start group_replication
cluster.status()

6.5 MySQL Router 部署与读写分离

# 在 Manager 节点部署 Router
mysqlrouter --bootstrap root@master:3306 -d myrouter --user=root
cd /etc/myrouter/ && ./start.sh

# Router 端口分配：6446=读写端口 | 6447=只读端口

# 写请求 → 主库
mysql -uroot -P6446 -h10.0.0.54 -p

# 读请求 → 从库
mysql -uroot -P6447 -h10.0.0.54 -p

MIC vs 传统方案对比：

对比项 MHA + ProxySQL MIC（原生）
主从同步 异步/半同步 MGR 强一致性
高可用 MHA Manager MGR 自动选主
读写分离 ProxySQL 手动配置 Router 自动感知
运维复杂度 高（多组件独立部署） 低（Shell 一键管理）
适用版本 5.7+ 8.0+

附录：高频故障排查速查表

故障现象 排查命令/方法 常见原因
IO 线程 No SHOW SLAVE STATUS\G → Last_IO_Error 网络/权限/binlog 被清理/server_id 冲突
SQL 线程 No SHOW SLAVE STATUS\G → Last_SQL_Error 主从数据冲突/DDL 不一致
半同步降级为异步 SHOW STATUS LIKE 'rpl_semi_sync%' 从库响应超时（timeout）
MGR 无法加入组 SELECT * FROM performance_schema.replication_group_members server_id 冲突/白名单未配置/plugin 未安装
MHA 检查失败 masterha_check_repl --conf=... SSH 互信不通/复制用户权限不足
ProxySQL 全走主库 SELECT * FROM mysql_servers\G 从库 read_only 未设置/监控用户错误
Clone 远程失败 ERROR 3707 接收者用户被删除/DNS 反向解析
GTID 复制报错 SHOW SLAVE STATUS\G → Retrieved_Gtid_Set 从库非复制写入导致 GTID 冲突
posted @ 2026-03-30 23:02  gzjwo  阅读(44)  评论(0)    收藏  举报