mysql-proxy实现读写分离_mysql-proxy 读写分离的目的-CSDN博客

本文链接：https://blog.csdn.net/a939029674/article/details/81740633

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
1.为什么要使用读写分离：
因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。
但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）

2、when 什么时候要读写分离？
数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是表折分，或是搜索引擎。都是解决方法。

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

环境描述:
操作系统：linux6.5
主服务器Master：172.25.24.1
从服务器Slave：172.25.24.2
调度服务器MySQL-Proxy：172.25.24.3
原理图（来源于网络）
这里写图片描述

安装mysql-proxy

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

创建存放配置文件和日志的目录

root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# mkdir conf

编写配置文件并修改启动脚本

[root@server3 logs]# vim mysql-proxy.conf   ##添加配置文件
[mysql-proxy]
user=root
proxy-address=0.0.0.0:3306      ##所有IP的3306端口
proxy-backend-addresses=172.25.24.1:3306  ##master
proxy-read-only-backend-addresses=172.25.24.2:3306  ##slave
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua   ##脚本所在位置
log-file=/usr/local/mysql-proxy/logs/msyql-proxy.log  ##日志存放位置
log-level=debug   ##日志模式
daemon=true    ##打入后台
keepalive=true

[root@server3 logs]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua      ##这是管理进程的脚本

这里写图片描述

启动mysql-proxy

[root@server3 conf]# chmod 660 mysql-proxy.conf 
[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
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1

测试（用物理机模仿客户端尝试是否链接成功）：

客户端链接proxy就是一个代理，当客户端去连接的时候，它会把这个请求给master，由于进程数设置的最大进程为两个，当连接的客户端超过2个之后，proxy会将请求给slave

[root@foundation24 mysql]# mysql -h 172.25.24.3 -u root -p
MySQL [westos]> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 12345    |
| user3    | 3333     |
| user4    | 4444     |
| user8    | 88       |
| user8    | 88       |
+----------+----------+
5 rows in set (0.00 sec)

MySQL [westos]> insert into userlist values ('user9','9999');
Query OK, 1 row affected (0.09 sec)

Server2: 查看是否同步，是同步的

mysql> use westos
Database changed
mysql> select * from  userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 12345    |
| user3    | 3333     |
| user4    | 4444     |
| uwer5    | 555      |
| uwer6    | 666      |
| user7    | 7777     |
| user8    | 88       |
| user8    | 88       |
| user9    | 9999     |
+----------+----------+
9 rows in set (0.00 sec)

Server1：

[root@server1 ~]# yum install -y lsof   

[root@server1 ~]# lsof -i :3306   查看连接情况
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1251 mysql   31u  IPv6   9203      0t0  TCP server1:mysql->server2:59113 (ESTABLISHED)
mysqld  1251 mysql   32u  IPv6   8644      0t0  TCP *:mysql (LISTEN)
mysqld  1251 mysql   50u  IPv6   9230      0t0  TCP server1:mysql->server3:58047 (ESTABLI

再开一台物理机访问

[root@foundation24 ~]# mysql -h 172.25.24.3 -u root -p
Enter password: 
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 ('user10','1010');
Query OK, 1 row affected (0.10 sec)

Server2:

mysql> select * from  userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2    | 12345    |
| user3    | 3333     |
| user4    | 4444     |
| uwer5    | 555      |
| uwer6    | 666      |
| user7    | 7777     |
| user8    | 88       |
| user8    | 88       |
| user9    | 9999     |
| user10   | 1010     |
+----------+----------+
10 rows in set (0.00 sec)

[root@server1 ~]# lsof -i :3306

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1251 mysql   31u  IPv6   9203      0t0  TCP server1:mysql->server2:59113 (ESTABLISHED)
mysqld  1251 mysql   32u  IPv6   8644      0t0  TCP *:mysql (LISTEN)
mysqld  1251 mysql   50u  IPv6   9230      0t0  TCP server1:mysql->server3:58047 (ESTABLISHED)
mysqld  1251 mysql   51u  IPv6   9835      0t0  TCP server1:mysql->server3:58048 (ESTABLISHED)

最后开启四台物理机

server3

root@server3 conf]# lsof -i :3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 1180 root   10u  IPv4   9294      0t0  TCP *:mysql (LISTEN)
mysql-pro 1180 root   11u  IPv4   9671      0t0  TCP server3:mysql->172.25.24.250:35348 (ESTABLISHED)
mysql-pro 1180 root   12u  IPv4   9672      0t0  TCP server3:58047->server1:mysql (ESTABLISHED)
mysql-pro 1180 root   13u  IPv4   9673      0t0  TCP server3:mysql->172.25.24.250:35350 (ESTABLISHED)
mysql-pro 1180 root   14u  IPv4   9674      0t0  TCP server3:58048->server1:mysql (ESTABLISHED)
mysql-pro 1180 root   15u  IPv4   9677      0t0  TCP server3:mysql->172.25.24.250:35354 (ESTABLISHED)
mysql-pro 1180 root   16u  IPv4   9678      0t0  TCP server3:36671->server2:mysql (ESTABLISHED)
mysql-pro 1180 root   17u  IPv4  10024      0t0  TCP server3:mysql->172.25.24.250:35356 (ESTABLISHED)
mysql-pro 1180 root   18u  IPv4  10025      0t0  TCP server3:36674->server2:mysql (ESTABLISHED)

Server2：

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1247 mysql   35u  IPv6   8573      0t0  TCP *:mysql (LISTEN)
mysqld  1247 mysql   49u  IPv6   9226      0t0  TCP server2:mysql->server3:36671 (ESTABLISHED)
mysqld  1247 mysql   52u  IPv6   9807      0t0  TCP server2:mysql->server3:36674 (ESTABLISHED)
mysqld  1247 mysql   55u  IPv4   9186      0t0  TCP server2:59113->server1:mysql (ESTABLISHED)

Server1：

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1251 mysql   31u  IPv6   9203      0t0  TCP server1:mysql->server2:59113 (ESTABLISHED)
mysqld  1251 mysql   32u  IPv6   8644      0t0  TCP *:mysql (LISTEN)
mysqld  1251 mysql   50u  IPv6   9230      0t0  TCP server1:mysql->server3:58047 (ESTABLISHED)
mysqld  1251 mysql   51u  IPv6   9835      0t0  TCP server1:mysql->server3:58048 (ESTABLISHED)