实现MySQL数据库的读写分离（MySQL-proxy)

最新推荐文章于 2023-07-29 19:30:37 发布

啾咪嘻啾咪

最新推荐文章于 2023-07-29 19:30:37 发布

阅读量249

点赞数

本文链接：https://blog.csdn.net/asufeiya/article/details/102864838

版权

读写分离

server1又要读又要写，读写分离，设置一个中间件，用户要写就到server1,读就到server2

主流的mysql-proxy Mycat Amoeba  

做mysql-proxy  server3

1.mysql数据库的读写分离的基础知识（为什么要进行读写分离）

对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作
（新闻网站，插入一条新闻，查询操作）
剩下的则是写（insert、update、delete，只要能对MySQL的数据造成更改的操作都叫写操作）操作
在使用负载均衡集群之后，可以很大程度的提升网站的整体性能，但是最终的数据处理的压力还是会落到MySQL数据库上
所有很有必要使用一些技术来提升MySQL的负载能力。（读写分离）写专门交给写服务器处理（一般网站来说写是比较少的读写比 4:1）
那么需要把读的任务分配多台服务器来完成的架构，就叫做读写分离

2.读写分离的实现方式

（1）php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器器）
mysql_connect(‘读写的区分’)

（2）MySQL- Proxy是实现"读写分离(Read/Write Splitting)"的一个软件（MySQL官方提供，也叫中间件），基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。

注意：MySQL-proxy 它能实现读写语句的区分主要依靠的是内部一个lua脚本（能实现读写语句的判断）

注意：如果只在主服务器（写服务器）上完成数据的写操作话；这个时候从服务器上没有执行写操作，是没有数据的
这个时候需要使用另外一个技术来实现主从服务器的数据一致性，这个技术叫做主从复制技术，所以说主从复制是读写分离的基础

读写分离(MySQL- Proxy)是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力
使用mysql-proxy实现mysql的读写分离，**mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求
对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上**
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率

续上一篇博客

应用：把写和读分开
如果业务压力不是很大的时候要做读写分离，取决于硬盘读取的性能，客户才满意
读库（配置低），写库（配置高：固态硬盘，高速存储）
读写分离->mysql集群、分布式、代理、高可用

proxy，相当于一个代理
实验一共需要三台主机
server1做读、写库
server2做读库
server3做proxy代理
并且两个库（server1和server2）要实现读写分离，但是还要实现数据同步，基于gtid的异步复制（主从复制）

主机名 	IP 	角色 	服务
server1 	172.25.63.1 	master 	mysqld
server2 	172.25.63.2 	slave 	mysqld
server3 	172.25.63.3 	代理端 	mysql-proxy

配置server3代理端（mysql-proxy）

在server3上面：搭建proxy代理服务器（实现客户端写在server1上面、客户端读server2上的数据）

在这里插入图片描述

