高性能MYSQL---数据库的读写分离(mysql-proxy)

小咪咪 2023-07-13 10:55 20阅读 0赞

### 文章目录 ###

*  1.MySQL读写分离
 *  2.为什么要读写分离？
 *  3.读写分离的实现

# 1.MySQL读写分离 #

Mysql的主从复制和Mysql的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。**Master数据库处理事务性增、删除、修改、更新操作（CREATE、INSERT、UPDATE、DELETE），而让Slave数据库处理SELECT操作，MYSQL读写分离前提是基于MYSQL主从复制，这样可以保证在Master上修改数据，Slave同步之后，WEB应用可以读取到Slave端的数据。** 简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库。

**使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70]

*  基于中间代理层实现  
    代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。

（1）mysql-proxy 为mysql开源项目，通过其自带的lua脚本进行SQL判断，虽然是mysql的官方产品，但是mysql官方不建议将其应用到生产环境

（2）Amoeba （变形虫）由陈思儒开发，曾就职与阿里巴巴，该程序由java语言进行开发，阿里巴巴将其应用于生成环境，它不支持事物和存储过程

*  如果业务压力不是很大的时候要做读写分离，取决于硬盘读取的性能，客户才满意，**读库（配置低），写库（配置高：固态硬盘，高速存储）**

# 2.为什么要读写分离？ #

因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。

# 3.读写分离的实现 #

**实验环境：**

*  selinux and firewalld disabled
 *  server1:172.25.254.1（master）
 *  server2:172.25.254.2（slave）
 *  server3:172.25.254.3（mysql-proxy）

server1和server2要实现主从复制，这里就不多赘述了，详情见上一篇博文

**mysql-proxy配置：**

在server3上

step1 配置mysql-proxy，创建主配置文件：

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
    cd /usr/local/
    ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
    cd /usr/local/mysql-proxy
    mkdir conf #需要手动创建该目录
    cd conf/	
    vim mysql-proxy.conf	#创建配置文件
    写入下面内容：注意不要加注释，否则会报错
    
      1 [mysql-proxy]
      2 user=root ##运行mysql-proxy用户
      3 proxy-address=0.0.0.0:3306 ##mysql-proxy运行ip和端口
      4 proxy-read-only-backend-addresses=172.25.254.2:3306 ##slave：只读
      5 proxy-backend-addresses=172.25.254.1:3306 ##master：可读写
      6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址
      7 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
      8 plugins=proxy
      9 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #日志位置
     10 log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
     11 daemon=true ##打入后台 
     12 keepalive=true ##mysql-proxy崩溃时，尝试重启（持续连接）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 1]  
step2 建立目录存放读写分离的日志：

mkdir /usr/local/mysql-proxy/log

![在这里插入图片描述][2020031119180710.png]  
step3 修改读写分离配置文件：

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
    修改以下内容：
     40                 min_idle_connections = 1,  #最小连接数
     41                 max_idle_connections = 2,  #最大连接数，最大连接数大于2时发生读写分离
    # 把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为2个便于测试。

![在这里插入图片描述][20200311191910255.png]  
step4 给文件设置权限，再启动mysql-proxy（否则会启动失败）：

原因：配置文件权限过大  
更改方法：修改文件权限

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf	#修改权限
    /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf	#启动mysql-proxy
    cat /usr/local/mysql-proxy/log/mysql-proxy.log	#查看到server1和server2已经添加上 
    netstat -antlp	#查看端口3306打开

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 2]  
step5 在master上授权一个新的用户：

mysql> grant insert,update,select on *.* to yy@'%' identified by 'Redhat.123';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> use westos;
    Database changed
    
    mysql> create table userinfo (
        -> username varchar(10) not null,
        -> phonenum varchar(10) not null);
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> desc userinfo;

![在这里插入图片描述][20200311192213159.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 3]  
step6 在proxy端监控3306端口情况，确保读写分离启动：

yum install lsof-4.87-4.el7.x86_64 -y
    lsof -i:3306

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 4]  
step7 使用真机通过server3连接数据库，直到lsof监控到读写分离出现：

mysql -h 172.25.254.3 -uyy -pRedhat.123	#打开几个shell多连接几次 #超过两个真机连接可以看到读写分离

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 5]  
注意：真机中如果没有mysql，直接用`yum install mariadb-server`安装

**测试：**

step1 master和slave状态都开启：  
在真机中通过server3连接数据库：

mysql -h 172.25.254.3 -uyy -pRedhat.123
    use westos;
    insert into userinfo values ('user1','111');	#插入数据
    select * from userinfo;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 6]  
注意：此时可以成功插入看到数据，并不能判断实现了读写分离，因为主从服务开着，随时都备份好了

step2 关闭server2（slave）：

stop slave;

在真机插入数据，却不能查看到数据，而在master上可以查看到插入的数据：

insert into userinfo values ('user2','222');
    select * from userinfo;

![在这里插入图片描述][20200311192833335.png]  
这里没有看到写入的数据，是因为执行查询读操作时，代理把读取转达到后端服务器server2上，而server2只有读权限，并且没有开启主从复制，所以server2没有更新代理在server1上的写操作内容，所以看不到。

**在server1查询:**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 7]  
发现server1可以看到，这说明实现了读写分离，写操作成功执行到了server1，因为server1有读写权限，而由于server2只读，并且关闭了主从复制，所以看不到新插入的数据。

开启server2的slave：

start slave;

在真机中再次select，可以查看到刚才插入的数据：

select * from userinfo;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 8]  
此时又可以看到插入的数据是因为：开启主从复制，从服务器server2及时更新到了主服务器server1上的写操作内容。  
说明：实现读写分离，读操作是在server2（slave）上

step3 关掉server1（master）：

systemctl stop mysqld

**在物理机上连接数据库，发现插入数据失败：**

insert into userinfo values ('user3','333');

![在这里插入图片描述][20200311193912122.png]  
说明：实现读写分离，写操作在server1（master）上

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200311191042898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020031119173716.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[2020031119180710.png]: https://img-blog.csdnimg.cn/2020031119180710.png
[20200311191910255.png]: https://img-blog.csdnimg.cn/20200311191910255.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200311192044471.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[20200311192213159.png]: https://img-blog.csdnimg.cn/20200311192213159.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200311192218261.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020031119232417.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200311192407549.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200311192704483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[20200311192833335.png]: https://img-blog.csdnimg.cn/20200311192833335.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200311192844489.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200311193447243.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ==,size_16,color_FFFFFF,t_70
[20200311193912122.png]: https://img-blog.csdnimg.cn/20200311193912122.png