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

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

Mysql-proxy简介：

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

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

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

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

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

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

实现环境：

server1	172.25.60.1	master
server2	172.25.60.2	slave
server3	172.25.60.3	mysql-proxy

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

1.server3部署mysql-proxy服务

1. tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local #解压到指定目录

2. cd /usr/local

3. mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy #重命名，方便管理

4. cd mysql-proxy/

5. mkdir conf #创建配置文件目录

6. mkdir log #创建日志文件目录

7. vim conf/mysql-proxy.conf #编写配置文件

8. [mysql-proxy]

9. proxy-address=0.0.0.0:3306 #监听本机中所有IP的端口。

10. proxy-backend-addresses=172.25.60.1:3306 #进行写的数据库server1

11. proxy-read-only-backend-addresses=172.25.60.2:3306

12. proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

13. pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid #pid文件

14. log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #生成日志文件

15. plugins=proxy #proxy插件

16. log-level=debug #日志等级为debug

17. keepalive=true #心跳检测

18. daemon=true #守护进程

19.  

mysql-proxy.conf

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,

 

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

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

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

1. mysql> grant insert,update,select on *.* to van@'%' identified by 'Van+128ff';

2. Query OK, 0 rows affected, 1 warning (0.05 sec)

3.  

4. mysql> flush privileges;

5. 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

测试 远程登录 同步成功

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

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

6.2在server3监听3306端口

server3:

yum install -y lsof
lsof -i:3306

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

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

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

 7 结果验证

server1：

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

server2:

server2没有数据。

这说明，读写成功分离