Mysql内容及相关实验

Honkers · 4 小时前

主机名	IP 地址	server-id	角色
mysql-node1	172.25.254.10	10	主库 / 首个节点
mysql-node2	172.25.254.20	20	从库 / 候选主库
mysql-node3	172.25.254.30	30	从库
mha	172.25.254.x	-	MHA Manager（高可用章节用）
mysqlrouter	172.25.254.40	-	MySQL Router（路由章节用）

系统：RHEL9.8系列
MySQL 版本：8.3.0（源码编译）
数据库 root 密码统一演示用：lee
复制专用账号：lee@'%' / lee

一、为什么要学 MySQL 集群？

1.1 单台 MySQL 的三大痛点

一台数据库服务器，就像只有一个收银台的超市。

痛点	解释	专业说法
性能瓶颈	双十一大家都来结账，一个收银台排长队，后面的人干等	高并发下单节点吞吐不足
单点故障	收银员突然请假，整个超市没法结账，全完了	单点故障（SPOF），一挂全挂
数据丢失	收银台的账本只有一本，烧了就再也找不回来	数据可靠性无保障

1.2 集群能解决什么

MySQL 集群（Cluster）的本质，就是用 多台服务器协同工作，把数据复制 / 分散存储、把请求分散处理，最终达到三个目标：

高可用（HA）：一个节点挂了，别的节点顶上，服务不中断。
高扩展（Scalability）：生意好了，加机器就能提升处理能力。
数据一致性：集群内部的数据保持同步（不同架构一致性级别不同）。

1.3 本文的路线

我们从最底层开始，一层层往上搭：

源码编译安装 → 主从复制 → 主从优化(延迟/慢查询/GTID/并行/半同步)
→ MHA 高可用 → 组复制 MGR → MySQL Router 路由

复制代码

每一步都是「先懂原理，再敲命令」，跟着做下来，你就拥有了一套可演示、可量产的 MySQL 集群方案。

二、MySQL 源码编译安装

2.1 为什么是企业级「源码编译」？

装 MySQL 有三种姿势——yum/dnf 直接装、二进制包解压、源码编译。
企业中 90% 的服务器跑的是 Linux，而企业里用得最多的又是 MySQL 5.7 和 8.x。
之所以选 源码编译，是因为它能：

精确控制安装路径（/usr/local/mysql）、数据目录（/data/mysql）、字符集；
按需启用存储引擎（比如只留 InnoDB）；
和系统里的 SSL 库、systemd 深度契合，后期好维护。

2.2 安装编译依赖

RHEL9 软件仓库自带编译工具链，直接装即可：

[root@mysql-node1 ~]# dnf install cmake3 gcc git bison openssl-devel ncurses-devel systemd-devel \
rpcgen.x86_64 libtirpc-devel-1.3.3-9.el9.x86_64.rpm \
gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils \
gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc -y
# 验证版本
[root@mysql-node1 ~]# cmake3 --version
cmake version 3.26.5
[root@mysql-node1 ~]# gcc -v
gcc version 11.5.0 20240719 (Red Hat 11.5.0-5) (GCC)

复制代码

2.3 下载并解压源码

# 下载 MySQL 8.3.0 源码（带 boost 的版本，编译时不用再单独下 boost）
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.3.0.tar.gz
[root@mysql-node1 ~]# tar zxf mysql-boost-8.3.0.tar.gz
[root@mysql-node1 ~]# cd mysql-8.3.0/
[root@mysql-node1 mysql-8.3.0]# mkdir build && cd build

复制代码

2.4 cmake 配置

[root@mysql-node1 build]# cmake3 .. \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_EXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \
-DWITH_SSL=system \
-DWITH_BOOST=bundled \
-DWITH_DEBUG=OFF \
-DSYSTEMD=ON \
-DSYSTEMD_SERVICE_DIR=/usr/lib/systemd/system

复制代码

2.5 编译并安装

# -j2 表示用 2 个核心并行编译，机器核心多就 -j$(nproc)
[root@mysql-node1 build]# make -j2
[root@mysql-node1 build]# make install

复制代码

2.6 部署：环境变量、运行用户、数据目录

[root@mysql-node1 ~]# cd /usr/local/mysql/
# 1) 把 mysql 命令加入环境变量
[root@mysql-node1 mysql]# vim ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
[root@mysql-node1 mysql]# source ~/.bash_profile
# 2) 创建 mysql 专用系统用户（不能登录、无家目录）
[root@mysql-node1 mysql]# useradd -r -s /sbin/nologin -M mysql
# 3) 创建数据目录并授权
[root@mysql-node1 ~]# mkdir -p /data/mysql
[root@mysql-node1 ~]# chown mysql.mysql /data/mysql/
# 4) 主配置文件
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

