数据库系列之MySQL基于MySQL-proxy实现读写分离

MySQL主从复制读写分离的实现有很多种，比如使用中间件mysql-proxy、proxysql和mycat三种中间件实现读写分离并进行测试验证。本文主要讨论基于MySQL-proxy中间件实现读写分离：

数据库系列之MySQL基于MySQL-proxy实现读写分离
数据库系列之MySQL基于proxySQL实现读写分离
数据库系列之MySQL基于Mycat实现读写分离

---

1、MySQL主从复制读写分离实现

主从复制的读写分离是将主节点作为应用Write库，从节点作为Read库，利用主从复制技术将主库的数据同步到从库中，以提升数据库的并发负载能力。常见的实现方案有以下几种：

1. 应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库。这种方式在应用程序逻辑上会比较复杂，且应用程序和数据库强耦合，数据库如果有变化会影响到主库
2. 利用中间件来做代理，负责对数据库的请求识别出读还是写，并分发到不同的数据库中。这种方式中数据库和应用程序弱耦合，但是代理存在性能瓶颈和可靠性风险增加。常见的中间件有MySQL-Proxy、proxySQL、mycat、DBProxy等
3. 利用mysql集群模式，罕见，复杂度高，稳定性差
   

基于中间件方式如上图所示，这种方式有以下特点：

1. Proxy将数据库屏蔽在后端，而proxy根据路由将语句路由到对应DB执行
2. 查询性能提升，数据有多份副本，查询性能得到保证
3. Proxy本身可能会成为性能瓶颈和单点故障，可以通过proxy负载均衡器的集群架构

本文中读写分离实现使用到的服务器环境配置如下：

Hostname	IP	系统	角色
tango-centos01	192.168.112.101	Centos7.0_x86-64	Master
tango-centos02	192.168.112.102	Centos7.0_x86-64	Slave1
tango-centos03	192.168.112.103	Centos7.0_x86-64	Slave2
tango-01	192.168.112.10	Centos7.0_x86-64	Proxy

其中MySQL主从复制环境部署请参考“MySQL主从复制集群部署”部分内容，本文中不再详述。

1.1 基于MySQL-proxy

1.1.1 MySQL-proxy介绍

MySQL-proxy是mysql官方提供的mysql中间件服务，上游可接入若干个mysql-client，后端可连接若干个mysql-server。它使用mysql协议，通过lua脚本实现sql拦截与修改、性能分析与监控、读写分离和请求路由等功能。

1.1.2 安装配置MySQL-proxy

1）下载安装包
https://downloads.mysql.com/archives/proxy/

2）在192.168.112.10安装MySQL-proxy

[root@tango-01 src]# tar -xzvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/
[root@tango-01 src]# cd /usr/local/
[root@tango-01 local]# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy
[root@tango-01 local]# cd mysql-proxy/
[root@tango-01 mysql-proxy]# mkdir conf
[root@tango-01 mysql-proxy]# mkdir logs

3）编写配置文件

[root@tango-01 mysql-proxy]# vi conf/mysql-proxy.conf    #编写配置文件
[mysql-proxy]
user=root    //运行mysql-proxy进程的用户
admin-username=admin
admin-password=admin
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy_address=0.0.0.0:4040   //监听本机所有地址的4040端口，默认为4040
proxy-backend-addresses=192.168.112.101:3306    //backend主   注意addresses
proxy-read-only-backend-addresses=192.168.112.102:3306,192.168.112.103:3306   //backend从
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  //lua脚本地址
log-file=/usr/local/mysql-proxy/logs/mysql-proxy   //proxy日志路径
plugins=proxy,admin
log-level=debug   //日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true     //打入后台
keepalive=true   //在mysql-proxy崩溃时尝试重启

4）修改rw-splitting.lua脚本配置

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，修改lua脚本，默认超过4个连接才会启用读写分离，改为超过2个连接启用读写分离

[root@tango-01 mysql-proxy]# vi /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,
                max_idle_connections = 2,

                is_debug = false
        }
end

5）启动mysql-proxy

[root@tango-01 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@tango-01 mysql-proxy]# ./bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@tango-01 mysql-proxy]# netstat -antlp|grep mysql-proxy
tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN      74015/mysql-proxy   
tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN      74015/mysql-proxy

1.1.3 读写分离效果测试

1）创建用于读写分离的数据库连接用户

登陆主数据库服务器192.168.112.101，创建读写分离用户proxyid

[mysql@tango-centos01 mysql]$ ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL ON *.* TO 'proxyid'@'192.168.112.%' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

2）连接mysql-proxy管理节点，检查主从机器状态