[root@server3 ~]# ls查看传过来的压缩包
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/加压到指定目录下
[root@server3 ~]# cd /usr/local/切换到解压的目录下
[root@server3 local]# ll查看
[root@server3 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy给一个短点的名字
[root@server3 local]# ll查看

在这里插入图片描述

[root@server3 local]# cd mysql-proxy/进去proxy服务的目录
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd bin/进入脚本目录
[root@server3 bin]# ls
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy启动脚本

在这里插入图片描述
新建一个配置文件的目录，创建配置文件

[root@server3 bin]# ls
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
[root@server3 bin]# cd ..
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# ls
[root@server3 conf]# vim mysql-proxy.conf编写服务的配置文件

在这里插入图片描述

 1 [mysql-proxy]
 2 proxy-address=0.0.0.0:3306               ##mysql-proxy运行的端口
 3 proxy-read-only-backend-addresses=172.25.63.2:3306     ##slave节点：只读
 4 proxy-backend-addresses=172.25.63.1:3306      ##master节点：可读可写
 5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua     ##lua脚本的路径
 6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid       ##进程pid的位置
 7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log    ##日志位置
 8 plugins=proxy
 9 log-level=debug    ##定义日志级别
 10 keepalive=true     ##mysql-proxy崩溃时尝试重启
 11 daemon=true        ##打入后台

在这里插入图片描述

[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
[root@server3 mysql-proxy]# mkdir log建立目录，存放进程pid和日志
[root@server3 mysql-proxy]# ls

在这里插入图片描述 修改数据库发生读写分离时的最大和最小连接数

[root@server3 mysql-proxy]# cd share/doc/mysql-proxy
[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy/share/doc/mysql-proxy
[root@server3 mysql-proxy]# ls
[root@server3 mysql-proxy]# vim rw-splitting.lua     修改主机数量  
40                 min_idle_connections = 1,   最小连接数    ##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
41                 max_idle_connections = 2,   最大连接数

在这里插入图片描述
启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf      ##修改配置文件的权限   权限太大不安全
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

在这里插入图片描述

root@server3 conf]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  log  share
[root@server3 mysql-proxy]# cd log/
[root@server3 log]# ls
mysql-proxy.log  mysql-proxy.pid
[root@server3 log]# cat mysql-proxy.pid 
[root@server3 log]# cat mysql-proxy.log ##查看日志
[root@server3 log]# netstat -tnlp

在这里插入图片描述

接下来测试读写分离

（1）在server1上面:创建新的用户并且授权
[root@server1 mysql]# mysql -uroot -p
Enter password: 
mysql> grant insert,update,select on *.* to dd@'%' identified by 'Yan+123kou';新建一个用户dd并对他授权
mysql> flush privileges;刷新授权表
mysql> show databases;查看数据库信息

在这里插入图片描述
现在开始测试读写分离
server3安装lsof

在真机上第一次连接数据库代理server3

mysql -h 172.25.12.3 -udd -pWestos+001
show databases;

在这里插入图片描述在真机上第二次连接数据库代理server3

mysql -h 172.25.12.3 -uyan -pYan=123kou
show databases;
在这里插入图片描述在真机上第三次连接数据库代理server3

mysql -h 172.25.12.3 -uyan -pYan=123kou
show databases;
在这里插入图片描述在server3上面:lsof -i:3306
发现开始读写分离(用户多的时候)

server1（master）上能看到数据说明写操作是在master上
而slave可以查看到数据是因为server1（master）和server2（slave）是主从复制的关系
不能说明server2上可以进行写操作，为了更清楚的验证读写分离，我们做下面的实验
在这里插入图片描述

总结：
当访问数据库的真机（用户）数量很多的时候，数据库的代理就会把后端的数据库实现读写分离
我的server1是写的数据库
我的server2是读的数据库
当我的server1和server2满足gtid的异步复制的时候，真机往数据库写入的东西其实是写入了server1，并没有写入server2，server2上面的数据是复制过去的
因此server1、server2、真机上面都能查到刚刚写进去的数据，其实真机查的是server2（读）
当关闭server1和server2的异步复制的时候，真机往数据库写入数据的东西只写进了server1，没有写进去server2，server2也没有复制一份
因为server1可以查看到，server2和真机上面都查不到刚刚写进去的数据，此时的真机读的是server2

#############mysql-proxy实现读写分离##########
	#读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba
	#上课演示proxy，其他下去自己看
	先看官网，官网没有el7版本的mysql-proxy，所以还是用6的
	
用server3来做proxy，1和2一个读一个写
做以下实验之前，先做好server1和server2的gtid主从复制

#server3上：
#先关闭之前的mysql，因为proxy也用3306端口
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy	#软链接便于访问

cd /usr/local/mysql-proxy/bin
./mysql-proxy --help	##查看帮助
./mysql-proxy --help-proxy	##查看proxy的帮助
./mysql-proxy --help-all	##查看所有帮助

cd /usr/local/mysql-proxy
mkdir conf	##建立配置文件目录

#编辑配置文件（自己手动新建）
vim mysql-proxy.conf	

[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.136.1:3306
proxy-read-only-backend-addresses=172.25.136.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
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

#创建日志目录
mkdir /usr/local/mysql-proxy/log

#修改lua脚本
min_idle_connections = 1,	##把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为1个好测试
max_idle_connections = 2,

#启动mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

启动报错：
2019-03-15 14:15:04: (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)
2019-03-15 14:15:04: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2019-03-15 14:15:04: (message) shutting down normally, exit code is: 1

#因为配置文件权限过大
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

再启动，正常

#查看日志
cat /usr/local/mysql-proxy/log/mysql-proxy.log
看到两个节点都加进来了

2019-03-15 15:07:22: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2521 alive
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:157: waiting for 2521
2019-03-15 15:07:22: (debug) chassis-unix-daemon.c:121: we are the child: 2521
2019-03-15 15:07:22: (critical) plugin proxy 0.8.5 started
2019-03-15 15:07:22: (debug) max open file-descriptors = 1024
2019-03-15 15:07:22: (message) proxy listening on port 0.0.0.0:3306
2019-03-15 15:07:22: (message) added read/write backend: 172.25.136.1:3306
2019-03-15 15:07:22: (message) added read-only backend: 172.25.136.2:3306

#在server1上授权新用户读写权限
mysql> grant insert,update,select on *.* to wsp@'%' identified by 'Wsp+123ld';

mysql> flush privileges;

mysql> use westos;
Database changed
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(15) not null);

mysql> desc usertb;

#在物理机上打开一个shell来连接数据库
[kiosk@foundation0 ~]$ mysql -h 172.25.136.3 -uwsp -pWsp+123ld

#然后在server3上看到连接已经建立
[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root   10u  IPv4  28318      0t0  TCP *:mysql (LISTEN)
mysql-pro 2521 root   11u  IPv4  28362      0t0  TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root   12u  IPv4  28363      0t0  TCP server3:47376->server1:mysql (ESTABLISHED)

#在物理机上再打开一个shell来连接数据库
#再到server3上查看
[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root   10u  IPv4  28318      0t0  TCP *:mysql (LISTEN)
mysql-pro 2521 root   11u  IPv4  28362      0t0  TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root   12u  IPv4  28363      0t0  TCP server3:47376->server1:mysql (ESTABLISHED)
mysql-pro 2521 root   13u  IPv4  28709      0t0  TCP server3:mysql->foundation0.ilt.example.com:55722 (ESTABLISHED)
mysql-pro 2521 root   14u  IPv4  28710      0t0  TCP server3:47382->server1:mysql (ESTABLISHED)

'发现两次连接都指向server1'

#在物理机上再打开一个shell来连接数据库
#再到server3上查看
[root@server3 log]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2521 root   10u  IPv4  28318      0t0  TCP *:mysql (LISTEN)
mysql-pro 2521 root   11u  IPv4  28362      0t0  TCP server3:mysql->foundation0.ilt.example.com:55716 (ESTABLISHED)
mysql-pro 2521 root   12u  IPv4  28363      0t0  TCP server3:47376->server1:mysql (ESTABLISHED)
mysql-pro 2521 root   13u  IPv4  28709      0t0  TCP server3:mysql->foundation0.ilt.example.com:55722 (ESTABLISHED)
mysql-pro 2521 root   14u  IPv4  28710      0t0  TCP server3:47382->server1:mysql (ESTABLISHED)
mysql-pro 2521 root   15u  IPv4  28769      0t0  TCP server3:mysql->foundation0.ilt.example.com:55726 (ESTABLISHED)
mysql-pro 2521 root   16u  IPv4  28770      0t0  TCP server3:55914->server2:mysql (ESTABLISHED)

'这次连接到了server2，说明读写分离启用'



#测试读写分离
在物理机上插入数据：
MySQL [(none)]> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MySQL [westos]> insert into usertb values('user1','123');
MySQL [westos]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

发现server1和server2都能看到，看不出读写分离，因为存在主从复制

现在到server2上关闭复制
mysql> stop slave;

这时再在物理机上插入数据
MySQL [westos]> insert into usertb values ('user4','123');
Query OK, 1 row affected (0.01 sec)

MySQL [westos]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
+----------+----------+
2 rows in set (0.00 sec)

发现插入的数据看不到，但是在server1上可以看到，这就说明了它在读的时候读的是server2上的数据，而写操作却写在了server1上

啾咪嘻啾咪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实现MySQL数据库的读写分离（MySQL-proxy)

读写分离server1又要读又要写，读写分离，设置一个中间件，用户要写就到server1,读就到server2主流的mysql-proxy Mycat Amoeba 做mysql-proxy server31.mysql数据库的读写分离的基础知识（为什么要进行读写分离）对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的...
复制链接

扫一扫