企业级数据库集群[mysql]——————读写分离服务

最新推荐文章于 2024-10-17 00:25:30 发布

mango_kid

最新推荐文章于 2024-10-17 00:25:30 发布

阅读量210

点赞数

分类专栏：数据库文章标签： mysql linux 运维读写分离

本文链接：https://blog.csdn.net/mango_kid/article/details/108418952

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

1.了解读写分离服务
2.实现读写分离设置
- 2.1 配置proxy代理端
- 2.2 测试读写分离

1.了解读写分离服务

1）我们为什么要实现读写分离？

对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作
（新闻网站，插入一条新闻，查询操作）。
剩下的则是写（insert、update、delete，只要能对MySQL的数据造成更改的操作都叫写操作）操作。

在使用负载均衡集群之后，可以很大程度的提升网站的整体性能，但是最终的数据处理的压力还是会落到MySQL数据库上。所以很有必要使用一些技术来提升MySQL的负载能力。（读写分离）

写专门交给写服务器处理（一般网站来说写是比较少的 读写比 4:1） 那么需要把读的任务分配多台服务器来完成的架构，就叫做读写分离

2）读写分离的实现方式：

（1）php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器）。
（2）MySQL- Proxy是实现"读写分离(Read/Write Splitting)"的一个软件（MySQL官方提供，也叫中间件），基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。

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

2.实现读写分离设置

实验准备：

基于gtid主从复制的主和备

2.1 配置proxy代理端

1）安装mysql-proxy安装包：到/usr/local/下

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

2）给安装目录重命名：

[root@proxy ~]# cd /usr/local/
[root@proxy local]# ll
total 0
drwxr-xr-x. 2 root root   6 Mar 10  2016 bin
drwxr-xr-x. 2 root root   6 Mar 10  2016 etc
drwxr-xr-x. 2 root root   6 Mar 10  2016 games
drwxr-xr-x. 2 root root   6 Mar 10  2016 include
drwxr-xr-x. 2 root root   6 Mar 10  2016 lib
drwxr-xr-x. 2 root root   6 Mar 10  2016 lib64
drwxr-xr-x. 2 root root   6 Mar 10  2016 libexec
drwxr-xr-x  8 7161 wheel 87 Aug 19  2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x. 2 root root   6 Mar 10  2016 sbin
drwxr-xr-x. 5 root root  49 Sep  3 12:26 share
drwxr-xr-x. 2 root root   6 Mar 10  2016 src
[root@proxy local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

3）新建一个配置文件的目录，编写配置文件：

[root@proxy local]# cd mysql-proxy/
[root@proxy mysql-proxy]# mkdir conf
[root@proxy mysql-proxy]# vim mysql-proxy.conf

[mysql-proxy] # 容器名称
proxy-address=0.0.0.0:3306 ##mysql-proxy运行的端口
proxy-read-only-backend-addresses=172.25.5.12:3306 ##slave节点：只读
proxy-backend-addresses=172.25.5.11:3306 ##master节点：可读可写
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 # 进程pid的位置
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log # 日志位置
plugins=proxy
log-level=debug # 定义日志级别
keepalive=true # mysql-proxy崩溃时尝试重启
daemon=true # 打入后台

4 ）修改数据库发生读写分离时的最大和最小连接数：

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1, # 修改主机数量
                max_idle_connections = 2,

                is_debug = false
        }
end

5）创建日志文件和进程pid文件所在的目录：

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

6）修改配置文件权限，启动mysql-proxy：

[root@proxy mysql-proxy]# cd conf/
[root@proxy conf]# chmod 660 mysql-proxy.conf 
[root@proxy conf]# ll
total 4
-rw-rw---- 1 root root 373 Sep  5 15:04 mysql-proxy.conf
[root@proxy conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

7）查看文件是否启动成功：启动成功

[root@proxy conf]# netstat -ntlp
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:3306            0.0.0.0:*               LISTEN      2162/mysql-proxy    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1001/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1378/master         
tcp6       0      0 :::22                   :::*                    LISTEN      1001/sshd           
tcp6       0      0 ::1:25

2.2 测试读写分离

1）主库建立远程链接用户：

[root@master ~]# mysql -uroot -pServer+147
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

2）测试最大链接当最大链接超过两个，连接到备库：

[root@proxy ~]# yum install lsof -y
[root@proxy ~]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2162 root   10u  IPv4  24832      0t0  TCP *:mysql (LISTEN)

2）客户端链接数据库：当用户链接数大与两个的时候，就会自动转到备库。

[root@proxy ~]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2162 root   10u  IPv4  24832      0t0  TCP *:mysql (LISTEN)
mysql-pro 2162 root   11u  IPv4  28191      0t0  TCP proxy.example.com:mysql->172.25.5.1:44404 (ESTABLISHED)
mysql-pro 2162 root   12u  IPv4  28061      0t0  TCP proxy.example.com:45548->172.25.5.11:mysql (ESTABLISHED)
mysql-pro 2162 root   13u  IPv4  28161      0t0  TCP proxy.example.com:45550->172.25.5.11:mysql (ESTABLISHED)
mysql-pro 2162 root   14u  IPv4  28192      0t0  TCP proxy.example.com:47752->172.25.5.12:mysql (ESTABLISHED)

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