【MySQL数据库】MySQL读写分离

读写分离

概念

读写分离是为了优化数据库性能，通过将写操作（INSERT、UPDATE、DELETE）和读操作（SELECT）分别分配到不同的数据库实例上，以达到降低单一数据库负载、提高整体响应速度的目的。这通常通过数据库的主从复制技术来实现，主数据库负责处理写操作，从数据库负责处理读操作，并保持与主数据库的数据同步。

读写分离的动机

1. 性能优化：写操作通常比读操作更耗时，因此将两者分开可以显著提高数据库系统的整体性能。
2. 负载均衡：通过读写分离，可以将查询请求分散到多个从数据库上，减轻主数据库的负担。
3. 高可用性：在读写分离架构中，如果主数据库出现故障，从数据库可以迅速接管读操作，确保系统的持续运行。

读写分离的适用场景

读写分离通常适用于以下场景：

1. 读多写少：如果系统的查询操作远多于更新操作，那么读写分离可以显著提高性能。
2. 高并发：在高并发场景下，读写分离可以有效分散数据库请求，提高系统的处理能力。
3. 数据一致性要求不高的读操作：对于某些读操作，即使数据略有延迟也是可以接受的，这样可以使用读写分离来提高性能。

主从复制与读写分离

• 必要性：在生产环境中，单一数据库服务器无法满足高并发、高可用性和安全性需求。
• 实现方式：通过主从复制同步数据，再通过读写分离提升数据库的并发处理能力。类似于rsync，但rsync是对磁盘文件备份，而MySQL主从复制是对数据和语句的备份。

MySQL 读写分离原理

• 核心思想：只在主服务器上执行写操作，在从服务器上执行读操作。
• 实现方式：主数据库处理事务性操作，从数据库处理SELECT查询。通过数据库复制技术，将主数据库上的变更同步到从数据库。

MySQL读写分离的实现方式

1. 基于程序代码内部实现：
   • 优点：性能较好，不需要额外的硬件设备。
   • 缺点：需要开发人员实现，运维人员难以介入；对于大型复杂应用，代码改动可能较大。
2. 基于中间代理层实现：
   • 优点：易于运维和管理，可以实现更复杂的路由规则和负载均衡策略。
   • 缺点：可能增加系统的复杂性和延迟；需要额外的硬件设备或软件资源。
     常见的中间代理层实现方式包括MySQL-Proxy、Atlas、Amoeba和Mycat等。这些工具各有优缺点，选择时应根据具体的应用场景和需求进行权衡。

代表性程序

1. MySQL-Proxy
   • 开源项目，通过自带Lua脚本进行SQL判断。
   • 需要编写大量Lua脚本，对不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说较困难。
2. Atlas
   • 由奇虎360开发维护，基于MySQL协议的数据中间层项目。
   • 在mysql-proxy 0.8.2基础上优化，支持事务和存储过程。
   • 每天承载数十亿条读写请求。
3. Amoeba
   • 由陈思儒开发，曾就职于阿里巴巴。
   • 使用Java开发，易于使用且可移植性强。
   • 不支持事务和存储过程。
4. Mycat
   • 基于Java编写的数据库中间件，实现MySQL协议。
   • 核心功能是分库分表，配合主从模式可实现读写分离。

MySQL读写分离实验

搭建 MySQL 读写分离

Amoeba 服务器配置

1. 安装 Java 环境
因为 Amoeba 是基于 JDK 1.5 开发的，所以推荐使用 JDK 1.5 或 1.6 版本。

# 进入 /opt/ 目录
cd /opt/# 将 JDK 安装包复制到 /usr/local/ 目录
cp jdk-6u14-linux-x64.bin /usr/local/# 进入 /usr/local/ 目录
cd /usr/local/# 给 JDK 安装包执行权限
chmod +x jdk-6u14-linux-x64.bin# 执行 JDK 安装包
./jdk-6u14-linux-x64.bin
# 按 yes 确认，按 enter 继续# 将解压后的 JDK 目录重命名为 jdk1.6
mv jdk1.6.0_14/ /usr/local/jdk1.6# 编辑 /etc/profile 文件，配置 JAVA_HOME 环境变量
vim /etc/profile
# 在文件末尾添加以下内容
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin# 使配置生效
source /etc/profile# 检查 Java 版本
java -version

2. 安装 Amoeba 软件

# 创建 Amoeba 安装目录
mkdir /usr/local/amoeba# 将 Amoeba 安装包解压到 /usr/local/amoeba/ 目录
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/# 设置 Amoeba 目录权限
chmod -R 755 /usr/local/amoeba/# 检查 Amoeba 是否安装成功
/usr/local/amoeba/bin/amoeba
# 如显示 amoeba start|stop 说明安装成功

