MYSQL数据库读写分离(mysql-proxy消息中间件)

最新推荐文章于 2024-08-08 10:54:52 发布

qq_44658961

最新推荐文章于 2024-08-08 10:54:52 发布

阅读量407

点赞数

本文链接：https://blog.csdn.net/qq_44658961/article/details/102673496

版权

什么是读写分离？

MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。(例如淘宝网站，大部分人进入网站后都是读）
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

为什么要读写分离？

因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。

实验开始前规定

server3(172.25.34.4)作为主库，server4(172.25.34.5)作为从库，server5(172.25.254.6)作为中间件

首先在server3（主）和server4（从）上搭建主从复制架构,参考之前的博客。

server5（中间件）:

下载安装包并解压到指定目录

[root@server5 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server5 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/

生成软连接

[root@server5 ~]# cd /usr/local/
[root@server5 local]# ls
bin  games    lib    libexec                                sbin   src
etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
[root@server5 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server5 local]# ls
bin  games    lib    libexec      mysql-proxy-0.8.5-linux-el6-x86-64bit  share
etc  include  lib64  mysql-proxy  sbin                                   src

建立目录存放配置文件和日志（安装后需要自己创建目录和文件）

[root@server5 local]# cd mysql-proxy
[root@server5 mysql-proxy]# ls
bin  conf  include  lib  libexec  licenses  share
[root@server5 bin]# mkdir log
[root@server5 mysql-proxy]# mkdir conf ##创建存放配置文件的目录
[root@server5 mysql-proxy]# cd conf/
[root@server5 conf]# vi mysql-proxy.conf  #创建配置文件
[mysql-proxy]
proxy-address=0.0.0.0:3306    ##mysql-proxy运行ip和端口
proxy-backend-addresses=172.25.34.4:3306   ##master：可读写 
proxy-read-only-backend-addresses=172.25.34.5:3306   ##slave：只读
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua   ##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  #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
keepalive=true   ##mysql-proxy崩溃时，尝试重启（持续连接）
daemon=true

修改数据库发生读写分离时的最大最小值

[root@server5 conf]# vi /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 
 40                 min_idle_connections = 1, ##最小连接数
     41                 max_idle_connections = 2,  ##最大连接数，最大连接数大于2时发生读写分离

给文件设置权限，再启动mysql-proxy（否则会启动失败）

[root@server5 conf]# cd /usr/local/mysql-proxy/bin/
[root@server5 bin]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@server5 bin]#/usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

查看日志是否配置成功

[root@server5 bin]#  cat /usr/local/mysql-proxy/log/mysql-proxy.log 
2019-10-20 14:36:05: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=2101 alive
2019-10-20 14:36:05: (debug) chassis-unix-daemon.c:157: waiting for 2101
2019-10-20 14:36:05: (debug) chassis-unix-daemon.c:121: we are the child: 2101
2019-10-20 14:36:05: (critical) plugin proxy 0.8.5 started
2019-10-20 14:36:05: (debug) max open file-descriptors = 1024
2019-10-20 14:36:05: (message) proxy listening on port 0.0.0.0:3306
2019-10-20 14:36:05: (message) added read/write backend: 172.25.34.4:3306
2019-10-20 14:36:05: (message) added read-only backend: 172.25.34.5:3306

server3(主库）:

授权用户，允许登陆的用户有对数据库更改权限：

mysql> grant insert,update,select on *.* to dd@'%' identified by'Westos+001';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

客户机：

安装mysql客户端

[kiosk@foundation34 Desktop]$ yum whatprovides */mysql
[kiosk@foundation34 Desktop]$ yum install mariadb-5.5.52-1.el7.x86_64  -y

客户端登陆写入数据

注意：由于连接数限制，我们可以在客户机上打开多个shell进行连接测试，激活读写分离功能。

[root@foundation34 Desktop]# mysql -h 172.25.34.6 -udd -pWestos+001
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

Database changed
MySQL [westos]> insert into userlist values ('usr3','789');
Query OK, 1 row affected (0.01 sec)

MySQL [westos]> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
+----------+----------+
3 rows in set (0.01 sec)

测试：

server5检测：

lsof
[root@server5 ~]# yum search lsof
Loaded plugins: product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
=============================== N/S matched: lsof ===============================
lsof.x86_64 : A utility which lists open files on a Linux/UNIX system

Name and summary matches only, use “search all” for everything.
[root@server3 ~]# yum install lsof.x86_64 -y

server3

查看数据库是否插入

mysql> 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

Database changed
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
+----------+----------+
3 rows in set (0.00 sec)