[root@tango-01 mysql]# ./bin/mysql -uadmin -p -h192.168.112.10 -P4041
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from backends;
+-------------+----------------------+---------+------+------+-------------------+
| backend_ndx | address              | state   | type | uuid | connected_clients |
+-------------+----------------------+---------+------+------+-------------------+
|           1 | 192.168.112.101:3306 | unknown | rw   | NULL |                 0 |
|           2 | 192.168.112.102:3306 | unknown | ro   | NULL |                 0 |
|           3 | 192.168.112.103:3306 | unknown | ro   | NULL |                 0 |
+-------------+----------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)

mysql>

3）连接到mysql-proxy

[root@tango-01 mysql]# ./bin/mysql -uroot -p -h192.168.112.10 -P4040
mysql> use test01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb01           |
+----------------+
1 row in set (0.01 sec)

mysql> select * from test01.tb01;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
+----------+----------+
3 rows in set (0.07 sec)

4）在192.168.112.101的Master端使用lsof查看连接情况

[mysql@tango-centos01 mysql]$ lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2103 mysql   23u  IPv4  33911      0t0  TCP tango-centos01:mysql (LISTEN)
mysqld  2103 mysql   49u  IPv4  34258      0t0  TCP tango-centos01:mysql->192.168.112.102:34112 (ESTABLISHED)
mysqld  2103 mysql  102u  IPv4 156035      0t0  TCP tango-centos01:mysql->192.168.112.103:58560 (ESTABLISHED)
mysqld  2103 mysql  104u  IPv4 235447      0t0  TCP tango-centos01:mysql->192.168.112.10:40154 (ESTABLISHED)

在proxy端再增加链接，后查看lsof

[mysql@tango-centos01 mysql]$ lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2103 mysql   23u  IPv4  33911      0t0  TCP tango-centos01:mysql (LISTEN)
mysqld  2103 mysql   49u  IPv4  34258      0t0  TCP tango-centos01:mysql->192.168.112.102:34112 (ESTABLISHED)
mysqld  2103 mysql   51u  IPv4 236992      0t0  TCP tango-centos01:mysql->192.168.112.10:40690 (ESTABLISHED)
mysqld  2103 mysql  102u  IPv4 156035      0t0  TCP tango-centos01:mysql->192.168.112.103:58560 (ESTABLISHED)
mysqld  2103 mysql  104u  IPv4 235447      0t0  TCP tango-centos01:mysql->192.168.112.10:40154 (ESTABLISHED)

在proxy端再增加链接，后查看lsof，master端没有增加，slave 192.168.112.102端增加了

[mysql@tango-centos02 mysql]$ lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2025 mysql   33u  IPv4  36158      0t0  TCP tango-centos02:mysql (LISTEN)
mysqld  2025 mysql   53u  IPv4  36167      0t0  TCP tango-centos02:34112->192.168.112.101:mysql (ESTABLISHED)
mysqld  2025 mysql   60u  IPv4 217689      0t0  TCP tango-centos02:mysql->192.168.112.10:55036 (ESTABLISHED)

5）分别在三台服务器上抓包测试：

在master节点192.168.112.101：

[root@tango-centos01 ~]# tcpdump -i ens33 -nn -XX ip dst 192.168.112.101 and tcp dst port 3306

在slave1节点192.168.112.102：

[root@tango-centos02 mysql]# tcpdump -i ens33 -nn -XX ip dst 192.168.112.102 and tcp dst port 3306

在slave2节点192.168.112.103：

[root@tango-centos02 mysql]# tcpdump -i ens33 -nn -XX ip dst 192.168.112.103 and tcp dst port 3306

在mysql-proxy上进行数据库操作：

mysql> insert into tb01 values('user4','123');
Query OK, 1 row affected (0.25 sec)

在master上的抓包信息：

在slave上的抓包信息：

查看状态，在proxy上操作，可以看到状态全部为up：

mysql> select * from backends;
+-------------+----------------------+---------+------+------+-------------------+
| backend_ndx | address              | state   | type | uuid | connected_clients |
+-------------+----------------------+---------+------+------+-------------------+
|           1 | 192.168.112.101:3306 | up      | rw   | NULL |                 0 |
|           2 | 192.168.112.102:3306 | up      | ro   | NULL |                 0 |
|           3 | 192.168.112.103:3306 | up      | ro   | NULL |                 0 |
+-------------+----------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)

由此验证，我们已经基于MySQL-proxy实现了MySQL读写分离

---

参考资料：

1. https://blog.csdn.net/vanvan_/article/details/97800883
2. https://www.cnblogs.com/tae44/p/4701226.html

---

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/117697556
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！