MsSQL 的主从复制与读写分离

已于 2022-08-23 16:45:26 修改
阅读量600 收藏 1
点赞数
文章标签： sqlserver 数据库 sql
于 2022-06-30 19:53:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/FBdart/article/details/125546914
版权

文章目录

一、MySQL 主从复制

1.1 主从复制原理有以及架构

  • 基于语句的复制（Statement,MySQL默认类型）
  • 基于 行的复制（ROW）
  • 混合类型的复制（MIXED）

1.1.1 服务性能扩展方式

  • 向上扩展，垂直扩展
  • 向外扩展，横向扩展

1.2 MySQL 扩展

  • 读写分离
  • 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单项复制。

1.2.1 读写分离是什么？（读写分离基本原理）

读写分离，基本原理：让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

每个事务更新数据完成之前，master 在二进制文件记录这些数据。写入二进制日志完成后，master 通知存储引擎提交事务。

1.2.1.1 三个线程，两个日志

两个日志：Binary log（二进制日志）；relay log（中继日志）
三个线程：主服务器中的 dump 线程；从服务器中的 I/O 线程，SQL 线程。

简单理解，通俗易懂的：

Binary log：二进制日志；relay log：中继日志
主服务器会把数据写进二进制文件里面—》
然后从服务器的i/o线程向dump发出同步请求，dump把数据发送给i/o线程，然后i/o写入到本地的中继日志—》
然后通过mysql从服务器的sql线程来读取中继日志，实现重放的功能，同步到自己的数据库中
最终实现mysql主从复制同步

  • Dump thread：是一个监听的线程（要么是等待线程，要么是睡眠线程）
  • Dump线程平常是不工作的，只有在 I/O 线程监听到有新的数据写入时，需要通过dump线程来进行相应，响应请求并传输数据。
  • 比如
    在这里插入图片描述

1.2.2 为何需要读写分离？

  • 因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
  • 但是数据库的“读”（读10000条数据可能只要5秒钟）。
  • 所以读写分离，解决的是，数据库的写入，影响了查询的效率。

1.2.3 何时需要读写分离？

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

1.2.4 主从复制；读写分离

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

  • 读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

1.2.5 MySQL 的四种同步方式

  • 异步同步（asynchronous）

搭建简单，使用非常广泛，从mysql诞生之初，就产生了这种架构，性能非常好，可为非常成熟。但是这种架构数据是异步的，所以有丢书数据库的风险。

主服务器不需要等待从服务器是否把二进制文件拷入到中继日志中，就开始读取新的请求了。

  • 同步复制（sync replication）

同步复制提供最佳安全性，保证数据安全。不丢失数据，损失性能。

主服务器需要等待从服务器把二进制文件拷贝到中继日志中，并且还要从库中执行完成，主服务器才能开始读取新的请求了。

  • 半同步复制（Semi synchronous）
    性能、功能都介于异步和全同步之间。从 MySQL 5.5版本开始诞生，目的是为了折中，上面两种架构性能已优缺点。

主服务器需要等待从服务器是否把二进制文件拷入到中继日志中，不管你是否在从库中执行完成，就开始读取新的请求了。

  • 无损复制，增强版的半同步复制（lossless replication）

数据零丢失，性能好，mysql 5.7 诞生。

1.2.6 使用场景

  • 在企业应用中，成熟的业务通常数据量都比较大。
  • 单台的 MySQL 在安全性、高可用性和高并发方面都无法满足实际的需求。
  • 配置多台主从数据库服务器以实现读写分离。

1.2.7 MySQL 支持的复制类型

  1. STATEMENT：基于语句的复制
  • 在服务器上面执行 sql 语句，在从服务器上执行同样的语句， MySQL 默认采用基于语句的复制。
  1. ROW：基于行的复制
  • 把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
  1. MIXED：混合类型的复制
  • 默认采用既有语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行复制。

