MySQL-读写分离

读写分离核心概念

读写分离是将数据库的读操作（SELECT）和写操作（INSERT/UPDATE/DELETE）分发到不同数据库节点的架构模式。

MySQL 读写分离的核心思想

写操作(INSERT、UPDATE、DELETE)只在主库(Master)上执行
读操作(SELECT)在从库(Slave)上执行
通过主从复制(Master-Slave Replication)保持数据一致

一主多从(Master-Slave)

Master(主库)负责处理所有写请求，并将数据变更同步到从库
Slave(从库)负责处理读请求，提高查询性能
中间件或代理(如 MySQL Proxy、MyCat、ShardingSphere-Proxy)用于路由 SQL 请求

读写分离方案

客户端层实现

在代码中根据 select 和 insert 进行路由分类，性能比较好，无需任何硬件代理条件，缺点是需要开发人员的编写，运维人员无从下手；

中间件方案

中间件名称	开发语言	主要特点	读写分离支持	分库分表支持	连接池管理	负载均衡	高可用性	社区活跃度	适用场景
MySQL Router	C++	MySQL 官方轻量级中间件	✔️	❌	✔️	基础	主从切换	中等	简单读写分离，MySQL 生态
ProxySQL	C++	高性能，灵活路由规则	✔️	❌	✔️	高级(权重/延迟)	自动故障转移	高	生产环境读写分离
Amoeba	Java	简单易用，阿里巴巴开源	✔️	❌	✔️	轮询/权重	手动切换	低(已停止维护)	小型项目快速实现
MyCAT	Java	基于 Cobar 发展而来	✔️	✔️	✔️	多种策略	心跳检测	高	分库分表+读写分离
MaxScale	C	MariaDB 官方中间件	✔️	❌	✔️	动态负载	自动故障转移	高	MariaDB/MySQL 高可用
ShardingSphere-Proxy	Java	Apache 顶级项目	✔️	✔️	✔️	多种算法	分布式事务	非常高	企业级分布式方案
Atlas	C	360 基于 MySQL Proxy 改造	✔️	❌	✔️	基础	手动切换	低	替代 MySQL Proxy

Amoeba-方案

阿里巴巴 2012 年开源，后停止维护。若已使用 Amoeba 可逐步迁移到 MyCAT

核心功能：

基于 XML 配置的简单读写分离
支持多从库轮询/权重分配
通过dbServers.xml定义主从节点

环境准备

主机名	IP 地址	配置	系统
mysql-master	192.168.148.170	4C8G	openEuler 24.03 (LTS)
mysql-slave01	192.168.148.171	4C8G	openEuler 24.03 (LTS)
mysql-slave02	192.168.148.172	4C8G	openEuler 24.03 (LTS)
amoeba	192.168.148.173	4C8G	openEuler 24.03 (LTS)

安装 jdk 环境

bash

tar xf jdk-7u65-linux-x64.gz -C /usr/local/

# 设置环境变量
vi /etc/profile
export JAVA_HOME=/usr/local/jdk1.7.0_65
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH

# 加载环境
source /etc/profile

创建 amoeba 用户

sql

-- 创建同步用户
CREATE USER 'amoeba'@'%' IDENTIFIED BY '123456';

-- 设置权限
GRANT all ON *.* TO 'amoeba'@'%';
# 修改连接验证规则
# ALTER USER 'amoeba'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

-- 刷新权限
FLUSH PRIVILEGES;

安装 amoeba

bash

mkdir /usr/local/amoeba
tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
cd /usr/local/amoeba/conf/


# 在master节点进行授权允许amoeba程序能够连接Mysql集群，slave节点会同步权限
vi /usr/local/amoeba/conf/amoeba.xml
#30行改成登录amoeba的用户名
#32行改成登录amoeba的密码

<property name="authenticator">
 28                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
 29
 30                                         <property name="user">root</property>
 31
 32                                         <property name="password"></property>
 33
 34                                         <property name="filter">
 35                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">
 36                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
 37                                                 </bean>
 38                                         </property>
 39                                 </bean>
 40                         </property>

#115行设置默认连接名master
#117行设置写库master
#118行设置读库slaves
115                 <property name="defaultPool">server1</property>
116
117                 <!--  这里的注释去掉
118                 <property name="writePool">server1</property>
119                 <property name="readPool">server1</property>
120                 -->这里的注释去掉


vi /usr/local/amoeba/conf/dbServers.xml
#26行改成amoebauser
#29行密码改成123123
   13         <dbServer name="abstractServer" abstractive="true">
     14                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
     15                         <property name="manager">${defaultManager}</property>
     16                         <property name="sendBufferSize">64</property>
     17                         <property name="receiveBufferSize">128</property>
     18
     19                         <!-- mysql port -->
     20                         <property name="port">3306</property>
     21
     22                         <!-- mysql schema -->
     23                         <property name="schema">test</property>
     24
     25                         <!-- mysql user -->
     26                         <property name="user">root</property>
     27
     28                         <!--  mysql password # 这里打开注释
     29                         <property name="password">password</property>
     30                         -->


#45行name设置master，48行设置master主库IP地址
#52行name设置slave01，从库地址设置slave01的IP
# 有多个节点可以复制dbServer标签进行修改
     45         <dbServer name="server1"  parent="abstractServer">
     46                 <factoryConfig>
     47                         <!-- mysql ip -->
     48                         <property name="ipAddress">127.0.0.1</property>
     49                 </factoryConfig>
     50         </dbServer>
     51
     52         <dbServer name="server2"  parent="abstractServer">
     53                 <factoryConfig>
     54                         <!-- mysql ip -->
     55                         <property name="ipAddress">127.0.0.1</property>
     56                 </factoryConfig>
     57         </dbServer>
