企业级mysql数据库集群（四）------mysql-proxy实现读写分离

mac2026-01-01 0

一、基础知识

读写分离可以用很多软件实现：mysql-proxy 、MyCat 、Amoeba 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服务器上因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率

二、实验环境

server1 :172.25.68.1 mysqld server2: 172.25.68.2 mysqld server3:172.25.68.3 mysqld-proxy 应用：把写和读分开如果业务压力不是很大的时候要做读写分离，取决于硬盘读取的性能，客户才满意读库（配置低），写库（配置高：固态硬盘，高速存储）读写分离->mysql集群、分布式、代理、高可用数据不一定来源于同一个地方还会有测试库，模拟上线，测试服务器 proxy，相当于一个代理实验一共需要量三台主机 server1做读、写库 server2做读库 server3做proxy代理并且两个库（server1和server2）要实现读写分离，但是还要实现数据同步，基于gtid的异步复制（主从复制）

三、配置过程

在server3 1.将真机的安装包传给server3,并解压

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 ##建立配置文件目录

2.编辑配置文件（自己手动新建）

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

3.创建日志目录

mkdir /usr/local/mysql-proxy/log

4.修改lua脚本

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

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

6.因为配置文件权限过大

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

7.再次启动，正常

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: 8.server1重启服务： 2.在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来连接数据库

mysql -h 172.25.68.3 -uwsp -pWestos+68

注意！：真机安装mysql过程：

在server3 1.安装软件

#然后在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'

四、测试读写分离

在物理机上插入数据：

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;

这时再在物理机上插入数据

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

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

最新回复(0)