使用 MySQLProxy实现基于主从复制(半同步复制)的 Mysql 读写分离

朴灿烈づ我的快乐病毒、 2023-07-13 04:09 96阅读 0赞

# 一.读写分离的作用 #

## 1.什么是读写分离？ ##

MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。

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

简单来说，读写分离的基本原理就是，在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

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

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。  
但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

## 3.什么时候要读写分离？ ##

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库 主从同步 。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是 表折分，或是搜索引擎。都是解决方法。

## 4.主从复制与读写分离 ##

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论在安全性，高可用性还是高并发等各个方面是不能满足需求的。因此通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。优点类似于我们前面学过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据，语句做备份。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70]

## 5.前较为常见的Mysql读写分离分为以下两种： ##

### 1）基于程序代码内部实现 ###

在代码中根据select，insert，进行路由分类，这种方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

### 2） 基于中间代理层实现 ###

代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接受到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。

（1）`mysql_proxy`。mysql\_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。

（2）`Atlas`。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

（3）Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存储过程。

经过上述简单的比较，不是所有的应用都能够在基于程序代码中实现读写分离，像一些大型的java应用，如果在程序代码中实现读写分离对代码的改动就较大，所以，像这种应用一般会考虑使用代理层来实现，那么**今天就使用mysql\_proxy为例，完成主从复制和读写分离。**

# 二、MySQLProxy介绍 #

下面使用MySQL官方提供的数据库代理层产品MySQLProxy搭建读写分离。  
MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 1]

# 三、基于主从复制(半同步复制)使用 MySQLProxy实现 Mysql 读写分离 #

## 实验准备 ##

1.`selinux`和`firewalld`状态为`disabled`

2.各主机信息如下：

<table> 
 <thead> 
  <tr> 
   <th>主机</th> 
   <th>ip</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server1（主库）</td> 
   <td>172.25.63.1</td> 
  </tr> 
  <tr> 
   <td>server2（从库）</td> 
   <td>172.25.63.2</td> 
  </tr> 
  <tr> 
   <td>server3（代理服务器）</td> 
   <td>172.25.63.3</td> 
  </tr> 
  <tr> 
   <td>物理机（客户端）</td> 
   <td>172.25.63.250</td> 
  </tr> 
 </tbody> 
</table>

3.在server1和server2上做好主从复制

