Mysql进阶之Mysql-proxy的读写分离

迷南。 2021-11-17 11:18 245阅读 0赞

Mysql-proxy简介：

mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等

MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，  
    从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。

当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70][]  
从图中可以看到，SQL语句并不直接进入到master数据库或者slave数据库，而是进入到 proxy，然后proxy判断这条语句是有关写的语句（包括insert、update、delete）还 是读语句（select），当是写语句的时候，那么proxy将向master所在的服务器发出请 求，同理，如果是读语句的时候，proxy将向slave所在的服务器发出请求。

**应用背景：**在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

背景环境：server1，2 基于gtid的主从复制，详细配置见：

[Mysql进阶之主从复制、基于gtid的主从复制、半同步复制、组复制][Mysql_gtid] 3.2 小节

## 实现环境： ##

<table> 
 <thead> 
  <tr> 
   <th>server1</th> 
   <th>172.25.60.1</th> 
   <th>master</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server2</td> 
   <td>172.25.60.2</td> 
   <td>slave</td> 
  </tr> 
  <tr> 
   <td>server3</td> 
   <td>172.25.60.3</td> 
   <td>mysql-proxy</td> 
  </tr> 
 </tbody> 
</table>

前提：必须实现主从复制。

**1.server3部署mysql-proxy服务**

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local    #解压到指定目录
    cd /usr/local
    mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy    #重命名，方便管理
    cd mysql-proxy/
    mkdir conf    #创建配置文件目录
    mkdir log     #创建日志文件目录
    vim conf/mysql-proxy.conf    #编写配置文件
    [mysql-proxy]
    proxy-address=0.0.0.0:3306	#监听本机中所有IP的端口。
    proxy-backend-addresses=172.25.60.1:3306	#进行写的数据库server1
    proxy-read-only-backend-addresses=172.25.60.2:3306	
    proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
    pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid    #pid文件
    log-file=/usr/local/mysql-proxy/log/mysql-proxy.log    #生成日志文件
    plugins=proxy    #proxy插件
    log-level=debug    #日志等级为debug
    keepalive=true     #心跳检测
    daemon=true        #守护进程

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 1][]

mysql-proxy.conf

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 2][]

**2.通过编写lua脚本实现读写分离**

修改lua脚本，默认超过4个连接才会启用读写分离，改为超过2个连接启用读写分离  
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  
min\_idle\_connections = 1,  
max\_idle\_connections = 2,

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 3][]

**3.启动mysql-proxy**

在启动前我们需要为配置文件加上660权限，没有修改权限，则会报错

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf  
 cd bin/  
./mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 5][]

**4.server1配置用户权限，测试**

测试之前，我们需要在server1中创建一个数据库和表，插入数据，用来实验。同时也要创建一个对数据库可以查询、插入和更新的用户

mysql> grant insert,update,select on *.* to van@'%' identified by 'Van+128ff';
    Query OK, 0 rows affected, 1 warning (0.05 sec)
    
    mysql>  flush privileges;
    Query OK, 0 rows affected (0.03 sec)

**5.测试远程登录mysql-proxy服务端能否同步**

测试主机安装mysql：yum install -y mysql

mysql -h 172.25.60.3 -uvan -pVan+128ff

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 6][]

测试 远程登录 同步成功

**6.测试是否读写成功分离**

6.1 在客户端多开几个终端远程连接mysql-proxy

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 7][]

6.2在server3监听3306端口

server3:

yum install -y lsof  
lsof -i:3306

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 8][]

6.3 server2关闭异步复制，远程添加数据（可以看到实验效果）

![2019073018042599.png][]

在客户端远程登录mysql-proxy，添加数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 9][]

可以发现数据写入成功，但是查看不到。因为我们把主从复制关掉了，写在了server1里面，但是读的内容却是server2，server2没有同步server1的数据，故查看不到。

** 7 结果验证**

**server1：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 10][]

可以看到数据确实添加到server1上面了

**server2:**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 11][]**

server2没有数据。

**这说明，读写成功分离**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70]: /images/20211116/349a4bb4f8404b1aa1307be3b7bbbc6b.png
[Mysql_gtid]: https://blog.csdn.net/vanvan_/article/details/97610972
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 1]: /images/20211116/3433f1d8ec1847c3a50a1f21c010eed7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 2]: /images/20211116/e929ec8fc5964f18aedf070109188bc7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 3]: /images/20211116/f9991f444a534447ad65a63f12e07ebe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 4]: /images/20211116/d42e6cede3194131be718023de3e265d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 5]: /images/20211116/a9eafd6f1e914b3eafe16fd629c15a3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 6]: /images/20211116/51898fcc59554fa0ac0ceac10c5d4880.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 7]: /images/20211116/e5af1c82ab7940db95ed9096c6312b77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 8]: /images/20211116/329785ac415f4c71b3e57d8dabc82438.png
[2019073018042599.png]: /images/20211116/8d88f28a53744c8684bae7b8bcf0b28b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 9]: /images/20211116/eabed15604b74a7fb9a6dc286619e7ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 10]: /images/20211116/44db0a9fe05448fdabf0ac0357e09794.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZhbnZhbl8_size_16_color_FFFFFF_t_70 11]: /images/20211116/2cdb0858924f41f3b9ce8bf4b5c852de.png