1.2.8 较为常见的 MySQL 读写分离

  1. 基于程序代码内部实现
    在代码中根据 select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。
    优点：性能较好，在程序代码中实现，不需要增加额外的设备为硬件开支；
    缺点：需要开发人员来实现，运维人员无从下手。
    但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的 Java 应用，如果在程序代码中实现读写分离对代码改动就较大。

  2. 基于中间代理层实现

    代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库的代表性程序。

    • MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断。

    • Atlas 是由奇虎 360 的 Web 平台部基础架构团队开发维护的一个基于 MySQL 协议的数据中间层项目。它是在 mysql-proxy 0.8.2 版本的基础上，对其进行了优化，增加了一些新的功能特性。360 内部使用 Atlas 运行的 mysql 业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

    • Amoeba 由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

由于使用 MySQL Proxy 需要写大量的 Lua 脚本，这些 Lua 并不是现成的，而是需要自己去写。这对于并不熟悉 MySQL Proxy 内置变量和 MySQL Protocol 的人来说是非常困难的。
Amoeba 是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

1.2.9 MySQL 主从复制与读写分离原理

客户端发送读写操作到 Amoeba 服务器，Amoeba 服务器将写的操作发送到主服务器，主服务器将写的操作记录到二进制日志中，从服务器有两个线程，一个 I/O 线程一个 SQL 线程，从服务器的 I/O 线程会在 Master 上打开一个普通的连接，Binlog dump 线程会从 Master 的二进制日志中读取事件，I/O 线程将这些事件写入中继日志，中继日志运行在缓存中，SQL 线程读取中继日志中的事件，并在自己的数据库中进行重放，Amoeba 对两个从服务器进行读操作，读取从服务器同步的事件。

二、主从复制工作过程

（1）在每个事务更新数据完成之前，Master在二进制日志（Binary log）记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务。

（2）Slave将Master的复制到其中继日志（Relay log）。首先slave开始一个工作线程（I/O），I/O线程在Master上打开一个普通的连接，然开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果以及跟上Master，它会睡眠并等待Master产生新的事件，I/O线程将这些事件写入中继日志。

（3）SQL slave thread（SQL从线程）处理该线程的最后一步，SQL线程从中继日志中读取事件，并重放其中的事件而更新slave数据，使其与master的数据保持一致，只要该线程与I/O线程保持一致，中继日志通常会位于OS缓存中，所以中继日志的开销很小。
复制过程有一个和重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作。

PS：

  • 中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。
  • 复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。

在这里插入图片描述

出现高并发时，发生 I/O 阻塞时，一般会如何处理？

在服务器中加上一个 slave 中继，用来分担主服务器的压力，写入数据时数据量如果过大的话，有可能会造成阻塞，此时通过 slave 中继来分担，它是用来解决高并发情况下，解决 I/O 阻塞的问题。

2.1 Mysql 主从复制部署

Master：192.168.10.13
Slave1：192.168.10.14
Slave2：192.168.10.15

关闭防火墙

systemctl stop firewalld
systemctl diable firewalld
setenforce 0

2.1.1 Mysql的主从服务器时间同步

2.1.1.1 Master配置（192.168.10.13）
yum install -y ntp

vim /etc/ntp.conf

末尾添加
server 127.127.10.0						——设置本地是时钟源127.127 ，注意修改网段 10网段
fudge 127.127.10.0 stratum 8			——设置时间层级为8（限制在15内）

server 127.127.10.0	
fudge 127.127.10.0 stratum 8

service ntpd start

在这里插入图片描述

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

2.1.1.2 Slave 1（192.168.10.14）
yum install ntp ntpdeta -y

service ntpd start
/usr/sbin/ntpdate 192.168.10.13			——进行时间同步

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.10.13

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

在这里插入图片描述
在这里插入图片描述
Slave 2 （192.168.10.15）

yum install ntp ntpdeta -y

service ntpd start
/usr/sbin/ntpdate 192.168.10.13		——进行时间同步

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.10.13
2.1.1.3 Master 的 Mysql 配置（192.168.10.13）
vim /etc/my.cnf

server-id = 1
log-bin=master-bin			——添加，主服务器开启二进制日志
binlog_format = MIXED
log-slave-updates=true		——添加，允许slave从master复制数据时可以写入到自己的二进制日志