可以参考博文：[https://blog.csdn.net/qq\_35887546/article/details/104757085][https_blog.csdn.net_qq_35887546_article_details_104757085]

## 配置代理服务器server3 ##

<1>下载软件:mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz，并将其解压到/usr/local目录下：

[root@server3 Mysql]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/

`—C`表示指定解压目录

<2>为了方便，做软链接

[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

<3>为了方便,编辑系统的环境变量：这样就能直接执行mysql-proxy命令,而不需要进入bin目录下执行命令：./mysql-proxy

[root@server3 bin]# vim ~/.bash_profile
    PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
    [root@server3 mysql-proxy]# source ~/.bash_profile

<4>在解压之后的目录中建立目录`conf`（配置文件目录）和`log`（日志目录），用来存放日志和配置文件，并编辑`conf`目录下的配置文件`mysql-proxy.conf`文件。

[root@server3 bin]# cd /usr/local/mysql-proxy
    [root@server3 mysql-proxy]# mkdir conf
    [root@server3 mysql-proxy]# cd conf/
    [root@server3 conf]# vim mysql-proxy.conf
     
     [mysql-proxy]
     daemon=true     #定义以守护进程模式启动(打入后台)
     user=root       #执行mysqql-proxy的用户(这个可写可不写,因为运行mysql-proxy的本身就是root用户)
     pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid       #定义mysql-proxy的pid文件路径
     plugins=proxy              #加载proxy的插件
     log-level=debug   #定义log日志级别,由高到低分别有(error|warning|info|message|debug),因为这里是测试,所以定义日志级别为debug
     log-file=/usr/local/mysql-proxy/log/mysql-proxy.log       #定义mysql-proxy的log文件路径
     keepalive=true        #使进程在异常关闭后能够自动恢复(保持连接) 
     proxy-address=172.25.83.3:3306       #调度器IP
     proxy-read-only-backend-addresses=172.25.83.2:3306   #定义后端只读服务器地址(从库IP)
     proxy-backend-addresses=172.25.83.1:3306         #定义后端主服务器地址(主库IP)
     proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua    #自带的lua脚本文件所在位置

值的注意的是：该配置文件中不能有空格，否则会报错。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 2]

<5>编辑自带的lua脚本

[root@server3 mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
     
     38 if not proxy.global.config.rwsplit then
     39         proxy.global.config.rwsplit = { 
     40                 min_idle_connections = 1,			#改为1
     41                 max_idle_connections = 2,			#改为2
     42 
     43                 is_debug = false
     44         }

这里1,2的意思是：MySQL Proxy会检测客户端连接,当连接没有超过min\_idle\_connections预设值时,不会进行读写分离,默认最小4个(最大8个)以上的客户端连接才会实现读写分离。现改为最小1个最大2个,便于读写分离的测试,生产环境中,可以根据实际情况进行调整。

<6>启动mysql-proxy服务：此时发现服务起不来，因为权限过大。根据提示需要修改为660。

#启动mysql-proxy服务
     [root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

![在这里插入图片描述][20200310163054897.png]

<7>修改mysql-proxy.conf文件的权限为660。

[root@server3 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

<8>重新启动mysql-proxy服务，并查看端口，以确保服务已经正常启动。

[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
    [root@server3 conf]# netstat -antulpe      #查看mysql-proxy的3306端口是否存在

查看日志确定节点以加入集群：

[root@server3 mysql-proxy]# cat /usr/local/mysql-proxy/log/mysql-proxy.log

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 3]

<9>安装losf软件以提供losf命令,用来查看调度器的连接数

[root@server3 mysql-proxy]# yum install lsof-4.87-4.el7.x86_64 -y
    [root@server3 mysql-proxy]# lsof -i:3306			 #查看本机的3306端口的连接数

## 配置server1（主库）: ##

<1>登录数据库，创建数据库westos以便测试，创建用户并且给用户进行授权(读和写的权限：update,select,insert)

[root@server1 ~]# mysql -uroot -pWestos+001
    
    
    mysql> grant,update,select on *.* to dd@'%' identified by 'Westos+001' ;
    Query OK, 0 rows affected, 1 warning (0.08 sec)
    
    #建立proxy用户，并给proxy用户授权(select,insert,update权限)

## 配置物理机（客户端） ##

<1>安装mysql，以提供mysql命令

值的注意的是：这里必须用yum安装mysql，不可以跟server1,server2一样安装rpm包。（因为mysql数据库的读写分离，不适用于最新版的mysql服务）

[root@foundation63 images]# yum install mariadb -y

## 读写分离测试： ##

**第一次连接：**

<1>物理机：以proxy用户的身份登录数据库。

[root@foundation63 images]# mysql -h 172.25.63.3 -udd -pWestos+001

<2>在server3（代理服务器）上查看调度器的连接数。

[root@server3 mysql-proxy]# lsof -i:3306

![在这里插入图片描述][20200310163657235.png]  
**第二次连接：**

<3>物理机：重新开一个终端（上次的那个登陆数据库的终端，不要退出数据库），以proxy用户的身份登录数据库。

[kiosk@foundation63 images]$ mysql -h 172.25.63.3 -udd -pWestos+001

<4>在server3（代理服务器）上查看调度器的连接数。

[root@server3 mysql-proxy]# lsof -i:3306

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 4]

**第三次连接：**

<5>物理机：重新开一个终端（上两次的那个登陆数据库的终端，不要退出数据库），以proxy用户的身份登录数据库。

[kiosk@foundation63 images]$ mysql -h 172.25.63.3 -udd -pWestos+001

<6>在server3（代理服务器）上查看调度器的连接数。

[root@server3 mysql-proxy]# lsof -i:3306

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 5]  
从上面的测试过程和结果可以看出，当客户端的连接数超过2个时，客户端指向的是server2（从库：只能读的库），表示读写分离配置成功，此时就可以进行读写分离的测试：

在server1建立新表：

mysql> create table userinfo (
        -> username varchar(10) not null,
        -> phonenum varchar(10) not null);
    Query OK, 0 rows affected (0.44 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 6]

此时，在客户端的任意一个终端对数据库进行写入操作：

[kiosk@foundation63 images]$ mysql -h 172.25.63.3 -udd -pWestos+001
    
    MySQL [(none)]> use westos;
    
    MySQL [westos]> insert into userinfo values ('redhat-cc','123456');

写入后可以查看到表的内容  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 7]此时停止从库：

在server2：

mysql> stop slave;
    Query OK, 0 rows affected (0.06 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 8]

在客户端再插入一条数据，并查看表格内容：

MySQL [westos]> insert into userinfo values ('redhat-qq','666666');
    
    MySQL [westos]> select * from userinfo;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 9]此时在server1中查看可以看到新插入的内容：  
![在这里插入图片描述][20200310165458814.png]在server2中查不到新插入的内容：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 10]

**说明关闭server2（从库：只能读的库）后，客户端读的功能受到了影响**

此时打开server2，并在客户端再读就可以查看到新插入的内容：

在server2：
    
    mysql> start slave;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 11]在客户端查看：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 12]表明读写分离配置成功

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200310161318461.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200310161603856.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_35887546_article_details_104757085]: https://blog.csdn.net/qq_35887546/article/details/104757085
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200310162742404.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200310163054897.png]: https://img-blog.csdnimg.cn/20200310163054897.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200310163528817.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200310163657235.png]: https://img-blog.csdnimg.cn/20200310163657235.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200310164131821.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200310164525399.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200310164854208.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/2020031016494974.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200310165221280.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200310165351612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[20200310165458814.png]: https://img-blog.csdnimg.cn/20200310165458814.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200310165936661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200310170027456.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200310170159630.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODg3NTQ2,size_16,color_FFFFFF,t_70