MySQL——主从复制与读写分离
·
一、业务背景
| 项目 | 详情 |
|---|---|
| 时间 | 2014年6月 - 2015年9月 |
| 产品类型 | 互联网动态站点 商城 |
| 用户数量 | 10000 - 12000（用户量猛增） |
| PV | 100000 - 500000（24小时访问次数总和） |
| QPS | 50 - 100*（每秒访问次数） |
| DAU | 2000（每日活跃用户数） |
随着活跃用户数增多，读取数据的请求变多，单台MySQL已无法满足需求。核心诉求是：使用多台MySQL数据库服务器降低单台压力，实现集群架构的稳定性和高可用性，同时保证数据的一致性和完整性。解决方案聚焦于主从复制 + 读写分离。
二、MySQL读写分离概述
2.1 读写分离的目的
数据库负载均衡：当数据库请求增多时，单例数据库无法满足业务需求，需要进行数据库实例的扩容，多台数据库同时响应请求。
读写分离：数据库的负载均衡不同于其他服务的负载均衡，因为数据要求一致性。基于主从复制的基础上，常见的数据库负载均衡使用读写分离方式——写操作走主数据库，读操作走从数据库。可以认为数据库读写分离是一种特殊的负载均衡实现。
2.2 读写分离的常见实现方式
方式一：业务代码实现读写分离
在业务代码中判断数据操作是读还是写，读连接从数据库服务器，写连接主数据库服务器。以LNMP架构为例，需要通过PHP代码实现读写分离。
方式二：中间件代理方式
业务代码不直接连接数据库，而是先请求到中间件服务器（代理），由代理服务器判断读操作走从库、写操作走主库。
常见中间件方案：
| 中间件 | 来源 | 说明 |
|---|---|---|
| MySQL Proxy | MySQL官方 | 测试版，不再维护 |
| Atlas | 奇虎360 | 基于MySQL Proxy |
| DBProxy | 美团点评 | - |
| Amoeba | 早期阿里巴巴 | - |
| Cobar | 阿里巴巴 | - |
| MyCat | 基于阿里开源的Cobar | 国内最活跃 |
| Kingshard | - | Go语言开发 |
| ProxySQL | - | - |
如何选择？
- 业务实现读写分离：操作方便，成本低，当前开发框架基本支持。
- 中间件代理：除了实现读写分离，还可以作为数据库集群的管理平台。
三、MySQL主从复制的三种方式
3.1 基于SQL语句的复制（SBR）
优点：
- 历史悠久，技术成熟
- binlog文件较小
- binlog中包含所有数据库更改信息，可用于审核数据库安全
- binlog可用于实时还原，而不仅仅用于复制
- 主从版本可以不一样，从服务器版本可以比主服务器版本高
缺点：
- 不是所有UPDATE语句都能被复制，尤其是包含不确定操作时
- 复制需要进行全表扫描的UPDATE时，需要比RBR请求更多的行级锁
- 对于复杂语句，在从服务器上的耗资源情况更严重
- 数据表必须几乎和主服务器保持一致，否则可能导致复制出错
- 执行复杂语句出错时会消耗更多资源
3.2 基于行的复制（RBR）
优点：
- 任何情况都可以被复制，最安全可靠
- 和其他大多数数据库系统的复制技术一样
- 多数情况下，从服务器上的表如果有主键，复制会快很多
缺点：
- binlog大了很多
- 复杂的回滚时binlog中会包含大量数据
- 主服务器执行UPDATE时，所有发生变化的记录都会写入binlog，频繁导致binlog并发写问题
- 无法从binlog中看到复制了什么语句
3.3 混合模式复制（MBR）
MySQL 5以后出现的复制方式，结合SBR和RBR的特点。
四、MySQL主从复制的原理
4.1 什么是主从复制？
主从复制用来建立一个和主数据库完全一样的数据库环境（从数据库），主数据库一般是准实时的业务数据库。
4.2 主从复制的作用
- 数据热备：主数据库故障后可切换到从数据库继续工作，避免数据丢失。
- 架构扩展：业务量增大、I/O访问频率过高时，做多库存储，降低磁盘I/O访问频率，提高单机I/O性能。
- 读写分离：使数据库能支撑更大并发，尤其在报表场景中，报表SQL走从库不会影响前台服务。
4.3 主从复制原理详解
核心机制：主数据库的 bin-log二进制日志 记录了所有SQL语句，目标是将主库bin-log的SQL语句复制过来，让从库在 relay-log重做日志文件 中再执行一次这些SQL语句。
主从复制涉及 三个线程：
| 线程 | 位置 | 作用 |
|---|---|---|
| binlog dump thread | 主库 | 每当从库连接时，主库创建一个线程发送binlog内容到从库 |
| I/O线程 | 从库 | 连接主库，请求主库发送binlog更新记录，拷贝到本地relay log文件 |
| SQL线程 | 从库 | 读取relay log中的更新事件并执行 |
复制流程五步骤：
步骤一：主库的更新事件（update、insert、delete）被写入binlog
↓
步骤二：从库发起连接，连接到主库
↓
步骤三：主库创建binlog dump thread线程，把binlog内容发送到从库
↓
步骤四：从库启动后创建I/O线程，读取主库传来的binlog内容并写入relay log
↓
步骤五：从库创建SQL线程，从relay log中读取内容，从Exec_Master_Log_Pos位置开始执行更新事件，
将更新内容写入slave的数据库
五、实验环境规划
一、环境信息
| 项目 | 版本/说明 |
|---|---|
| 操作系统 | CentOS 7.x / Rocky Linux 8.x（64位） |
| MySQL | 8.0.x |
| JDK | jdk1.8.0_192（仅 MyCAT 节点需要） |
| MyCAT | Mycat-server-1.6.5 |
| 主从复制方式 | 基于 bin-log 的异步复制 |
| 读写分离实现 | MyCAT 中间件代理方式 |
二、IP 地址规划
| 主机 | IP地址 | 角色 | 安装内容 |
|---|---|---|---|
| 服务器A | 192.168.30.122 |
MySQL Master（主库） | MySQL 服务端 |
| 服务器B | 192.168.30.124 |
MySQL Slave（从库） | MySQL 服务端 |
| 服务器C | 192.168.30.126 |
MyCAT 中间件 | JDK + MySQL 客户端 |
三、架构拓扑
┌──────────────────────────┐
│ 192.168.30.126 │
│ MyCAT 中间件 │
│ 客户端:8066 管理:9066 │
└────────────┬─────────────┘
│
┌────────────────┴────────────────┐
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ 192.168.30.122 │ bin-log │ 192.168.30.124 │
│ MySQL Master │ ──────────▶ │ MySQL Slave │
│ (写操作) │ 异步复制 │ (读操作) │
└──────────────────┘ └──────────────────┘
实验前准备：三台服务器统一操作
Step 1：关闭防火墙（实验环境）
三台服务器全部执行：
systemctl stop firewalld
systemctl disable firewalld
# 或
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
Step 2：配置 MySQL 客户端（方便后续操作）
三台都执行，避免每次敲全路径：
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
Step 3：准备依赖包
https://download.csdn.net/download/qq_44769717/92997521
实验一：MySQL 主从复制
1-1 Master 配置（192.168.30.122）
前置按照mysql
yum install -y mariadb-server mariadb
# 按照完之后启动
systemctl start mariadb
# 测试mysql
mysql -uroot -p
#然后回车进入mysql
① 编辑 MySQL 配置文件
vim /etc/my.cnf
在 [mysqld] 段添加：
[mysqld]
log-bin = mysql-bin
server-id = 1
重启 MySQL：
systemctl restart mariadb
② 创建 Slave 复制用户
用 root 登录 MySQL：
mysql -uroot -p
#回车进入mysql
-- MySQL 8.0 正确写法：先创建用户，再授权
CREATE USER 'slave'@'192.168.30.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.30.%';
FLUSH PRIVILEGES;
验证：
SELECT user, host, plugin FROM mysql.user WHERE user = 'slave';
期望看到 plugin 为 mysql_native_password。
③ 重置 binlog（清理历史遗留）
RESET MASTER;
④ 查看 Master 状态
SHOW MASTER STATUS;
输出示例：
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000001 | 155 |
+------------------+----------+
务必记录这两个值，后续 Slave 配置要用。
1-2 Slave 配置（192.168.30.124）
① 编辑 MySQL 配置文件
vim /etc/my.cnf
在 [mysqld] 段添加：
[mysqld]
server-id = 2
重启 MySQL：
systemctl restart mariadb
② 处理克隆实例的 UUID 冲突
提示：如果 Slave 是从 Master 克隆的虚拟机，两者的
auto.cnf中 server-uuid 相同，主从无法互相识别。必须删除该文件后重启，让 MySQL 自动生成新 UUID。
rm -f /usr/local/mysql/data/auto.cnf
systemctl restart mariadb
③ 测试网络连通性
在 Slave 上测试能否连接 Master：
mysql -h 192.168.30.122 -uslave -p'123456' -e "SELECT 1;"
如果返回 1 说明网络和用户均正常。
④ 清除旧的主从配置
STOP SLAVE;
RESET SLAVE ALL;
提示：
RESET SLAVE ALL比RESET SLAVE更彻底，会把之前残留的CHANGE MASTER TO配置全部清掉，避免指向错误的 binlog 文件。
⑤ 配置主从关系
先去主mysql里查看
回到 Slave上用新值执行将 Master上 SHOW MASTER STATUS 的 File 和 Position 值填入：
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO
MASTER_HOST='192.168.30.122',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
-- 这里填Master新File值
MASTER_LOG_FILE='mysql-bin.000001',
-- 这里填Master新Position值
MASTER_LOG_POS=245;
启动复制：
START SLAVE;
⑥ 验证主从状态
SHOW SLAVE STATUS\G;
目标：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1-3 主从复制验证
在 Master 上创建测试数据：
CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));
INSERT INTO test_table (name) VALUES ('hello_master');
在 Slave 上查询：
USE test_db;
SELECT * FROM test_table;
能查到数据即主从复制成功。
实验二：MyCAT 读写分离
注意
读写分离在主从复制的基础上进行操作
2-1 后端 MySQL 准备（Master .122 和 Slave .124 都要做）
① 创建 tp5shop 数据库和用户
两台 MySQL 分别执行：
-- 创建业务数据库
CREATE DATABASE IF NOT EXISTS tp5shop;
-- 尝试删掉可能残留的空壳用户
DROP USER 'tp5shop'@'192.168.30.%';
-- 创建用户（MariaDB 语法）
CREATE USER 'tp5shop'@'192.168.30.%' IDENTIFIED BY '$Abc3721';
-- 授权
GRANT ALL PRIVILEGES ON tp5shop.* TO 'tp5shop'@'192.168.30.%';
GRANT SELECT ON *.* TO 'tp5shop'@'192.168.30.%';
FLUSH PRIVILEGES;
-- 确认
SELECT user, host FROM mysql.user WHERE user = 'tp5shop';
FLUSH PRIVILEGES;
主mysql
从mysql:
验证：
SELECT user, host, plugin FROM mysql.user WHERE user = 'tp5shop';
SHOW DATABASES LIKE 'tp5shop';
主mysql
从mysql
2-2 MyCAT 服务器安装（192.168.30.126）
① 安装 JDK
# 解压
tar -xvzf jdk-8u261-linux-x64.tar.gz
mv jdk1.8.0_261/ /usr/local/java
# 配置环境变量
# 1.简单版
echo 'PATH=/usr/local/java/bin:$PATH' >> /etc/profile
source /etc/profile
# 2.完整版
cat >> /etc/profile << 'EOF'
# Java Environment
export JAVA_HOME=/usr/local/java
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib
export PATH=$JAVA_HOME/bin:$PATH
EOF
source /etc/profile
# 验证
java -version
简单版
完整版
② 安装 MySQL 客户端（仅客户端，不需要服务端）
yum install -y mariadb
提示：MyCAT 只是 Java 中间件，不存数据，不需要安装
mysqld服务端。装mariadb包只为了获得mysql命令行工具用于测试连接。
验证：
which mysql
③ 安装 MyCAT
tar -xvzf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz -C /usr/local
2-3 配置 MyCAT
① 配置 server.xml
编辑 /usr/local/mycat/conf/server.xml，确保 root 用户配置如下：
vim /usr/local/mycat/conf/server.xml
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
② 配置 schema.xml（核心文件）
cat > /usr/local/mycat/conf/schema.xml << 'EOF'
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3">
</schema>
<dataNode name="dn3" dataHost="localhost1" database="tp5shop" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.30.122:3306" user="tp5shop" password="$Abc3721">
<readHost host="hostS2" url="192.168.30.124:3306" user="tp5shop" password="$Abc3721" />
</writeHost>
</dataHost>
</mycat:schema>
EOF
提示：
schema标签必须添加dataNode="dn3"属性，否则 MyCAT 不知道路由到哪个后端数据库dataNode的database必须和后端真实数据库名一致（这里是tp5shop，不是默认的db3）balance="1"必须设置，balance="0"表示不开启读写分离writeHost和readHost分别指向 Master（.122）和 Slave（.124）
③ 配置 MySQL 客户端默认认证（解决 MySQL 8.0 客户端兼容性）
cat >> /etc/my.cnf << 'EOF'
[client]
default-auth=mysql_native_password
EOF
提示：MySQL 8.0 客户端默认使用
caching_sha2_password连接，MyCAT 1.6.5 前后端握手都不支持该协议。不加此配置，连接 MyCAT 8066 端口时会报ERROR 1045 Access denied for user 'root'。
2-4 启动与验证
① 启动 MyCAT
# 方式1
/usr/local/mycat/bin/mycat start
# 方式2
/usr/local/mycat/bin/mycat console
mycat console 和 mycat start 的区别
两个命令都在 /usr/local/mycat/bin/ 目录下，用来启动 MyCAT，但运行方式不同
命令 运行方式 日志输出 终端关闭后 适用场景 mycat console前台运行 直接打印在终端屏幕上 MyCAT 会停止 调试、排错、看实时日志 mycat start后台守护进程 写入 logs/wrapper.log文件继续运行 生产环境、正式使用
检查进程和端口：
netstat -lntp | grep 8066
应看到 java 进程监听 8066。
② 看日志确认后端连接正常
tail -20 /usr/local/mycat/logs/mycat.log
应该看到 connectionAcquired 信息，没有任何 connectionError 或 Access denied。
③ mycat客户端连接测试（8066 端口）
mysql -h127.0.0.1 -uroot -p123456 -P8066
进入后测试：
SHOW DATABASES;
-- 应看到 TESTDB
USE TESTDB;
SHOW TABLES;
-- 建表测试
CREATE TABLE test(id INT, name VARCHAR(20));
INSERT INTO test VALUES(1, 'mycat_test');
SELECT * FROM test;
④ mycat客户端测试（9066 端口）
mysql -h127.0.0.1 -uroot -p123456 -P9066
SHOW @@help;
SHOW @@heartbeat;
心跳正常说明读写分离已生效。
查看是否心跳正常
-- 查看数据源详情（连接数、读写计数、状态）
show @@datasource;
-- 查看心跳状态（你已经验证过）
show @@heartbeat;
-- 查看所有管理命令帮助
show @@help;
-- 查看MyCat运行时参数
show @@server;
心跳状态：
执行 SHOW @@heartbeat; 后，关键字段含义如下：
- RS_CODE = 1：相当于“ping 通了”。MyCAT 向后端 MySQL 发送
SELECT user()心跳检测，MySQL 正常返回结果即为 1，这是判断后端节点健康的核心依据。 - RETRY = 0：重试次数为 0，说明从 MyCAT 启动至今，该节点心跳一次都未失败过，无需重试。
- EXECUTE_TIME = 1,1,1：最近三次心跳的执行耗时（单位：毫秒）。三个 1 说明响应极快，网络通畅。如果心跳失败，此处会显示为空或异常值。
- STATUS = idle：连接池当前状态为空闲，等待处理 SQL 请求。如果状态变为
error，则说明该节点已挂掉。 - STOP = false：表示该节点未被手动停用。可以通过命令
STOP @@HEARTBEAT 'hostM1'将其设为true，届时 MyCAT 将不再向此节点转发请求。false表示节点正常工作。 - LAST_ACTIVE_TIME：最后一次心跳成功的时间戳。可以间隔一段时间（如10秒）再次执行
SHOW @@heartbeat;，观察此时间戳是否更新，以确认心跳在持续运行。
2-5 读写分离验证
方法一：分别在 Master 和 Slave 查看写入数据
# Master（.122）
mysql -uroot -p
SELECT * FROM tp5shop.test;
# Slave（.124）
mysql -uroot -p
SELECT * FROM tp5shop.test;
主mysql
从mysql
两边数据一致，说明：
- MyCAT 写操作 → 路由到 Master（.122）
- Master → binlog 复制到 Slave（.124）
- MyCAT 读操作 → 路由到 Slave（.124）
方法二：主从mysql远程连接mycat测试
主mysql测试
从mysql测试
方法三：更直观的测试读写分离
1.在 Slave 上直接插入一条独有数据
在 Slave 服务器（192.168.30.124）上执行：
USE tp5shop;
INSERT INTO test VALUES (999, 'slave_only_data_from_124');
SELECT * FROM test;
2.在 Master 上确认没有这条数据
USE tp5shop;
SELECT * FROM test;
3.通过 MyCAT 查询
mysql -h192.168.30.126 -uroot -p123456 -P8066 --default-auth=mysql_native_password
USE TESTDB;
SELECT * FROM test;
4.判断结果
| 场景 | MyCAT 能否查到 Slave 独有数据 | 结论 |
|---|---|---|
| ✅ 能查到 | 读操作确实路由到了 Slave | 读写分离生效 |
| ❌ 查不到 | 读操作走了 Master 或未开启读写分离 | 读写分离未生效 |
核心逻辑：Slave 上这条数据是绕过主从复制直接插入的，Master 上根本没有。如果 MyCAT 能查出来，唯一的解释就是 MyCAT 把这条 SELECT 请求分发给了 Slave。物理隔离证明比任何日志都直观。
完整操作速查表
| 实验 | 步骤 | 服务器 | 关键命令 |
|---|---|---|---|
| 主从复制 | 1 | Master .122 | vim /etc/my.cnf → log-bin + server-id=1 |
| 主从复制 | 2 | Master .122 | CREATE USER 'slave'@'...' IDENTIFIED WITH mysql_native_password |
| 主从复制 | 3 | Master .122 | RESET MASTER; SHOW MASTER STATUS; |
| 主从复制 | 4 | Slave .124 | vim /etc/my.cnf → server-id=2 |
| 主从复制 | 5 | Slave .124 | rm -f auto.cnf（克隆环境） |
| 主从复制 | 6 | Slave .124 | RESET SLAVE ALL; CHANGE MASTER TO ...; START SLAVE; |
| 主从复制 | 7 | Slave .124 | SHOW SLAVE STATUS\G → 双 Yes |
| 读写分离 | 1 | Master+Slave | CREATE USER 'tp5shop'@'...' IDENTIFIED WITH mysql_native_password |
| 读写分离 | 2 | MyCAT .126 | 装 JDK + MySQL 客户端 |
| 读写分离 | 3 | MyCAT .126 | 写 server.xml + schema.xml |
| 读写分离 | 4 | MyCAT .126 | /etc/my.cnf → default-auth=mysql_native_password |
| 读写分离 | 5 | MyCAT .126 | mycat start → mysql -P8066 连接验证 |
openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构
更多推荐
21
0- 0
已为社区贡献8条内容
所有评论(0)