死磕数据库系列（九）：MySQL 读写分离详解

上一期我们学习了：死磕数据库系列（八）：MySQL 主从同步详解 ，当业务量到达一定程度时，数据库的负载压力就会随之越来越明显，这个时候我们就面临如何解决这个问题的困境。我们发现其实数据库大部分的负载压力源于读，而不是写，所以，读写分离技术就随之出现。

但是，有一些技术同学可能对于“读写分离”了解不多，认为数据库的负载问题都可以使用“读写分离”来解决。这其实是一个非常大的误区，我们要用“读写分离”，首先应该明白“读写分离”是用来解决什么样的问题的，而不是仅仅会用这个技术。

读写分离概述

读写分离的基本原理

基本的原理是让主数据库处理事务性增、删、改操作（INSERT、DELETE、UPDATE），而从数据库处理 SELECT 查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

为什么要读写分离

• 面对越来越大的访问压力，单台的服务器性能成为瓶颈，需要分担负载。
• 因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
• 但是数据库的“读”（读10000条数据可能只要5秒钟）。
• 所以读写分离，解决的是，数据库的写入，影响了查询的效率。

什么时候要读写分离

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

MySQL读写分离的基础

实现方式主要基于 mysql 的主从复制，通过路由的方式使应用对数据库的写请求只在master上进行，读请求在slave上进行。

实现读写分离的原理与方案

1、基于 MySQL proxy 代理的方式

在应用和数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

• 实现原理

• 开源方案

MySQL的代理常见的是mysql-proxy、cobar、mycat、Atlas等。这种方式对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。

• mysql-proxy是一个轻量的中间代理，是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，依靠内部一个lua脚本实现读写语句的判断。项目地址：https://github.com/mysql/mysql-proxy ，该项目已经六七年没有维护了，官方也不建议应用于生成环境。
• cobar是阿里提供的一个中间件，已经停止更新。项目地址：httphttps://github.com/alibaba/cobar
• mycat的前身就是cobar，活跃度比较高，完全使用java语言开发。项目地址：https://github.com/MyCATApache/Mycat-Server，该项目当前已经有8.3k的点赞量。
• moeba（变形虫）是阿里工程师陈思儒基于java开发的一款数据库读写分离的项目(读写分离只是它的一个小功能)，与MySQL官方的MySQL Proxy相比，作者强调的是amoeba配置的方便（基于XML的配置文件，用SQLJEP语法书写规则，比基于lua脚本的MySQL Proxy简单）。下载地址：https://sourceforge.net/projects/amoeba/。
• Atlas奇虎360的一个开源中间代理，是在mysql官方mysql-proxy 0.8.2的基础上进行了优化，增加一些新的功能特性。项目地址: https://github.com/Qihoo360/Atlas，该项目当前已经有4.4k的点赞量。

基于应用内路由的方式

基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行sql。

• 实现原理

• 实现方案

基于spring的aop实现: 用aop来拦截spring项目的dao层方法，根据方法名称就可以判断要执行的sql类型(即是read还是write类型)，进而动态切换主从数据源。类似项目有：

多数据源切换：https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter

基于 mysql-connector-java 的 jdbc 驱动方式

• 实现原理

使用mysql驱动Connector/J的可以实现读写分离。即在jdbc的url中配置为如下的形示：

jdbc:mysql:replication://master,slave1,slave2,slave3/test

• 实现方案

java程序通过在连接MySQL的jdbc中配置主库与从库等地址，jdbc会自动将读请求发送给从库，将写请求发送给主库，此外，mysql的jdbc驱动还能够实现多个从库的负载均衡。

• 关于mysql的jdbc说明官方文档地址https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html
• 关于mysql的读写分离文档地址：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-source-replica-replication-connection.html

基于sharding-jdbc的方式

sharding-sphere是强大的读写分离、分表分库中间件，sharding-jdbc是sharding-sphere的核心模块。

• 实现原理

• 实现方案

sharding-jdbc 可以与springboot集成。官方网址：https://shardingsphere.apache.org/

    org.apache.shardingsphere    sharding-jdbc-spring-boot-starter    4.0.1

总结

以上四种方案各有优缺点，基于MySQL proxy代理的方式对于应用来说相对简单，但是在项目稳定性、事务支持性等方面还存在问题；而基于应用内路由的方式固然灵活度比较高，但是也增加了应用逻辑的复杂度；基于mysql-connector-java的jdbc驱动和sharding-jdbc的方式在使用上相对简单，但限制了需要使用java开发。

读写分离实战操作（主从复制基础上）

安装 Java 环境

amobea 服务器IP为192.168.169.202（在主从复制的基础上进行读写分离）

因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。 将jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上传到/opt目录下。 cd /opt/cp jdk-6u14-linux-x64.bin /usr/local/ cd /usr/local/chmod +x jdk-6u14-linux-x64.bin ./jdk-6u14-linux-x64.bin按空格到后一行，按yes，按回车。 mv jdk1.6.0_14/ /usr/local/jdk1.6 vim /etc/profileexport JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin#在末尾加入 source /etc/profilejava -version ##安装 Amoeba软件##mkdir /usr/local/amoebatar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/chmod -R 755 /usr/local/amoeba//usr/local/amoeba/bin/amoeba//如显示amoeba start|stop 说明安装成功

配置 Amoeba读写分离，两个 Slave 读负载均衡

在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问。

grant all on *.* to test@'192.168.169.%' identified by '123456';

amoeba服务器配置amoeba服务

cd /usr/local/amoeba/conf/ cp amoeba.xml amoeba.xml.bak vim amoeba.xml                  #修改amoeba配置文件 30修改amoeba 32修改123456 115修改master 117去掉注释–masterslaves

修改数据库配置文件

cp dbServers.xml dbServers.xml.bak vim dbServers.xml               #修改数据库配置文件 23注释掉作用：默认进入test库 以防mysql中没有test库时，会报错 26修改test 28-30去掉注释123456 45修改，设置主服务器的名Master 48修改，设置主服务器的地址192.168.223.10 52修改，设置从服务器的名slave1 55修改，设置从服务器1的地址192.168.223.9 58复制上面6行粘贴，设置从服务器2的名slave2和地址192.168.223.11 65修改 71修改slave1,slave2 /usr/local/amoeba/bin/amoeba start&         #启动Amoeba软件，按ctrl+c 返回 netstat -anpt | grep java             #查看8066端口是否开启，默认端口为TCP 8066

测试读写分离

在客户端（192.168.169.200）服务器上进行测试：

使用yum快速安装MySQL虚拟客户端

yum install -y mariadb mariadb-server mysql -u amoeba -p123456 -h 192.168.169.201 -P8066   

通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从服务器

主服务器上

create database zzz; use zzz;create table ceshi (id int(10),name varchar(10),address varchar(10));

关闭两个从服务器同步

stop slave;         #关闭同步 use zzz;

在两个从服务器分别加数据

在主服务器加数据

查看客户端服务器

use zzz;select * from test;   客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据 insert into test values('4','zb','four');   只有主服务器上有此数据

之后在两个从服务器上执行 start slave; 即可实现同步在主服务器和客户机上添加的数据

start slave；

更多关于MySQL读写分离实践的操作与配置，请参考以下往期推送的文章：

• Mysql+Mycat实现数据库主从同步与读写分离
• Sharding-Jdbc 实现读写分离
• SpringBoot 实现 MySQL 读写分离技术
• ProxySQL+Mysql实现数据库读写分离实战

参考链接如下： https://blog.csdn.net/weixin_44842613/article/details/116604132 https://blog.csdn.net/FateZRK/article/details/125526077