mysql-proxy实现读写分离

最新推荐文章于 2021-07-14 19:40:42 发布

weixin_44822212

最新推荐文章于 2021-07-14 19:40:42 发布

阅读量237

点赞数

本文链接：https://blog.csdn.net/weixin_44822212/article/details/94631801

版权

一.mysql-proxy实现读写分离的原理
MYSQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。　
mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等　MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。
对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。　　
当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。
工作原理如图：
在这里插入图片描述
二.实验环境的搭建
三台主机
172.25.4.111 server1 ##主库实现写
172.25.4.112 server2 ##从库实现读
172.25.4.113 server3 实现proxy
实验开始前将server1和server2的数据库设置为GTID主从复制
1.mysql-proxy的配置–server3
（1）关闭数据库
因为数据库的端口和mysql-proxy端口均为3306，会产生冲突
（2）mysql-proxy的配置

[root@server3 ~]# cd mysql/
[root@server3 mysql]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local  ##解压mysql-proxy软件包到指定目录
[root@server3 mysql]# cd /usr/local/
[root@server3 local]# ls
bin  games    lib    libexec                                sbin   src
etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy  ##设置软链接便于访问
[root@server3 local]# cd mysql-proxy
[root@server3 mysql-proxy]# mkdir conf  ##创建配置文件的文件夹，因为本身配置文件不存在，需要自己进行编辑
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf  ##编辑主配置文件
[root@server3 conf]# cat mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.4.111:3306
proxy-read-only-backend-addresses=172.25.4.112:3306  ##只读数据信息主机的ip
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true
[root@server3 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  ##更改lua脚本
min_idle_connections = 1,	##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
max_idle_connections = 2,
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  ##开启服务
2019-07-05 03:44:32: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)  ##开启服务失败，原因是配置文件的权限过大
[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf  ##设置主配置文件的权限为660
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  ##再次开启服务失败
2019-07-05 03:45:02: (critical) can't open log-file '/usr/local/mysql-proxy/log/mysql-proxy.log': No such file or directory  ##原因是查找不到日志
[root@server3 conf]# mkdir /usr/local/mysql-proxy/log  ##创建日志存放目录
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  ##开启服务成功
[root@server3 conf]# netstat -antlp  ##查看端口为3306
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2243/mysql-proxy    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      672/sshd            
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      847/master          
tcp        0      0 172.25.4.113:22         172.25.4.250:45212      ESTABLISHED 2179/sshd: root@pts 
tcp6       0      0 :::22                   :::*                    LISTEN      672/sshd            
tcp6       0      0 ::1:25                  :::*                    LISTEN      847/master          
[root@server3 conf]# cat /usr/local/mysql-proxy/log/mysql-proxy.log  ##查看日志
2019-07-05 03:45:29: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2243 alive
2019-07-05 03:45:29: (debug) chassis-unix-daemon.c:157: waiting for 2243
2019-07-05 03:45:29: (debug) chassis-unix-daemon.c:121: we are the child: 2243
2019-07-05 03:45:29: (critical) plugin proxy 0.8.5 started
2019-07-05 03:45:29: (debug) max open file-descriptors = 1024
2019-07-05 03:45:29: (message) proxy listening on port 0.0.0.0:3306
2019-07-05 03:45:29: (message) added read/write backend: 172.25.4.111:3306
2019-07-05 03:45:29: (message) added read-only backend: 172.25.4.112:3306  ##读写分离设置成功
[root@server3 conf]# ps -aux | grep proxy  ##查看进程
root      2242  0.0  0.0  37856   704 ?        S    03:45   0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
root      2243  0.0  0.1  37856  1144 ?        S    03:45   0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
root      2247  0.0  0.0 112648   956 pts/0    R+   03:47   0:00 grep --color=auto proxy
[root@server3 conf]# kill -9 2243
[root@server3 conf]# kill -9 2242  ##关闭服务

2.主库的设置

mysql> grant insert,update,select on *.* to luck@'%' identified by 'Westos+001';  ##创建新用户luck给于其读写权限
ysql> create database westos;  ##创建数据库
Query OK, 1 row affected (0.11 sec)

mysql> use westos;
Database changed
mysql> create table usertb(  ##创建表格
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.52 sec)

mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.物理机连接数据库
第一次连接

[kiosk@foundation4 ~]$ mysql -h 172.25.4.113 -uluck -pWestos+001

[root@server3 conf]# lsof -i:3306  ##查看连接，此时连接建立并在server1上
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2276 root   10u  IPv4  36339      0t0  TCP *:mysql (LISTEN)
mysql-pro 2276 root   11u  IPv4  36362      0t0  TCP server3:mysql->172.25.4.250:55414 (ESTABLISHED)
mysql-pro 2276 root   12u  IPv4  36363      0t0  TCP server3:43354->server1:mysql (ESTABLISHED)

第二次连接

[root@server3 conf]# lsof -i:3306  ##此时连接仍指向server1
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2276 root   10u  IPv4  36339      0t0  TCP *:mysql (LISTEN)
mysql-pro 2276 root   11u  IPv4  36362      0t0  TCP server3:mysql->172.25.4.250:55414 (ESTABLISHED)
mysql-pro 2276 root   12u  IPv4  36363      0t0  TCP server3:43354->server1:mysql (ESTABLISHED)
mysql-pro 2276 root   13u  IPv4  36627      0t0  TCP server3:mysql->172.25.4.250:55420 (ESTABLISHED)
mysql-pro 2276 root   14u  IPv4  36628      0t0  TCP server3:43358->server1:mysql (ESTABLISHED)

第三次连接

[root@server3 conf]# lsof -i:3306  ##第三次连接指向server2，说明已经实现了读写分离
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2276 root   10u  IPv4  36339      0t0  TCP *:mysql (LISTEN)
mysql-pro 2276 root   11u  IPv4  36362      0t0  TCP server3:mysql->172.25.4.250:55414 (ESTABLISHED)
mysql-pro 2276 root   12u  IPv4  36363      0t0  TCP server3:43354->server1:mysql (ESTABLISHED)
mysql-pro 2276 root   13u  IPv4  36627      0t0  TCP server3:mysql->172.25.4.250:55420 (ESTABLISHED)
mysql-pro 2276 root   14u  IPv4  36628      0t0  TCP server3:43358->server1:mysql (ESTABLISHED)
mysql-pro 2276 root   15u  IPv4  36658      0t0  TCP server3:mysql->172.25.4.250:55422 (ESTABLISHED)
mysql-pro 2276 root   16u  IPv4  36659      0t0  TCP server3:35640->server2:mysql (ESTABLISHED)

4.物理机对数据库进行更改

MySQL [(none)]> use westos
MySQL [westos]> insert into usertb values('user1','11111');  ##插入数据
MySQL [westos]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
+----------+----------+

在主库进行查看

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
+----------+----------+

在从库进行查看

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
+----------+----------+
1 row in set (0.00 sec)

此时主从库均可以看到插入的信息，原因是主从库存在GTID复制
关闭GTID复制再次进行测试

mysql> stop slave;

物理机测试

MySQL [westos]> insert into usertb values('user2','22222');  ##插入新的数据
MySQL [westos]> select * from usertb;  ##读取不到新数据
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
+----------+----------+

主库查看

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
| user2    | 22222    |
+----------+----------+

从库查看

mysql> melect * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 11111    |
+----------+----------+

由此可知，插入的数据在主库可以看到，从库与物理机均看不到。说明它在读的时候读的是server2从库上的数据，而写操作却写在了server1主库上

weixin_44822212

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql-proxy实现读写分离

一.mysql-proxy实现读写分离的原理MYSQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器...
复制链接

扫一扫