16.MySQL读写分离1

一、读写分离的含义

简单来说,数据库的读写分离就是有多个数据库,这些数据库划分为读写库和只读库,读写库可以提供数据的读服务和写服务,只读库只能提供读服务。这种通过将数据库按照职责划分的架构,称为数据库的读写分离架构。

二、读写分离作用

1、安全

读写分离通常是给数据库连接方提供一个代理服务器的IP地址和端口,可以有效隐藏数据库的真实IP和端口

2、提升性能

将读写请求分担到不同的服务器上执行,让多个数据库各司其职,共同分担压力,能够有效提升数据库的读写性能

3、易扩展

当数据库读压力过大时,可以迅速扩展从库,提高数据库的读性能

三、读写分离架构示意图

读写分离结构图

四、常见的实现方式

1、开发代理中间件

2、用开源中间件

常见的有:MyCat,MySQL-Proxy

3.Spring支持

使用提供的AbstractRoutingDataSource结合AOP实现读写分离

五、MySQL-Proxy实现

1、环境准备

主库:192.168.0.4:3306(主机名:mysql-server01)
从库:192.168.0.5:3306(主机名:mysql-server02)
代理服务器:192.168.0.6:3306
mysql版本:mysql-5.6.39
mysql-proxy下载地址:https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

2、搭建MySQL主从

可参考:

https://www.jinnianshizhunian.vip/2019/05/18/10-mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/

3、上传代理软件