在这里插入图片描述

systemctl restart mysqld

mysql -u root -pabc123

GRANT REPLICATION SLAVE ON *.* TO 'myslvae'@'192.168.10.%' IDENTIFIED BY '123456';	——给从服务器授权

FLUSH PRIVILEGES; 		——刷新权限

show master status;

在这里插入图片描述
重启mysql服务重启不了，并且没有报错，该如何解决？
在这里插入图片描述

2.1.1.4 Slave 的 Mysql 配置
vim /etc/my.cnf

server-id = 2		——修改，注意id与Master的不同，两个Slave的id也要不同

relay-log=relay-log-bin			——添加，开启中继日志，从主服务器上同步日志文件记录到本地

relay-log-index=slave-relya-bin.index		——添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录

relay_log_recovery = 1      ——选配项

当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上获取日志，这样就保证了relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。

在这里插入图片描述

systemctl restart mysqld

mysql -u root -p123456
CHANGE master to master_host='192.168.10.13',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;				——配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

start slave;					——启动同步，如有报错执行 reset slave;
show slave status\G				——查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes			——负责与主机的io通信
Slave_SQL_Running: Yes			——负责自己的slave mysql进程
FBdart
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL主从复制与读写分离原理及用法详解
09-08
主要介绍了MySQL主从复制与读写分离原理及用法,结合实例形式详细分析了mysql数据库主从复制、读写分离基本概念、原理、用法及操作注意事项,需要的朋友可以参考下
Thinkphp实现MySQL读写分离操作示例
12-19
相对于其他方法实现MySQL的读写分离来说，采用Thinkphp框架实现MySQL的读写分离简单易用，其配置文件示例代码如下: 'DB_TYPE'=> 'mysql', 'DB_DEPLOY_TYPE' => 1, //开打支持多服务器 'DB_RW_SEPARATE'=>true,//数据库读写否分离 'DB_HOST'=> '192.168.11.101,192.168.11.102', 'DB_NAME'=>'test', 'DB_USER'=>'admin', 'DB_PWD'=>'admin101', 'DB_PREFIX' => 'fav_', 注意事项： 1、当采用原生态的sq
MySQL主从复制与读写分离
07-30
MySQL主从复制与读写分离MySQL主从复制与读写分离
CentOS服务器平台搭建mysql主从复制与读写分离的方法
09-09
主要介绍了CentOS服务器平台搭建mysql主从复制与读写分离的方法,结合实例形式较为详细的分析了CentOS平台搭建mysql主从复制与读写分离的步骤、设置方法、相关操作技巧与注意事项,需要的朋友可以参考下
MySQL主从复制与读写分离.docx
04-13
MySQL主从复制与读写分离
MSSQL 发布订阅，实现读写分离
weixin_30445169的博客
09-05 254
主库做增删改，从库只读。 大部分的数据库压力，都是由查询引起的，读写分离可以减轻数据库的压力。 1、在(主)数据库上对需要同步的数据进行发布。 2、在(从)数据库上对(主)数据库的发布进行订阅。 注：发布订阅都需要实际的服务器名称才能连接到服务器。 --查询服务器的名称和实际计算机的名称，如果两者不一致，就需要修改。 use master go sel...
【SQL】主从复制
null
12-12 1260
如何提升数据库并发能力：在实际工作中，我们常常将与MySQL配合来使用，当有请求时，会首先从缓存中进行查找，如果存在就直接取出。不存在再访问数据库，这样就提升了读取效率，减少后端数据库的访问压力。redis的缓存架构是高并发架构中非常重要的一环。此外，一般应用对数据库而言都是“”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构、进行读写分离，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。
MSQL主从复制
小婷子最美的博客
01-15 617
MYSQL主从复制 1、主从服务器分别作以下操作：  1.1、版本一致  1.2、初始化表，并在后台启动mysql  1.3、修改root的密码 2、修改主服务器master:  #vi /etc/my.cnf  [mysqld]  log-bin=mysql-bin //[必须]启用二进制日志  server-id=1 //[必须]服务器唯一ID，默认是1，一般取IP最
SQL Server 双机热备份-实现主从复制
weixin_45130813的博客
07-21 1万+
文章目录一、前期准备二、本地服务器配置三、本地服务器主数据库配置发布四、从服务器从数据库订阅设置四、总结 一、前期准备 1.服务器两台。（可本地下载一个VMware Workstation虚拟机，在虚拟机里面安装windows系统） 2.两台服务器各自安装SQL Server2008及以上版本数据库。 3.两台服务器一定要能够互通，简而言之就是需要能够互相共享文件，网络互通 4.关闭两台服务器的防火墙，具体关闭防火墙的方法请自行百度 二、本地服务器配置 1.打开SQL Server配置管理器 2.开启Sq
【SqlServer 高可用数据库读写分离 事务复制】
04-25 233
在实际开发中及时性要求不高的数据我们可以选择从从数据库中查询避免主数据库过高的负载。
sql server 主从同步配置
11-05
配置sql server主从服务器同步、实现灾难备份、读写分离
netscaler mysql_稳定高效的MySQL与MSSQL读写分离/连接复用/Sharding-把HTTP的技术推进到数据层...
weixin_28867333的博客
02-02 105
读了一些有关数据库读写分离的文章，大多是官方开源的MySQL-Proxy以及其衍生项目，而末尾往往谈到在实际部署时的困难重重。首先学习测试和部署的周期较长，同时在数据库中间多加了一层代理便多了一层故障点，需要专业的运维人员来继续开发和耐心维护，如出现问题只能把问题交给社区。在业务压力增大后往往发现这层代理的延迟越来越高形成瓶颈。因此我来谈谈NetScaler的DataStream技术。Citrix...
Sqlserver读写分离（主从备份)
songjuntao8的专栏
08-26 4418
一.前言 1. 背景 　　大部分场景中，DB操作80%是读，20%是写，对于时效性要求不高的数据，为了减少磁盘读和写的竞争，引入读写分离的概念，即在数据库上进行主从配置，一个主，多个从，实现主从同步，从而业务上实现读写分离。 　　读写分离在网站发展初期可以一定程度上缓解读写并发时产生锁的问题，将读写压力分担到多台服务器上。基本原理是让主数据库处理增、删、操作，而从数据库处理SELECT查询操作。随着系统的业务量不断增长，数据不断增多，数据库的IO操作压力会很大，读写分离也是数据库分库的一种方案。 .
mysql实现发布订阅_MSSQL 发布订阅，实现读写分离
weixin_42509236的博客
02-10 1070
主库做增删改，从库只读。大部分的数据库压力，都是由查询引起的，读写分离可以减轻数据库的压力。1、在(主)数据库上对需要同步的数据进行发布。2、在(从)数据库上对(主)数据库的发布进行订阅。注：发布订阅都需要实际的服务器名称才能连接到服务器。--查询服务器的名称和实际计算机的名称，如果两者不一致，就需要修改。use mastergoselect @@servername;select serverp...
基于Windows Server 2008 R2的WSFC实现 SQL Server 2012高可用性组（AlwaysOn Group）
weixin_33738555的博客
05-11 341
两年前的《SQL Server 2008 R2数据库镜像部署》，今天“再续前缘”—— 微软新一代数据库产品SQL Server 2012已经面世一段时间了，不管从功能上讲还是性能上的体现，较之其早期产品都有了很大提升。特别是其引入高可用性组（AlwaysOn Group, AG）这一概念和功能，大大增强和提高了SQL Server的可用性，在之前的镜像数据库的基础上有了质的...
MHA高可用配置和故障切换
qq_57377057的博客
09-27 1093
MySQL服务器中，单台服务器无法承受服务量就配置多台MySQL服务器，多台MySQL服务器或者MySQL服务器五冗余的情况下，配置主从复制。没有上去看服务器的读和写的压力不均衡可使用MySQL的读写分离，MySQL的读写分离中Master存在单点故障，就得用到MHA。
MySQL主从数据库配置（详细介绍）
weixin_45460791的博客
06-19 4484
这样就连接成功了 ，你可以在数据库中进行测试，在主数据库中创建一个数据库，从数据库也会创建一个。条件准备：准备两个数据库，这里就不详细介绍如何下载和安装数据库了，默认你已经有了两个基本数据库。2，重启从数据库服务（和主服务器相同的操作，不是重启主的服务器哦，而是你从服务器的数据库）default-storage-engine=MyISAM #一般从数据库作为读数据库。本文章是介绍如何实现数据库的主从配置，只允许从数据库进行读的操作。注意这几个位置，都是主数据库配置过的，见上面的一些图或者命令。
MySQL主从复制
热门推荐
m0_62473957的博客
04-13 2万+
一、MySQL主从复制原理 在实际的生产中，为了解决Mysql的单点故障已经提高MySQL的整体服务性能，一般都会采用「主从复制」。 比如：在复杂的业务系统中，有一句sql执行后导致锁表，并且这条sql的的执行时间有比较长，那么此sql执行的期间导致服务不可用，这样就会严重影响用户的体验度。 主从复制中分为「主服务器（master）「和」从服务器（slave）」，「主服务器负责写，而从服务器负责读」，Mysql的主从复制的过程是一个「异步的过程」。 这样读写分离的过程能够是整体的服务性能提高，即使写操作时间
SQL Server 读写分离(主从备份) 配置主从服务器
weixin_48449171的博客
07-26 705
SQL Server读写分离是一种常见的数据库架构模式，通过将数据库分为主服务器（写入服务器）和从服务器（只读服务器），实现数据库的负载均衡和提高系统性能。2.使用数据库连接字符串：在应用程序的数据库连接字符串中指定主服务器和从服务器的不同地址，根据操作类型选择适当的服务器。3.实施自动故障切换机制：使用自动故障切换机制，当主服务器故障时能够自动将从服务器切换为主服务器，减少系统的停机时间。1.数据同步延迟：主从服务器之间的数据同步存在一定的延迟，导致在写入后立即进行读取时，读取到的数据可能不是最新的。
搭建mysql主从复制与读写分离
最新发布
09-15
对于搭建MySQL主从复制和读写分离的步骤如下： 1. 配置主服务器： - 在主服务器的配置文件中开启二进制日志（binlog），可以通过在my.cnf文件中添加如下配置进行开启： ``` log-bin=mysql-bin server-id=1 ``` - 重启MySQL服务。 2. 配置从服务器： - 在从服务器的配置文件中添加如下配置： ``` server-id=2 relay-log=mysql-relay-bin read-only=1 ``` - 重启MySQL服务。 3. 创建复制用户： - 在主服务器上创建一个用于复制的用户，并赋予适当的权限： ``` CREATE USER 'replication'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; FLUSH PRIVILEGES; ``` - 注意修改密码为实际使用的密码。 4. 备份主服务器数据并导入到从服务器： - 在主服务器上执行以下命令备份数据： ``` mysqldump -u root -p --all-databases --master-data > dump.sql ``` - 将备份文件导入到从服务器： ``` mysql -u root -p < dump.sql ``` 5. 配置主从复制： - 在从服务器上执行以下命令，指定主服务器的地址、复制用户和密码，并开始复制： ``` CHANGE MASTER TO MASTER_HOST='主服务器IP', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.XXXXXX', MASTER_LOG_POS=XXX; START SLAVE; ``` - 注意替换主服务器IP、复制用户和密码，以及MASTER_LOG_FILE和MASTER_LOG_POS参数。 6. 配置读写分离： - 在从服务器上创建一个用于读取数据的用户，并赋予适当的权限： ``` CREATE USER 'reader'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON *.* TO 'reader'@'%'; FLUSH PRIVILEGES; ``` - 在应用程序中配置读写分离，将读请求发往从服务器，写请求发往主服务器。 完成上述步骤后，你将成功搭建MySQL主从复制和读写分离。请记住，以上步骤仅供参考，具体操作可能因环境和需求而有所不同。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值