# multiPool修改成slaves。server1,server2改成slave01,slave02
     66         <dbServer name="multiPool" virtual="true">
     67                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
     68                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
     69                         <property name="loadbalance">1</property>
     70
     71                         <!-- Separated by commas,such as: server1,server2,server1 -->
     72                         <property name="poolNames">server1,server2</property>
     73                 </poolConfig>
     74         </dbServer>


# 修改amoeba启动脚本
vim /usr/local/amoeba/bin/amoeba
# 修改启动文件58行
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss128k"
# 改成：
DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"
# 启动amoeba
/usr/local/amoeba/bin/amoeba start &

创建 systemd 服务文件

bash

# 设置权限和用户
useradd -r -s /sbin/nologin amoeba
chown -R amoeba:amoeba /usr/local/amoeba

cat > /usr/lib/systemd/system/amoeba.service <<EOF
[Unit]
Description=Amoeba MySQL Proxy Server
After=network.target

[Service]
Type=forking
User=amoeba
Group=amoeba
Environment=JAVA_HOME=/usr/java/jdk1.8.0_131
Environment=PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:$JAVA_HOME/bin
ExecStart=/usr/local/amoeba/bin/amoeba start
ExecStop=/usr/local/amoeba/bin/amoeba stop
Restart=on-failure
RestartSec=5s
PIDFile=/usr/local/amoeba/bin/amoeba.pid
WorkingDirectory=/usr/local/amoeba

[Install]
WantedBy=multi-user.target

EOF
systemctl daemon-reload
systemctl enable amoeba
systemctl start amoeba

安装 mysql 客户端

bash

yum install -y mysql

测试读写分离

测试的客户端和数据库版本尽量保持一致

bash

mysql -uadmin -p'123456' -h192.168.148.175 -P8066 -e'select 1'

Atlas-方案

简介

Github：https://github.com/Qihoo360/Atlas

Atlas 是由 Qihoo-360 公司 web 平台部基础架构团队开发维护的一个基于 MySQL 协议的数据中间层项目。它在 MySQL 官方推出的 MySQL-Proxy-0.8.2 版本的基础上，修改了大量 bug，添加了很多功能特性。它在 MySQL 官方推出的 MySQL-Proxy0.8.2 版本的基础上，修改了大量 bug，添加了很多功能特性。

mermaid

graph TD
    Client --> Atlas
    Atlas --> Master[Master DB]
    Atlas --> Slave1[Slave DB 1]
    Atlas --> Slave2[Slave DB 2]
    Atlas --> Slave3[Slave DB 3]

主要功能特性

读写分离
- 智能 SQL 解析，自动路由 SELECT 到从库
- 事务内强制走主库
- 支持/master/注释强制走主库
连接池管理
- 最大支持 2000 个前端连接
- 后端连接复用
- 连接超时自动回收
分表功能
- 支持水平分表
- 基于范围/哈希的分表规则
- 分布式事务支持
高可用机制
- 主库故障自动检测
- 从库延迟监控
- 自动摘除异常节点

系统要求

仅支持 64 位 Linux 系统（CentOS 6.x/7.x 推荐）
MySQL 版本需 ≥5.1，建议 5.6+

安装配置 Atlas

bash

wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

安装路径默认为/usr/local/mysql-proxy

配置 Atlas

编辑/usr/local/mysql-proxy/conf/test.cnf

ini

[mysql-proxy]
# 管理接口配置
admin-username = user
admin-password = pwd
admin-address = 0.0.0.0:2345

# 后端数据库配置
proxy-backend-addresses = 主库IP:3306  # 写节点或者VIP地址
proxy-read-only-backend-addresses = 从库1IP:3306@2,从库2IP:3306@1  # 读节点及权重

# 认证配置（需先用encrypt工具加密密码）
pwds = 用户名:加密密码

# 性能参数
event-threads = 8  # 推荐CPU核数2-4倍
daemon = true       # 生产环境用守护进程模式
keepalive = true    # 启用monitor监控进程

proxy-address = 0.0.0.0:1234 #Atlas的工作监听端口（提供代理服务）
admin-address = 0.0.0.0:2345 #Atlas的管理监听端口

# 分表配置（库名.表名.分表字段.子表数）
tables = mydb.mytable.id.3

# IP白名单
client-ips = 192.168.1.0/24,10.0.0.1[3]

mysql 密码加密：

bash

/usr/local/mysql-proxy/bin/encrypt 123456

服务管理命令

bash

# 启动服务
/usr/local/mysql-proxy/bin/mysql-proxyd test start
# 检测服务
/usr/local/mysql-proxy/bin/mysql-proxyd test status
# 重启服务
/usr/local/mysql-proxy/bin/mysql-proxyd test restart
# 停止服务
/usr/local/mysql-proxy/bin/mysql-proxyd test stop


# 管理接口连接
mysql -uuser -ppwd -h127.0.0.1 -P2345
# 查看后端状态
SELECT * FROM backends;  # 在管理界面执行

# 客户端链接MySQL(host要连接Atlas的地址)
mysql -uroot -p'123456' -h192.168.148.171 -P1234

与 MHA 配合

ini

# 在主库故障时自动切换配置
proxy-backend-addresses = 新主库VIP:3306

MySQL-读写分离 ​

读写分离核心概念 ​