复制代码

2.7 数据初始化

[root@mysql-node1 ~]# mysqld --initialize --user=mysql

复制代码

⚠️ 重点：初始化后会 在屏幕输出一个 root 临时密码（也在 /data/mysql/*.err 日志里），一定要记下来！格式类似 lsyVh+etR1ht。
忘记的话去 mysql-node1.err 里 grep password 找。

2.8 启动 MySQL

方式 A：自带的 init 脚本（通用）

[root@mysql-node1 ~]# dnf install initscripts -y
[root@mysql-node1 ~]# cd /usr/local/mysql/support-files/
[root@mysql-node1 support-files]# cp -p mysql.server /etc/init.d/mysqld
[root@mysql-node1 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.err'.
. SUCCESS!
# 设置开机自启
[root@mysql-node1 support-files]# chkconfig --level 35 mysqld on

复制代码

方式 B：systemd 服务（RHEL9 推荐）

[root@mysql-node2 ~]# vim /lib/systemd/system/mysqld.service
[Unit]
Description=MySQL 8.3 Database Server
After=network.target syslog.target
[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecReload=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown
TimeoutSec=300
PrivateTmp=true
LimitNOFILE=65535
LimitNPROC=65535
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
[root@mysql-node2 ~]# systemctl daemon-reload
[root@mysql-node2 ~]# systemctl enable --now mysqld.service

复制代码

2.9 安全初始化

[root@mysql-node1 ~]# mysql_secure_installation

复制代码

按提示操作，建议这样选：

Enter password for user root: # 输入刚才的临时密码
New password: lee # 设置新密码
Re-enter new password: lee
VALIDATE PASSWORD ... ? : no # 是否启用密码强度校验，演示选 no
Change the password for root ? : no # 上面已经设过了
Remove anonymous users? : y # 删匿名用户 ✅
Disallow root login remotely? : y # 禁止 root 远程登录 ✅
Remove test database? : y # 删 test 库 ✅
Reload privilege tables now? : y # 立即生效 ✅

复制代码

2.10 验证

[root@node10 ~]# mysql -uroot -plee
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

复制代码

✅ 看到这四个系统库，说明 MySQL 源码编译部署成功！三台机器都按同样流程装好。

三、主从复制：一主一从 / 一主多从

3.1 主从复制是什么？

主从复制就是「老板写账，小弟抄账」。
主库（Master）负责接收所有的增删改（写），把这些操作记到「二进制日志（binlog）」里；
从库（Slave）把 binlog 拿过来，在自己机器上重放一遍，于是从库的数据就和主库一模一样了。
这样读请求就可以分散到多个从库上，主库专心写，从库专心读，压力一下子就分摊了。

3.2 复制的三个线程

主从同步本质是基于 binlog，整个过程由 3 个线程 推动：

Binlog dump 线程（主库）：从库连上来后，主库把 binlog 的更新推送给从库。
I/O 线程（从库）：连接到主库，把收到的 binlog 写到本地的「中继日志（relay log）」。
SQL 线程（从库）：读取中继日志，把里面的事件在本地执行一遍，数据就和主库同步了。

3.3 复制的三步骤

步骤1：Master 把写操作记录到 binlog
↓
步骤2：Slave 的 I/O 线程把 binlog 拷到本地 relay log
↓
步骤3：Slave 的 SQL 线程重放 relay log，应用到自己的数据库

复制代码

💡 关键点：MySQL 复制是异步的，且是串行回放的。也就是说从库多少会「慢半拍」，这也是后面要讲「延迟复制」「并行复制」的原因。

3.4 实验：编写 my.cnf 主配置文件

三台机器都要开 log-bin 和唯一 server-id：

# mysql-node1（主）
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin
# mysql-node2（从）
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin
# mysql-node3（从）
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
log-bin=mysql-bin
# 三台都重启
[root@mysql-node1~3 ~]# /etc/init.d/mysqld restart

复制代码

⚠️ server-id 必须全网唯一，不然主从会打架。

3.5 实验：建立同步专用账号

在主库创建用于复制的账号（注意 MySQL 8 默认认证插件是 caching_sha2_password，跨版本/老客户端建议用 mysql_native_password）：

[root@mysql-node1 ~]# mysql -uroot -plee
mysql> SHOW VARIABLES LIKE 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
mysql> CREATE USER lee@'%' IDENTIFIED WITH mysql_native_password BY 'lee';
mysql> GRANT REPLICATION SLAVE ON *.* TO lee@'%';
mysql> SHOW GRANTS FOR lee@'%';
+---------------------------------------------+
| Grants for lee@% |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `lee`@`%` |
+---------------------------------------------+

复制代码

验证从库能连上主库（在 node2 测）：

[root@mysql-node2 ~]# mysql -ulee -plee -h172.25.254.10
# 能进 mysql> 提示符就说明网络+账号 OK

复制代码

3.6 实验：配置一主一从

# 在主库查看当前 binlog 文件名和位置
[root@mysql-node1 ~]# mysql -uroot -plee -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 659 | | | |
+------------------+----------+----------+--------------+------------------+-------------------+
# 在从库（node2）指向主库
[root@mysql-node2 ~]# mysql -uroot -plee
mysql> CHANGE MASTER TO \
MASTER_HOST='172.25.254.10', \
MASTER_USER='lee', \
MASTER_PASSWORD='lee', \
MASTER_LOG_FILE='mysql-bin.000001', \
MASTER_LOG_POS=659;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
# 关键看这两行：
# Slave_IO_Running: Yes ← I/O 线程正常工作
# Slave_SQL_Running: Yes ← SQL 线程正常回放

复制代码

3.7 实验：测试同步

# 主库建库
[root@mysql-node1 ~]# mysql -uroot -plee -e "CREATE DATABASE timinglee;"
# 从库查看，能看见 timinglee 就是同步成功
[root@mysql-node2 ~]# mysql -uroot -plee -e "SHOW DATABASES;"
# ... timinglee 已出现

复制代码

3.8 实验：向「一主多从」中加入新从库

真实场景：集群已经跑了一阵，主库里已经有数据了，这时候新加一台从库（node3），不能直接 CHANGE MASTER，否则新从库会「缺历史账本」。

思路：先手动把旧数据「拉平」，再接上复制。

# 1) 模拟主库已有数据
[root@mysql-node1 ~]# mysql -uroot -plee
mysql> CREATE TABLE timinglee.userlist (name VARCHAR(10) NOT NULL, pass VARCHAR(50) NOT NULL);
mysql> INSERT INTO timinglee.userlist VALUES ('user1','123');
# 2) 把主库现有数据导出，传给新从库
[root@mysql-node1 ~]# mysqldump -uroot -p timinglee > timinglee.sql
[root@mysql-node1 ~]# scp timinglee.sql root@172.25.254.30:/root/
# 3) 新从库先建同名的库，再把数据灌进去
[root@mysql-node3 ~]# mysql -uroot -plee -e "CREATE DATABASE timinglee;"
[root@mysql-node3 ~]# mysql -uroot -plee timinglee < timinglee.sql
[root@mysql-node3 ~]# mysql -uroot -plee -e "SELECT * FROM timinglee.userlist;"
+-------+------+
| name | pass |
+-------+------+
| user1 | 123 |
+-------+------+
# 4) 查主库当前 binlog 位置，让新从库从这里开始追
[root@mysql-node1 ~]# mysql -uroot -plee -e "SHOW MASTER STATUS;"
# 假设 File=mysql-bin.000001 Position=1415
[root@mysql-node3 ~]# mysql -uroot -plee
mysql> CHANGE MASTER TO \
MASTER_HOST='172.25.254.10', MASTER_USER='lee', \
MASTER_PASSWORD='lee', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1415;
mysql> START SLAVE;

复制代码

测试：主库再插一条 user2，node3 也能查到，一主两从就齐活了。

3.9 主从架构的缺陷

传统主从是「异步」的——主库写完 binlog 往磁盘一落，就告诉客户端「成功了」，至于从库到底收没收到、落没落盘，主库 根本不确认。
这就埋了雷：

主到从的网络抖一下，binlog 可能压根没到从库；
主库突然挂了，还没传过去的那些事务就丢了；
所以异步主从 做不到零数据丢失、强一致。

后面的「半同步」「MGR」都是在补这个坑。

四、主从架构优化技巧

主从搭好只是开始，生产中还要解决「误删能救、慢 SQL 能查、切换不迷路、回放不卡、数据不丢」这些问题。

4.1 延迟复制（Delay Replication）

理论：延迟复制只卡 SQL 线程，不卡 I/O 线程。意思是：binlog 照样实时拉到从库，但 SQL 线程故意「晚 N 秒」再回放。

相当于给从库装了个「时光机」。老板把账记下来了，小弟故意等 60 秒再抄。这 60 秒里如果老板发现自己手滑 DELETE 错数据了，赶紧在主库上止损；小弟因为还没抄那笔，数据还是好的——这是防误操作的救命绳。

实验（MySQL 8）：

# 在需要延迟的从库上
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY=60; # 延迟 60 秒
mysql> START REPLICA;
# 验证
[root@mysql-node2 ~]# mysql -uroot -plee -e "SHOW SLAVE STATUS\G" | grep SQL_Delay
SQL_Delay: 60

复制代码

验证效果：主库 DELETE 掉 user1，未延迟的从库立刻同步删除；延迟从库 60 秒内 user1 还在，等足 60 秒后才消失。

4.2 慢查询日志（Slow Query Log）

理论：执行时间超过阈值（默认 long_query_time=10 秒）的 SQL 会被记进慢查询日志。它是 SQL 调优的放大镜——哪些语句慢，一目了然。

实验：

# 默认是关闭的
mysql> SHOW VARIABLES LIKE 'slow%';
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
# 开启 + 把阈值调到 4 秒
mysql> SET GLOBAL slow_query_log=ON;
mysql> SET long_query_time=4;
# 造一条慢查询（sleep 4 秒，刚好超阈值）
mysql> SELECT SLEEP(4);
# 看日志
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
# Time: 2026-02-27T02:04:39.297189Z
# Query_time: 4.000424 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1772157875;
select sleep (4);

复制代码

💡 注意：SELECT SLEEP(3) 不会进慢日志（3 < 4），SLEEP(4) 才会。阈值判断是 严格大于。

4.3 GTID 模式（全局事务标识）

为什么需要 GTID？（理论）

没有 GTID 时，主从靠「binlog 文件名 + 位置（pos）」来对齐。一旦主库挂了，从库要接替成新主库，你就得手动去算「新主库现在的 pos 是多少、其他从库该从哪追」——又麻烦又容易错。
GTID 相当于给每个事务发了一张「全局身份证」（形如 uuid:序号）。从库只管说「我执行到第几个了」，不用关心具体 pos，主从切换全自动对齐，再也不用手算位置。

GTID 工作流程（4 步）：

主库提交事务，自动生成唯一 GTID，写进 binlog；
从库读 binlog，先把这个 GTID 标记为「已收到」；
从库执行该事务，执行完标记为「已执行」；
同步时，从库只向主库请求自己「还没执行」的 GTID 对应的事务。

实验：开启 GTID

# 三台主机 my.cnf 都加两行，然后重启
[root@mysql-node1~3 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node1~3 ~]# /etc/init.d/mysqld restart
# 从库用 AUTO_POSITION 方式指向主库（不再写具体的 file/pos）
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO \
MASTER_HOST='172.25.254.10', MASTER_USER='lee', \
MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
# 看到 Auto_Position: 1 就说明 GTID 复制生效了

复制代码

4.4 多线程并行回放（Parallel Replication）

默认从库是 单线程 回放——主库那边一群人同时写，从库这边只有一个人一笔一笔抄，能不慢吗？
开了多线程回放，相当于小弟叫来 16 个帮手，按「逻辑时钟（LOGICAL_CLOCK）」分组并行抄，主从延迟大幅缩小。

实验（在从库 node2 上）：

[root@mysql-node2 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK # 基于逻辑时钟并行
slave-parallel-workers=16 # 开 16 个 worker 线程
relay_log_recovery=ON # 中继日志崩溃恢复
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
# 重启后用 SHOW PROCESSLIST; 能看到多个 Worker 线程在干活

复制代码

MySQL「组提交（Group Commit）」是一个性能优化——多个事务的日志可以一次性刷盘，减少磁盘 I/O。多线程回放配合组提交，效果最佳。

4.5 半同步复制（Semi-Synchronous Replication）

原理：

前面说过异步复制「主库写完就跑，不管从库死活」。半同步就是打个补丁：
主库写完 binlog，必须等至少一个从库确认「我收到并写进 relay log 了」（返回 ACK），主库才正式提交、才告诉客户端成功。
这样就保证：只要客户端收到「成功」，那笔数据至少已经在一个从库落盘，主库挂了也丢不了。

MySQL 5.6 的 AFTER_COMMIT：先提交，再等 ACK。
MySQL 5.7+ 的 AFTER_SYNC（默认）：先等 ACK，再提交。更安全。
如果等 ACK 超时（默认 10 秒），主库自动退化成异步，不阻塞业务。

实验：开启半同步

# ===== 主库 =====
[root@mysql-node1 ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
# ===== 从库 =====
[root@mysql-node2 ~]# vim /etc/my.cnf
rpl_semi_sync_slave_enabled=1
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE IO_THREAD; # 重启 IO 线程，半同步才生效
mysql> START SLAVE IO_THREAD;
# 看状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
# Rpl_semi_sync_master_status = ON 表示半同步已开启

复制代码

实验：模拟 ACK 故障

# 所有从库停掉 IO 线程（假装网络断了，收不到 ACK）
mysql> STOP SLAVE IO_THREAD;
# 主库再插入数据 —— 会卡 10 秒（等 ACK 超时）
mysql> INSERT INTO timinglee.userlist VALUES ('user3','123');
Query OK, 1 row affected (10.01 sec) # 注意这 10 秒！
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
# Rpl_semi_sync_master_status = OFF ← 已自动退化为异步
# Rpl_semi_sync_master_no_tx = 2 ← 有 2 笔没走半同步
# 恢复：从库启动 IO 线程
mysql> START SLAVE IO_THREAD;

复制代码

结论：半同步用「一点点写延迟」换来了「不丢数据」，是生产环境强烈推荐的配置。

五、MHA 高可用集群

5.1 什么是 MHA？

💡 大白话：前面主从切换还得「人肉敲命令」，MHA（Master High Availability）就是请了个 24 小时不睡觉的数据库管家。
它部署在一台独立机器（MHA Manager）上，不停 ping 主库：主库一挂，管家立刻按规则挑一个数据最新的从库 自动提拔成新主库，并把其他从库重新指向它。
再配合一个 VIP（虚拟 IP）——应用永远连这个 VIP，管家在主库切换时把 VIP「漂移」到新主库上，应用完全无感知。

架构角色：

MHA Manager（管家） —— 独立主机，监控 + 自动切换
├── mysql-node1 （主，带 VIP 172.25.254.100）
├── mysql-node2 （从，候选主库）
└── mysql-node3 （从）

复制代码

5.2 环境准备：保证数据一致性

切换前，三台 MySQL 必须已经是 GTID 主从状态，且数据一致。先重新初始化并把主从重新接好：

# 所有节点重置数据（演示用，生产别乱 rm）
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# mysqld --initialize --user mysql
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql_secure_installation
# 主库建复制账号
[root@mysql-node1 ~]# mysql -uroot -plee -e "CREATE USER lee@'%' IDENTIFIED WITH mysql_native_password BY 'lee';"
[root@mysql-node1 ~]# mysql -uroot -plee -e "GRANT REPLICATION SLAVE ON *.* TO lee@'%';"
# 从库接主库（GTID 模式）
[root@mysql-node2 ~]# mysql -uroot -plee -e "CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='lee', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;"
[root@mysql-node2 ~]# mysql -uroot -plee -e "START SLAVE;"

复制代码

5.3 安装 MHA 软件

Manager 节点装 Perl 依赖 + manager/node 包；所有 MySQL 节点只装 node 包。

# MHA 节点（独立主机）
[root@mha ~]# dnf install perl perl-DBD-MySQL perl-CPAN -y
[root@mha ~]# cpan
cpan[1]> install Config::Tiny
cpan[2]> install Log::Dispatch
cpan[3]> install Mail::Sender
cpan[4]> install Parallel::ForkManager
cpan[5]> exit
# 验证组件装好
[root@mha ~]# perl -MConfig::Tiny -e 'print "OK\n"' # OK
[root@mha ~]# perl -MLog::Dispatch -e 'print "OK\n"' # OK
[root@mha MHA-7]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm \
mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps
# 所有 MySQL 节点安装 node 包
[root@mha MHA-7]# for i in 10 20 30; do
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.$i:/mnt
ssh -l root 172.25.254.$i "rpm -ivh /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps"
done

复制代码

5.4 修复检测代码 + 建立远程用户

MySQL 8 的版本号（如 8.3.0）会让 MHA 旧版解析报错，需要修一下 NodeUtil.pm：

[root@mha ~]# vim /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm
# 把原 parse_mysql_major_version 函数替换为：
sub parse_mysql_major_version($) {
my $str = shift;
my @nums = $str =~ m/(\d+)/g;
my $result = sprintf( '%03d%03d', $nums[0]//0, $nums[1]//0);
return $result;
}

复制代码

主库给 MHA 建一个能远程管理的 root 账号：

mysql> CREATE USER root@'%' IDENTIFIED WITH mysql_native_password BY 'lee';
mysql> GRANT ALL ON *.* TO root@'%';

复制代码

5.5 配置文件

[root@mha mha4mysql-manager-0.58]# mkdir -p /etc/masterha/
# 把模板合并成 app1.cnf（实际用 samples/conf 下的模板拼接即可）
[root@mha ~]# vim /etc/masterha/app1.cnf
[server default]
user=root
password=lee
ssh_user=root
repl_user=lee
repl_password=lee
master_binlog_dir=/data/mysql
remote_workdir=/tmp
secondary_check_script=masterha_secondary_check -s 172.25.254.10 -s 172.25.254.20
ping_interval=3
manager_workdir=/etc/masterha
manager_log=/etc/masterha/mha.log
[server1]
hostname=172.25.254.10
candidate_master=1 # 优先被选为新的主库
check_repl_delay=0
[server2]
hostname=172.25.254.20
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.25.254.30
no_master=1 # 永远不会被提拔为主库

复制代码

5.6 环境检测

# 1) SSH 互通检测
[root@mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
# 末尾出现：All SSH connection tests passed successfully.
# 2) 主从复制检测
[root@mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
# 末尾出现：MySQL Replication Health is OK.
# 并且能看到 GTID failover mode = 1，三台都在线

复制代码

5.7 手动切换

① 主库无故障切换（在线切换） —— 比如运维要把主库从 node1 挪到 node2：

[root@mha ~]# masterha_master_switch \
--conf=/etc/masterha/app1.cnf \
--master_state=alive \
--new_master_host=172.25.254.20 \
--new_master_port=3306 \
--orig_master_is_new_slave \
--running_updates_limit=10000
# 交互过程按提示输入 yes 即可
# 切换完成后，原主 node1 自动变成 node2 的从库

复制代码

② 主库故障后切换：

[root@mha ~]# masterha_master_switch \
--master_state=dead \
--conf=/etc/masterha/app1.cnf \
--dead_master_host=172.25.254.10 \
--dead_master_port=3306 \
--new_master_host=172.25.254.20 \
--new_master_port=3306 \
--ignore_last_failover
# 按提示 yes，MHA 会把 node20 提拔为主库，node30 重新指向它

复制代码

⚠️ 故障恢复提示：切换后会生成锁文件 /etc/masterha/app1.failover.complete，不删掉就不能再次切换。恢复旧主库时也记得：

[root@mha ~]# rm -f /etc/masterha/app1.failover.complete
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql -uroot -plee -e "CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='lee', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;"
[root@mysql-node1 ~]# mysql -uroot -plee -e "START SLAVE;"

复制代码

💡 如果之前开过半同步，故障切换后从库可能报 Last_SQL_Error，解决办法是跳过那笔冲突事务：

mysql> STOP REPLICA;
mysql> SET GTID_NEXT='d77b3abd-92cb-11f1-abed-000c29f195bc:1'; # 用实际报错的事务号
mysql> BEGIN; COMMIT;
mysql> SET GTID_NEXT='AUTOMATIC';
mysql> START REPLICA;

复制代码

5.8 自动切换

开两个 shell：一个 watch 日志，一个启动 manager。

# shell 1：盯日志
[root@mha ~]# watch -n1 cat /etc/masterha/mha.log
# shell 2：启动自动切换守护
[root@mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf &
# 模拟主库宕机
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
# 几秒后 watch 窗口里会看到 MHA 自动把主库切到 node20，并输出 Failover Report

复制代码

5.9 VIP 漂移

先把官方脚本放到 MHA 的 scripts 目录，并改 VIP 地址：

[root@mha ~]# mkdir -p /etc/masterha/scripts
[root@mha ~]# cp MHA-7/master_ip_failover /etc/masterha/scripts/
[root@mha ~]# cp MHA-7/master_ip_online_change /etc/masterha/scripts/
[root@mha ~]# vim /etc/masterha/scripts/master_ip_failover
my $vip = '172.25.254.100/24';
[root@mha ~]# vim /etc/masterha/scripts/master_ip_online_change
my $vip = '172.25.254.100/24';
# 在 app1.cnf 里启用这两个脚本
[root@mha ~]# vim /etc/masterha/app1.cnf
master_ip_failover_script= /etc/masterha/scripts/master_ip_failover
master_ip_online_change_script= /etc/masterha/scripts/master_ip_online_change
# 先在初始主库上手动挂上 VIP
[root@mysql-node1 ~]# ip a a 172.25.254.100/24 dev eth0
# 启动 manager 并制造故障
[root@mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf &
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
# 到 node2 上验证 VIP 已经漂过来
[root@mysql-node2 ~]# ip a
# 能看到 inet 172.25.254.100/24 scope global secondary eth0

复制代码

✅ VIP 漂移到新主库，应用连 172.25.254.100 完全无感知——这就是真正的高可用。

六、MySQL 组复制 MGR

6.1 什么是 MGR？

MGR（MySQL Group Replication）是 MySQL 官方 2016 年推出的「自带高可用 + 强一致」方案，比 MHA 更「原生」。
把几台机器拉成一个「组」，组里的写入要经过 大多数人（> N/2+1）投票同意 才能提交——这叫「多数派协议」，天然防脑裂。
组里任何一台挂了，剩下的自动重组，无需外部管家。

单主 vs 多主模式：

模式	特点	解释
single-primary（单主）	组内只有一台可写，其余只读；主挂了自动选新主	一个话事人，其他人旁听
multi-primary（多主）	所有节点都能读写，数据最终一致	人人都能拍板，最后对账一致

⚠️ 节点数 不能超过 9 台。

6.2 实验：还原所有节点

为干净起见，三台都重置数据，并写入 MGR 所需的 my.cnf：

# 所有节点
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# cat > /etc/my.cnf <<EOF
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10 # node2 写 20，node3 写 30
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
default_authentication_plugin=mysql_native_password
log_slave_updates=ON
binlog_format=ROW # 必须用行格式
binlog_checksum=NONE
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
EOF
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start

复制代码

6.3 实验：部署组复制

先统一配置 hosts 解析，并追加组复制参数（注意：每台机器的 local_address 要改成自己的 IP）：

[root@mysql-node1 ~]# cat > /etc/hosts <<EOF
172.25.254.10 mysql1
172.25.254.20 mysql2
172.25.254.30 mysql3
EOF
[root@mysql-node1 ~]# cat >> /etc/my.cnf <<EOF
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.10:33061" # 每台改自己 IP
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF # OFF=多主模式
EOF
[root@mysql-node1 ~]# /etc/init.d/mysqld start

复制代码

首台节点（引导组）：

[root@mysql-node1 ~]# mysql -uroot -p'lsyVh+etR1ht' # 用初始化生成的临时密码
mysql> ALTER USER root@localhost IDENTIFIED BY 'lee';
mysql> SET SQL_LOG_BIN=0; # 建用户时不写 binlog，避免污染
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
mysql> GRANT REPLICATION SLAVE, CONNECTION_ADMIN, BACKUP_ADMIN, GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='lee' \
FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON; # 只有首台需要引导
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='lee';
mysql> SET GLOBAL group_replication_bootstrap_group=OFF; # 引导完立刻关掉
mysql> SELECT * FROM performance_schema.replication_group_members;
# 看到 MEMBER_STATE = ONLINE，PRIMARY 角色，说明首节点进组成功

复制代码

其余节点加入：

# node2 / node3 同样建 rpl_user，然后：
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='lee' \
FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='lee';
# 若报错 "not configured properly"，先执行 reset master; 再启动即可

复制代码

⚠️ 报错处理：ERROR 3092 ... not configured properly → 执行 reset master; 后重新 START GROUP_REPLICATION。

三台都 ONLINE 后，在任意节点查 replication_group_members 应看到 3 行，MEMBER_STATE 全为 ONLINE。

6.4 实验：测试读写与同步

多主模式下，任意节点都能写，数据自动同步到全员：

# node1 建表插数据
mysql> CREATE DATABASE timinglee;
mysql> CREATE TABLE timinglee.userlist (username VARCHAR(10) PRIMARY KEY NOT NULL, password VARCHAR(50) NOT NULL);
mysql> INSERT INTO timinglee.userlist VALUES ('user1','111');
# node2 立刻能看到，并继续写
mysql> SELECT * FROM timinglee.userlist; # 看到 user1
mysql> INSERT INTO timinglee.userlist VALUES ('user2','222');
# node3 同样能看到 user1、user2，并继续写
mysql> INSERT INTO timinglee.userlist VALUES ('user3','333');
# 回到 node1 / node2 查询，三台数据完全一致 ✅

复制代码

七、MySQL Router 读写分离与负载均衡

7.1 为什么需要 Router？

MGR 组里有多台机器，应用总不能自己记着「哪台是主、哪台是从、主挂了换谁」吧？
MySQL Router 就是个「智能接线员」：应用只连 Router 的一个端口，Router 自动把 写请求 转给主库、读请求 转发给从库做负载均衡。主库一变，Router 跟着变，应用无感知。

7.2 路由算法（理论·重点）

策略	说明	典型场景
first-available	连列表里第一个可用节点；它挂了自动切下一个，恢复后切回	读写端口，单主写请求
next-available	取第一个可用节点；故障后永不自动切回原节点	不想故障回迁的静态后端
round-robin	轮询，新连接轮流分给每台后端，尽量均匀	只读端口，读请求负载均衡
round-robin-with-fallback	优先轮询从库；从库全挂才降级轮询主库	读写分离，读尽量走从

7.3 实验：安装、配置、测试

# 下载安装（独立主机 mysqlrouter，IP 172.25.254.40）
[root@mysqlrouter ~]# wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-community-8.4.7-1.el9.x86_64.rpm
[root@mysqlrouter ~]# dnf install mysql-router-community-8.4.7-1.el9.x86_64.rpm -y
# 配置文件
[root@mysqlrouter ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin # 读：轮询负载均衡
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available # 写：第一个可用节点（主库）
[root@mysqlrouter ~]# systemctl enable --now mysqlrouter.service
# 验证端口在监听
[root@mysqlrouter ~]# netstat -antlupe | grep mysql
tcp 0 0 0.0.0.0:7001 ... LISTEN ... 39587/mysqlrouter
tcp 0 0 0.0.0.0:7002 ... LISTEN ... 39587/mysqlrouter

复制代码

测试：先在任意 MySQL 节点给 root 开远程登录，然后应用通过 Router 端口连接：

# MySQL 节点上
mysql> CREATE USER root@'%' IDENTIFIED BY 'lee';
mysql> GRANT ALL ON *.* TO root@'%';
# 通过 Router 的读写端口连（7002）
[root@mysql-node1 ~]# mysql -uroot -plee -h172.25.254.40 -P7002
# 能连上即说明 Router 把请求正确路由到后端 MySQL ✅

复制代码

💡 用 watch -n1 lsof -i :3306 在各 MySQL 节点上能看到 Router 把连接均匀分发到了不同后端。

📌 声明：本文实验均基于 MySQL 8.3.0 源码编译 + RHEL9.8 环境，命令中的 IP、密码、路径可按你的实际环境替换。

[数据库] Mysql内容及相关实验

目录

一、为什么要学 MySQL 集群？

1.1 单台 MySQL 的三大痛点

1.2 集群能解决什么

1.3 本文的路线

二、MySQL 源码编译安装

2.1 为什么是企业级「源码编译」？

2.2 安装编译依赖

2.3 下载并解压源码

2.4 cmake 配置

2.5 编译并安装

2.6 部署：环境变量、运行用户、数据目录

2.7 数据初始化

2.8 启动 MySQL

2.9 安全初始化

2.10 验证

三、主从复制：一主一从 / 一主多从

3.1 主从复制是什么？

3.2 复制的三个线程

3.3 复制的三步骤

3.4 实验：编写 my.cnf 主配置文件

3.5 实验：建立同步专用账号

3.6 实验：配置一主一从

3.7 实验：测试同步

3.8 实验：向「一主多从」中加入新从库

3.9 主从架构的缺陷

四、主从架构优化技巧

4.1 延迟复制（Delay Replication）

4.2 慢查询日志（Slow Query Log）

4.3 GTID 模式（全局事务标识）

4.4 多线程并行回放（Parallel Replication）

4.5 半同步复制（Semi-Synchronous Replication）

五、MHA 高可用集群

5.1 什么是 MHA？

5.2 环境准备：保证数据一致性

5.3 安装 MHA 软件

5.4 修复检测代码 + 建立远程用户

5.5 配置文件

5.6 环境检测

5.7 手动切换

5.8 自动切换

5.9 VIP 漂移

六、MySQL 组复制 MGR

6.1 什么是 MGR？

6.2 实验：还原所有节点

6.3 实验：部署组复制

6.4 实验：测试读写与同步

七、MySQL Router 读写分离与负载均衡

7.1 为什么需要 Router？

7.2 路由算法（理论·重点）

7.3 实验：安装、配置、测试

新人须知

常见问题

关于我们