Mysql的读写分离

最新推荐文章于 2024-04-22 16:39:26 发布
最新推荐文章于 2024-04-22 16:39:26 发布
阅读量221 收藏
点赞数
分类专栏： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/even160941/article/details/97614281
版权

文章目录

mysql读写分离

什么是读写分离

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。即主从复制和读写分离是离不开的。

原理

在这里插入图片描述

  • 应用：当服务器的业务压力过大时，如果对该服务器既读又写，会影响用户体验。所以我们采取读写分离的方式，对只读库（配置低一点），对读写库（配置高速存储：固态硬盘等。）
  • 读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。
读写分离的好处
  • 分摊服务器压力，提高机器的系统处理效率
    读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；
  • 增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务。
实现方式
  • 目前较为常见的Mysql读写分离分为两种：
  • 基于程序代码内部实现
    在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
  • 基于中间代理层实现
    代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库，有以下代表性的程序。
    1.mysql_proxy。mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。
    2.Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
    3.Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存数过程。
    注意：不是所有的应用都能够在基于程序代码中实现读写分离，像一些大型的java应用，如果在程序代码中实现读写分离对代码的改动就较大，所以，像这种应用一般会考虑使用代理层来实现。

实验环境

主机（ip）功能
server1(172.25.24.1)读写主机
server2(172.25.24.2)只读主机
server3(172.25.24.3)proxy
真机(172.25.24.24)客户端

实验过程

server1和server2恢复gtid主从复制状态
  • 因为做了组复制，先关闭组复制，关闭服务，删除目录下的文件，再开启服务。
  • 以下步骤server1和server2相同。
[root@server1 mysql]# systemctl stop mysqld.service 
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# ls
[root@server1 mysql]# systemctl start mysqld

获取密码：[root@server1 mysql]# cat /var/log/mysqld.log | grep password
安全初始化：[root@server1 mysql]# mysql_secure_installation
  • server1，并修改配置文件，重启服务：
[root@server1 mysql]# mysql -uroot -p
mysql> CREATE USER 'repl'@'172.25.24.%' IDENTIFIED BY 'Wsp+123ld';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.24.%';
mysql> flush privileges;
mysql> show master status;

[root@server1 mysql]# vim /etc/my.cnf

 29 log-bin=mysql-bin
 30 server_id=1
 31 gtid_mode=ON
 32 enforce_gtid_consistency=ON

[root@server1 mysql]# systemctl restart mysqld
  • server2，并修改配置文件，重启服务：
mysql> change master to master_host='172.25.24.1',master_user='repl',master_password='Wsp+123ld',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;

[root@server2 mysql]# vim /etc/my.cnf
 29 server_id=2
 30 gtid_mode=ON
 31 enforce_gtid_consistency=ON
[root@server2 mysql]# systemctl restart mysqld
  • server1上建表插入数据检测，gtid主从复制有没有成功。
mysql> create database westos;
mysql> use westos;
Database changed
mysql> create table userlist(
    -> username varchar(10) not null,
    -> password varchar(10) not null);
mysql> desc userlist;
mysql> show master status\G;

在这里插入图片描述
在这里插入图片描述