[root@mysql-proxy ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
[root@mysql-proxy ~]# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy-0.8.5

4、配置mysql-proxy的环境变量

#编辑/etc/profile文件
[root@mysql-proxy ~]# vim /etc/profile

 

#添加如下内容
MYSQL_PROXY_HOME=/usr/local/mysql-proxy-0.8.5
PATH=$PATH:$MYSQL_PROXY_HOME/bin

 

#使配置文件生效
[root@mysql-proxy ~]# source /etc/profile

5、创建配置文件目录和日志目录

[root@mysql-proxy ~]# mkdir -pv /usr/local/mysql-proxy-0.8.5/{logs,conf}

6、编辑mysql-proxy配置文件

 

[root@mysql-proxy ~]# vim /usr/local/mysql-proxy-0.8.5/conf/mysql-proxy.conf

#写入如下内容
[mysql-proxy]
 plugins=admin,proxy
 admin-username=admin
 admin-password=admin
 admin-lua-script=/usr/local/mysql-proxy-0.8.5/lib/mysql-proxy/lua/admin.lua
 proxy-backend-addresses=192.168.0.4:3306
 proxy-read-only-backend-addresses=192.168.0.5:3306
 proxy-lua-script=/usr/local/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua
 log-file=/usr/local/mysql-proxy-0.8.5/logs/mysql-proxy.log
 log-level=debug
 daemon=true
 keepalive=true

参数解释:

plugins:要加载的插件,admin表示管理插件,可以查看所代理服务器的状态信息,proxy表示代理插件
admin-username:管理用户的用户名
admin-password:管理用户的密码
admin-lua-script:表示要加载的lua脚本,admin.lua是用来查看被代理服务器状态的脚本
proxy-backend-addresses:表示所代理读写库的ip地址和端口,用来配置主从中的主库,可读可写
proxy-read-only-backend-addresses:表示所代理读库的ip地址和端口,用来配置主从中的从库
rw-splitting.lua:该脚本是用来实现读写分离的脚本,mysql-proxy中自带,可以直接使用
log-file:表示mysql-proxy服务的日志文件路径
log-level:表示日志级别
daemon:表示是否以后台进程启动
keepalive:当mysql-proxy崩溃时,是否尝试重启
注意:admin插件功能启动之后默认监听在TCP的4041端口,proxy插件默认监听在TCP的4040端口

7、查看防火墙

#确定防火墙对外开启了TCP的3306端口

[root@mysql-server01 ~]# service iptables status

8、启动mysql-proxy

[root@mysql-proxy ~]# mysql-proxy –defaults-file=/usr/local/mysql-proxy-0.8.5/conf/mysql-proxy.conf

 

#查看日志是否正常,如下日志输出表示启动正常
[root@mysql-proxy ~]# tail -f /usr/local/mysql-proxy-0.8.5/logs/mysql-proxy.log
2018-05-07 07:28:09: (critical) plugin proxy 0.8.5 started
2018-05-07 07:28:09: (debug) max open file-descriptors = 1024
2018-05-07 07:28:09: (message) admin-server listening on port :4041
2018-05-07 07:28:09: (message) proxy listening on port :4040
2018-05-07 07:28:09: (message) added read/write backend: 192.168.0.4:3306
2018-05-07 07:28:09: (message) added read-only backend: 192.168.0.5:3306

 

#查看端口是否启动,4040端口和4041端口
[root@mysql-proxy ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 10095/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 10095/mysql-proxy

9、查看主从库状态

#使用管理用户连接代理,代理上的admin管理服务默认监听在tcp的4041端口
[root@mysql-proxy ~]# mysql -uadmin -padmin -P4041

 

#使用查看被代理服务器的状态
mysql> SELECT * FROM backends \G
*************************** 1. row ***************************
backend_ndx: 1
address: 192.168.0.4:3306
state: up #表示被代理服务器的状态,可能得值有:up,unkonwn,down
type: rw
uuid: NULL
connected_clients: 0
*************************** 2. row ***************************
backend_ndx: 2
address: 192.168.0.5:3306
state: unknown #此时没有查询操作,所以状态可能为unkown
type: ro
uuid: NULL
connected_clients: 0
2 rows in set (0.00 sec)

10、在主库上创建读写分离用户

mysql> GRANT ALL ON *.* TO ‘proxy’@’192.168.0.%’ IDENTIFIED BY ‘proxy’;
mysql> FLUSH PRIVILEGES;

11、建库,查看是否可以同步

[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040
mysql> CREATE DATABASE proxy_db;
Query OK, 1 row affected (0.13 sec)

 

#查看是否创建成功
mysql> SHOW DATABASES;

 

#在主库和从库中分别查看proxy_db库是否正常创建和同步,此处不演示,可直接登陆主从库查看

12、验证读写分离

可以通过在主库和从库上执行建库和查询操作,然后在主从库上分别通过tcpdump抓包操作完成验证,tcpdump的抓包操作此处不做详解,可查阅资料
(1)修改MySQL读写分离lua脚本中的如下内容,使其连接数很少时依然使用读写分离

[root@mysql-proxy ~]# vim /usr/local/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua

 

if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
#以下两处将默认的值改为1,表示连接数为多少时使用读写分离
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end

(2)连接主库192.168.0.4,然后启动抓包操作,并连接代理服务器,然后执行create database操作

#在主库上执行抓包操作
[root@mysql-server01 ~]# tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.0.4

 

#连接代理服务器
[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040

 

#执行数据库创建操作
mysql> CREATE DATABASE test1;
#执行完成create操作之后,可以看到主服务器上打印出的类似如下数据包信息
10:46:06.909236 IP 192.168.0.6.47684 > 192.168.0.4.3306: Flags [P.], seq 1552605453:1552605479,…

10:46:06.911517 IP 192.168.0.5.48496 > 192.168.0.4.3306: Flags [.], ack 2392886444…

10:46:06.911535 IP 192.168.0.6.47684 > 192.168.0.4.3306: Flags [.], …
结果:从上述的输出结果可以看出,执行DDL写操作时,操作的数据库为主库。

——————————————————————————————————————–

#在从库上执行抓包操作
[root@mysql-server02 ~]# tcpdump -i eth0 -s0 -nn -XX tcp dst port 3306 and dst host 192.168.0.5

 

#连接代理服务器
[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040

 

#执行查询操作
mysql> SELECT user FROM mysql.user;
#执行完成select操作之后,可以看到从服务器上打印出类似如下的数据包信息
04:13:31.854981 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags

04:13:31.866803 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags [.], ack 3371…

04:13:31.877291 IP 192.168.0.6.56506 > 192.168.0.5.3306: Flags [.], a…

结果:从上述的输出结果可以看出,执行DDL写操作时,操作的数据库为从库。

(3)使用管理用户连接带来服务器,然后查看被代理主从服务器的当前状态

[root@mysql-proxy ~]# mysql -uproxy -pproxy -P4040

 

#查询被代理服务器的当前状态
mysql> SELECT * FROM backends;

此时可以看到两个被代理服务器的状态都为up,表示读写分离配置成功。

注:文章属原创,如果转发,请标注出处。

发表评论

电子邮件地址不会被公开。