3. 配置 Amoeba 读写分离
首先，在 Master、Slave1、Slave2 的 MySQL 上开放权限给 Amoeba 访问。

GRANT ALL ON *.* TO 'test'@'192.168.80.%' IDENTIFIED BY '123.com';

然后，回到 Amoeba 服务器配置 Amoeba 服务。

# 进入 Amoeba 配置文件目录
cd /usr/local/amoeba/conf/# 备份 Amoeba 配置文件
cp amoeba.xml amoeba.xml.bak# 编辑 Amoeba 配置文件
vim amoeba.xml
# 修改以下内容：
# 第30行：设置 Amoeba 用户名为 amoeba
<property name="user">amoeba</property>
# 第32行：设置 Amoeba 用户密码为 123456
<property name="password">123456</property>
# 第115行：设置默认连接池为 master
<property name="defaultPool">master</property>
# 第117行：去掉注释，设置写连接池为 master
<property name="writePool">master</property>
# 第118行：去掉注释，设置读连接池为 slaves
<property name="readPool">slaves</property># 备份数据库配置文件
cp dbServers.xml dbServers.xml.bak# 编辑数据库配置文件
vim dbServers.xml
# 修改以下内容：
# 第23行：注释掉默认库配置，以防 MySQL 中没有 test 库时报错
<!-- <property name="schema">test</property> -->
# 第26行：设置连接 MySQL 的用户名为 test
<property name="user">test</property>
# 第28-30行：去掉注释，设置连接 MySQL 的用户密码为 123.com
<property name="password">123.com</property>
# 第45行：设置主服务器名为 master
<dbServer name="master" parent="abstractServer">
# 第48行：设置主服务器 IP 地址为 192.168.80.10
<property name="ipAddress">192.168.80.10</property>
# 第52行：设置从服务器名为 slave1
<dbServer name="slave1" parent="abstractServer">
# 第55行：设置从服务器1的 IP 地址为 192.168.80.11
<property name="ipAddress">192.168.80.11</property>
# 第58行：复制上面6行，设置从服务器2的名称为 slave2 和 IP 地址为 192.168.80.12
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.80.12</property>
# 第65行：设置虚拟从服务器名为 slaves
<dbServer name="slaves" virtual="true">
# 第71行：设置虚拟从服务器的连接池为 slave1 和 slave2
<property name="poolNames">slave1,slave2</property># 启动 Amoeba 服务
/usr/local/amoeba/bin/amoeba start&
# 检查 8066 端口是否开启，默认端口为 TCP 8066
netstat -anpt | grep java

测试读写分离

1. 安装 MariaDB

# 安装 MariaDB
yum install -y mariadb-server mariadb# 启动 MariaDB 服务
systemctl start mariadb.service

2. 测试读写分离
在客户端服务器上测试连接 Amoeba 服务器代理的 MySQL。

# 通过 Amoeba 服务器代理访问 MySQL
mysql -u amoeba -p123456 -h 192.168.80.20 -P8066# 在主服务器上创建数据库和表
USE db_test;
CREATE TABLE test (id INT(10), name VARCHAR(10), address VARCHAR(20));# 在两台从服务器上关闭同步并插入数据
STOP SLAVE;
USE db_test;
-- 在 slave1 上插入数据
INSERT INTO test VALUES('1', 'zhangsan', 'this_is_slave1');
-- 在 slave2 上插入数据
INSERT INTO test VALUES('2', 'lisi', 'this_is_slave2');# 在主服务器上插入数据
INSERT INTO test VALUES('3', 'wangwu', 'this_is_master');# 在客户端服务器上查询数据
USE db_test;
SELECT * FROM test;
# 客户端会分别向 slave1 和 slave2 读取数据，显示只有在两个从服务器上添加的数据，没有在主服务器上添加的数据# 在客户端服务器上插入数据
INSERT INTO test VALUES('4', 'qianqi', 'this_is_client');
# 只有主服务器上有此数据# 在两个从服务器上执行 START SLAVE; 实现同步在主服务器上添加的数据
START SLAVE;

问答

读写分离：
1）基于程序代码内部实现：优点：性能好，不需要额外的硬件设备支出；缺点：由开发人员负责实现和维护，在一些现有的大型应用中现实读写分离对代码改动会较为困难
2）基于中间代理层实现：优点：实现起来较为简单，不需要重构代码；缺点：需要额外的硬件设置支出，由运维人员负责维护
典型代表：mysql-proxy、mycat、ameoba