使用mysql-proxy实现mysql的读写分离

概述

试验目的

验证、测试Linux系统下使用MySQL-Proxy实现MySQL的读写分离

试验环境

准备两台服务器，分别安装好操作系统和数据库，数据库的版本要一样。

环境项目	环境配置	备注
硬件环境	VMware虚拟机
server1	Ubuntu 20.04.2 LTS 桌面版	ip: 192.168.229.128
server2	Ubuntu 20.04.2 LTS 服务器版	ip: 192.168.229.129
MySQL	8.0.25-0-ubuntu0.20.04.1	server1 和s erver2 必须先配置完成主从或者主主复制(此实验环境配置的是主主复制，方便后期继续配置高可用性)。 配置方法请参考《Linux系统下使用MySql双机热备功能》
MySQL-proxy	0.8.5 alpha	MySQL-Proxy 是官方提供的 MySQL 中间件产品可以实现负载平衡， 读写分离

试验拓扑图

注意

MySql官方已不建议将mysql-proxy用于生产系统！！！

试验背景

在项目中曾经使用nginx配置过 server1和server2中MySQL的负载均衡(server1和server2中配置了MySQL的主主复制)，但在使用中如果项目应用程序是多线程的，会存在同一个应用程序同时写server1和server2的现象（虽然nginx开启了iphash）。这种情况就会存在两个server同时使用同一个表中的同一个id序号的概率，导致数据因id重复而丢失。因此启用读写分离很有必要。

准备环境

安装操作系统

分别在两台服务器上安装操作系统，本文略。请参考《华为服务器安装Ubuntu 20.04系统并实现向日葵远程桌面》

安装MySQL

分别在两台服务器上安装MySQL，本文略。

配置MySQL的主从复制

如果单纯完成本文的试验，MySQL数据库配置主从复制即可。为了实现后续的高可用性，本文实际配置的是主主复制，配置详情请参考《Linux系统下使用MySql双机热备功能》

下载安装MySQL-Proxy

下载MySQL-Proxy

可以从MySQL官网下载MySQL-Proxy,如下图所示，根据操作系统的实际情况选择下载。

解压指定目录

# 解压到指定目录
sudo tar zxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
# 导航到解压目录
cd /usr/local
# 为了方便，重命名文件夹
sudo mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

配置MySQL-Proxy

创建必要的文件夹

# 导航的mysql-proxy安装目录
cd /usr/local/mysql-proxy
# 创建配置文件夹
sudo mkdir conf
# 创建日志文件夹
sudo mkdir log

复制rw-splitting.lua文件到conf文件夹,并编辑

在../mysql-proxy/share/coc/mysql-proxy文件夹下有很多已经编辑好的lua脚本文件，基本都可以直接使用。rw-splitting.lua是实现读写分离的脚本文件

1. 复制文件

sudo cp /usr/local/mysql-proxy/share/coc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/conf

2. 编辑文件
   修改lua脚本，默认超过4个连接才会启用读写分离，改为超过2个连接启用读写分离。

sudo vim /usr/local/mysql-proxy/conf/rw-splitting.lua

min_idle_connections默认4，修改为1
max_idle_connections默认8，修改为2

新建并编辑配置文件

1. 新建或者编辑配置文件：

sudo vim /usr/local/mysql-proxy/conf/mysql-proxy.conf

配置文件内容为：

文件中的参数请根据实际情况填写，不要完全照抄

[mysql-proxy]
# 监听所有IP的3308端口,如果不加端口，默认4040
proxy-address = 0.0.0.0:3308
# 进行写的数据库server
proxy-backend-addresses = 127.0.0.1:3306
# 进行读的数据库server
proxy-read-only-backend-addresses = 192.168.229.129:3306
# 配置读写分离脚本
proxy-lua-script = /usr/local/mysql-proxy/conf/rw-splitting.lua
# pid文件存放位置
pid-file = /usr/local/mysql-proxy/log/mysql-proxy.pid
# 日志文件
log-file = /usr/local/mysql-proxy/log/mysql-proxy.log
# proxy插件
plugins = proxy
# 日志文件等级，由高到低分别为(error|warning|info|message|debug)
log-level = debug
# 心跳检测
keepalive = true
# 守护进程
daemon = true

为配置文件添加权限

sudo chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

添加程序启动项到自启动文件

编辑/etc/rc.local文件

sudo vim /etc/rc.local

在文件中添加下面内容:

#!/bin/sh -e
# 可能存在的其他项...
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf &
exit 0

测试

启动

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

查看运行的进程

ps aux | grep mysql-proxy

关闭进程

sudo killall -9 mysql-proxy

功能测试

1. 在客户端连接数据库
   此处使用windows客户端Navcat连接测试，连接3308端口成功
   

2. 在客户端新建数据库mytest，检查server1和server2是否都有新的数据库
   

3. 在客户端新建表test_tb,检查server1和server2是否都有新的表
   

4. 在表test_tb中插入一行数据,检查server1和server2对应表中是否都有数据
   

5. 关闭server2的slave功能，在表test_tb中再插入一条数据，检查两个服务器和客户端的数据

1. 在客户端,写入正常，但刷新时新写入的数据又可能没有了(有一定的概率能刷新出来，此时读数据路径被分配到了主库)；
   2）在server1端，表中有新写入的数据；
2. 在server2端，表中没有新写入的数据(开启slave功能后，数据自动恢复)；
   

6. 停止server2的mysql服务，在表test_tb中再插入一条数据，检查两个服务器和客户端的数据
   1）客户端和server1读写正常;

2. server2的mysql服务重启后,数据自动恢复。

7. 停止server1的mysql服务，检查能否写入和读取数据

1. 客户端能读取到数据,但不能写入，会报错。要解决此问题，需要引入高可用性。
   

结论

mysql-proxy可以实现负载均衡和读写分离，但从机离线会造成数据更新不了，主机离线会造成服务不可用。
另外，由于mysql官方已经不在提供mysql-proxy的更新和不推荐其应用到生产系统中，因此最好不在生产系统中使用。

其他

还可以添加为环境变量(不是必须的)

1. 修改/etc/profile:

sudo vim /etc/profile

在PATH中添加上:/usr/local/mysql-proxy/bin,如果没有PATH，就添加上PATH=$PATH:/usr/local/mysql-proxy/bin

2. 重载文件

# 切换到root用户
sudo su
# 注意:'.'与'/'之间有空格
. /etc/profile
# 检查$PATH
echo $PATH
# 检查mysql-proxy的配置
mysql-proxy -V

这种方法操作后新加的PATH只对root用户有效，最好重启系统

参考文献

Mysql进阶之Mysql-proxy的读写分离
mysql-proxy简介