server3
  • 如果mysqld服务是开启的状态，首先关闭mysqld，保证3306端口没有被占用。
  • 获取安装包：mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
    在这里插入图片描述
  • 解压到指定目录，并重命名：
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@server3 ~]# cd /usr/local/
[root@server3 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy

在这里插入图片描述

  • 新建日志目录，配置文件目录
[root@server3 local]# cd mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
[root@server3 mysql-proxy]# mkdir logs
  • 根据–help查看帮助，写配置文件
[root@server3 mysql-proxy]# vim conf/mysql-proxy.conf

[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.24.1:3306
proxy-read-only-backend-addresses=172.25.24.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/logs/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
plugins=proxy
log-level=debug
keepalive=true
daemon=true
  • 修改lua源文件：
[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/
[root@server3 mysql-proxy]# vim rw-splitting.lua

 37 -- connection pool
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,   #为了实验效果明显修改为1和2
 41                 max_idle_connections = 2,
 42 
 43                 is_debug = false
 44         }
 45 end

在这里插入图片描述

  • 开启服务：
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
  • 查看进程，日志，端口，目的是查看mysql-proxy服务有没有成功开启。
[root@server3 mysql-proxy]# cd logs/
[root@server3 logs]# ls
mysql-proxy.log  mysql-proxy.pid
[root@server3 logs]# cat mysql-proxy.log 

2019-07-28 12:50:15: (message) proxy listening on port 0.0.0.0:3306
2019-07-28 12:50:15: (message) added read/write backend: 172.25.24.1:3306
2019-07-28 12:50:15: (message) added read-only backend: 172.25.24.2:3306

[root@server3 logs]# netstat -antlupe | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      0          26473      2094/mysql-proxy

在这里插入图片描述
在这里插入图片描述

  • server3安装lsof：[root@server3 logs]# yum install lsof -y
server1
  • 登陆之后，新建从真机登陆的用户
mysql> grant insert,update,select on *.* to zyw@'%' identified by 'Wsp+123ld';  #除了删除delete，数据只能多不能少
mysql> flush privileges;
mysql> select * from mysql.user;
测试读
  • 真机打开一个shell，登陆数据库。
[kiosk@foundation24 ~]$ mysql -h 172.25.24.3 -uzyw -pWsp+123ld
  • server3查看
[root@server3 logs]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2094 root   10u  IPv4  26473      0t0  TCP *:mysql (LISTEN)
mysql-pro 2094 root   11u  IPv4  26697      0t0  TCP server3:mysql->foundation24.ilt.example.com:38366 (ESTABLISHED)
mysql-pro 2094 root   12u  IPv4  26698      0t0  TCP server3:56648->server1:mysql (ESTABLISHED)

在这里插入图片描述

  • 真机再打开一个shell，登陆数据库。
[kiosk@foundation24 ~]$ mysql -h 172.25.24.3 -uzyw -pWsp+123ld
  • server3查看
[root@server3 logs]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2094 root   10u  IPv4  26473      0t0  TCP *:mysql (LISTEN)
mysql-pro 2094 root   11u  IPv4  26697      0t0  TCP server3:mysql->foundation24.ilt.example.com:38366 (ESTABLISHED)
mysql-pro 2094 root   12u  IPv4  26698      0t0  TCP server3:56648->server1:mysql (ESTABLISHED)
mysql-pro 2094 root   13u  IPv4  26952      0t0  TCP server3:mysql->foundation24.ilt.example.com:38368 (ESTABLISHED)
mysql-pro 2094 root   14u  IPv4  26953      0t0  TCP server3:56654->server1:mysql (ESTABLISHED)

在这里插入图片描述

  • 真机再打开一个shell，登陆数据库。
[kiosk@foundation24 ~]$ mysql -h 172.25.24.3 -uzyw -pWsp+123ld
  • server3查看。
[root@server3 logs]# lsof -i:3306
COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 2094 root   10u  IPv4  26473      0t0  TCP *:mysql (LISTEN)
mysql-pro 2094 root   11u  IPv4  26697      0t0  TCP server3:mysql->foundation24.ilt.example.com:38366 (ESTABLISHED)
mysql-pro 2094 root   12u  IPv4  26698      0t0  TCP server3:56648->server1:mysql (ESTABLISHED)
mysql-pro 2094 root   13u  IPv4  26952      0t0  TCP server3:mysql->foundation24.ilt.example.com:38368 (ESTABLISHED)
mysql-pro 2094 root   14u  IPv4  26953      0t0  TCP server3:56654->server1:mysql (ESTABLISHED)
mysql-pro 2094 root   15u  IPv4  26977      0t0  TCP server3:mysql->foundation24.ilt.example.com:38370 (ESTABLISHED)
mysql-pro 2094 root   16u  IPv4  26978      0t0  TCP server3:46060->server2:mysql (ESTABLISHED)

在这里插入图片描述

  • 真机登陆，<=2个shell，都没有开启读写分离模式，我们打开第三个shell登陆时，才是从server2上读。
测试写
  • server2，停止gtid主从复制。
mysql> stop slave;
  • 真机写入数据：
MySQL [(none)]> insert into westos.userlist values('user2','111');

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

  • 写成功，但真机和server2都查不到刚写入的数据，server1可以查看到。所以读写分离成功。
且听风吟zyw
关注
专栏目录
springboot+mysql读写分离
07-29
springboot结合mysql主从来实现读写分离 一、实现的功能 1、基于springboot框架，application.yml配置多个数据源，使用AOP以及AbstractRootingDataSource、ThreadLocal来实现多数据源切换，以实现读写分离。mysql...
MySQL读写分离
FateZRK的博客
06-30 6780
在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。 但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。 通过amoeba服务器代理访问mysq
你如何理解mysql的读写分离_面试官：谈谈你对Mysql数据库读写分离的了解，并且有哪些注意事项？...
weixin_35044595的博客
01-19 410
这篇文章讲述的不是Mysql具体的如何实现读写分离，而是指什么时候需要上读写分离，及其相关的注意事项。因为用户的增多，数据的增多，单机的数据库往往支撑不住快速发展的业务，所以数据库集群就产生了！今天来说说读写分离的数据库集群方式！读写分离顾名思义就是读和写分离了，对应到数据库集群一般都是一主一从(一个主库，一个从库)或者一主多从(一个主库，多个从库)，业务服务器把需要写的操作都写到主数据库中，读的...
面试官：Mysql为什么要读写分离
poor_charlie的博客
10-26 1897
读写分离的场景 当数据库系统出现瓶颈时，有很多种优化方式，读写分离只能算是其中的一种，它主要解决的问题是，数据库的读多写少，读请求非常多，但是写请求非常少；为什么呢？我们分几种情况讨论： 读多写少： 1）并发读不需要事务，而写入数据需要事务， 假如写入数据是200ms，而读数据是10ms；1s内如果全是读请求，则可以达到100qps。如果1s内有2个写请求，qps可能就降低到60qps； 2）此种情况下做读写分离，一个主库可以挂多个从库(假如3个)，那么此时1s的并发直接提升到300qps； 写多读少
MySQL 读写分离实例详解
12-15
MySQL 读写分离是一种常见的数据库优化策略，它将数据库的读操作和写操作分配到不同的服务器上，以提高系统的并发处理能力和响应速度。在大型应用系统中，由于大量的读操作通常远超过写操作，这种分离可以显著提升...
MySQL数据库：读写分离
张维鹏的博客
12-26 1万+
读写分离解决的是，数据库的写操作，影响了查询的效率，所以读写分离适用于读远大于写的场景。读写分离的实现基础是主从复制，主数据库利用主从复制将自身数据的改变同步到从数据库集群中，然后主数据库负责处理写操作（当然也可以执行读操作），从数据库负责处理读操作，不能执行写操作。并可以根据压力情况，部署多个从数据库提高读操作的速度，减少主数据库的压力，提高系统总体的性能。
Mysql读写分离的四种方案
最新发布
ZhaZha_Yi的博客
04-22 1689
数据库写入效率要低于读取效率，一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，这是做读写分离的原因。以上四种方案各有优缺点，基于MySQL proxy代理的方式对于应用来说相对简单，但是在项目稳定性、事务支持性等方面还存在问题；而基于应用内路由的方式固然灵活度比较高，但是也增加了应用逻辑的复杂度；基于mysql-connector-java的jdbc驱动和sharding-jdbc的方式在使用上相对简单，但限制了需要使用java开发。
Mysql-读写分离
海的那边，还是海吗
09-14 170
MySQL读写分离是一种数据库架构策略，它将数据库的读操作（查询）和写操作（插入、更新、删除）分离到不同的数据库实例或服务器上。通常情况下，写操作会在主数据库上执行，而读操作则会在一个或多个从数据库（也称为副本）上执行。这种分离的目的是提高数据库的性能、可用性和可伸缩性。MySQL读写分离是提高数据库性能和可用性的关键策略。通过将读操作分发到多个从数据库，减轻主数据库的负载，提高了系统的性能和可伸缩性。然而，读写分离也需要考虑数据一致性和维护等问题。正确实施和维护。
mysql读写分离原理及步骤
热门推荐
张必安的博客
05-21 2万+
数据库每天都要承担的网站的数据更新，所以大量的读写任务都在一个机器上压力非常大。使用读写分离可以有效的缓解数据库压力。读写分离要建立在读写分离的基础上，关于读写分离的搭建方法这里不再叙述，大家可以看博主其他文章。这里使用amoeba来实现mysql读写分离。大概的一个原理图如下。都是centos6.5的系统这里使用两个从服务器承担读的任务，主服务器承担写的任务。主服务器写入数据，两个从服务器复制，...
mysql主从复制和读写分离实战
fsx2550553488的博客
06-05 1万+
目录 mysql主从复制和读写分离实战mysql主从复制Master配置Slave1配置Slave2配置主从复制测试mysql读写分离mysql_proxy配置读写分离测试mysql主从复制和读写分离实战mysql主从复制mysql主从复制模型：(本次是一主二从模型)Master：172.25.254.7slave1：172.25.254.8slave2：172.25.254.9mysql_pr...
mysql读写分离
qq_42711214的博客
10-21 924
mysql的读写分离的基本原理是： 让master（主数据库）来响应事务性操作，让slave（从数据库）来响应select非事务性操作，然后再采用主从复制来把master上的事务性操作同步到slave数据库中。 配置环境： server3安装mysql-proxy调度器 server1和server2为之前设置好的一主一从 tar zxf mysql-proxy-0.8.5-linux-e...
数据库主从复制与读写分离(了解)
weixin_34292287的博客
07-09 1814
在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 如图所示： 一、 案例前...
Mysql读写分离
weixin_40213018的博客
08-07 703
目的 分散数据库的访问压力，提高访问效率，主服务器负责读/写请求，从服务器只负责读请求。 工作原理 主服务器将数据更新记录到日志（bin-log），然后定期同步到从服务器。 步骤1： 所有数据更新都会被主库记录到主库的二进制日志（也叫变更日志）。 步骤2： 从库IO线程从主库上读取二进制日志，写入到从库的中继日志上。 ...
MySQL读写分离配置实战
"MySQL读写分离实现方法" 在MySQL数据库系统中，读写分离是一种常见的优化策略，用于提高数据库系统的性能和可扩展性。当数据库负载较大时，读写分离能够将读请求和写请求分散到不同的服务器上，减轻主数据库的压力...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值