百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

干货分享:Mysql8 主从复制架构-原理、搭建、监控

wptr33 2025-03-13 21:06 23 浏览

一、原理部分

常见的主从方案

  • 一主一从 M-S

最简单的主从方式

  • 一主多从 M-S-S-S

一主多从架构是一种常见的主从架构，这种架构在实施上较为简单有效。不仅可以实现高可用性（HA），还可以实现读写分离。

主从复制的概念

MySQL的主从复制是一个非常重要的数据同步技术，它允许数据从一个MySQL数据库服务器主节点复制到一个或多个从节点。这种机制提高了数据库的并发性能和可用性。MySQL 默认采用异步复制方式。

目前来说，MYSQL提供了两种方式来实现主从复制

  • 基于bin-log 传统的方式，MySQL1.5版本就有了。
  • 基于事务(GTID) global transaction identifieds ，MySQL5.7版本开始提供，其底层也是基于binlog .

基于bin log的主从复制

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。 因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

----------------------------------------------------------------------------------------------------------------

binlog记录格式

MySQL 主从复制有三种方式：

  1. 基于SQL语句的复制（statement-based replication，SBR），
  2. 基于行的复制（row-based replication，RBR)，
  3. 混合模式复制（mixed-based replication,MBR)。

对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。

  • Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。
  • Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。
  • Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式. 这个参数主要影响主从复制。

----------------------------------------------------------------------------------------------------------------

GTID复制模式

GTID底层也是基于bin log 。

1、全局事物标识：global transaction identifieds。

2、GTID事物是全局唯一性的，且一个事务对应一个GTID。

3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。

6、在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）


基于GTID复制实现的工作原理

主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。

从节点的I/O线程将变更的bin log，写入到本地的relay log中。

SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。

如果有记录，说明该GTID的事务已经执行，从节点会忽略。

如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

MySQL 主从复制主要用途

读写分离 : 有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

数据实时备 : 当系统中某个节点发生故障时，可以方便的故障切换

高可用HA

架构扩展: 随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL复制性能优化

影响主从延迟的因素

主库写入二进制日志的时间 ------> 控制主库的事务大小，分割大事务

二进制日志传输的时间 ------> 使用mixed日志格式 或者 使用row,但要set binlog_row_image=minimal;

默认情况下从库只有一个SQL线程，主库上并发修改但到了从库变成了一个线程串行 -----> 使用多线程复制 （5.6提供的功能） ,在mysql中可以按照逻辑时钟的方式来分配SQL线程

MySQL主从复制无法解决的问题

  • 无法分担主库的写压力 ---->分库分表
  • 无法提供读写分离的功能
  • 无法进行自动故障转移及主从切换—> MMM或者 MHA（推荐）


二、实战部分

（一）基于二进制日志文件的方法

配置 master主服务器（核心部分）

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认为binlog；在MySQL 8.0中，无论是否指定--log bin选项，默认情况下都会启用二进制日志记录，并将log_bin系统变量设置为ON。
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32-1. ；主数据库和从数据库的server-id不能重复
server-id=1
###可选配置
# 需要主从复制的数据库，如多个则重复配置
binlog-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步），如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
  • 启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
  • 接着给创建的用户授权同步复制权限：
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  • 刷新权限生效：
FLUSH PRIVILEGES
  • 接着执行命令查看并记下binary log二进制日志文件名 File 以及位置 Position的值，需要在从数据库用到：
SHOW MASTER STATUS;


配置 slave从服务器

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32-1. ；主数据库和从数据库的server-id不能重复
server-id=2

###可选配置
# 需要主从复制的数据库 ，如多个则重复配置
replicate-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） ，如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一） 
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin  
# log_replica_updates表示slave是否将复制事件写进自己的二进制日志，默认值ON开启；8.0.26版本之前使用log_slave_updates
log_replica_updates=ON
# 防止改变数据(只读操作，除了特殊的线程)
read_only=ON

低于8.0.23版本的语法：

CHANGE MASTER TO MASTER_HOST='192.168.34.120',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=825;

自8.0.23版本后的语法：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.34.120',SOURCE_PORT=3306,SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000007',SOURCE_LOG_POS=825;

最后开启主从复制工作：

低于8.0.22版本的语法：

START SLAVE;

自8.0.22版本后的语法：

START REPLICA;

可执行命令查看详细信息以及状态：

低于8.0.22版本的语法：

SHOW SLAVE STATUS;

自8.0.22版本后的语法：

SHOW REPLICA STATUS;

假如需要停止主从复制工作，则执行以下命令：

低于8.0.22版本的语法：

STOP SLAVE;

自8.0.22版本后的语法：

STOP REPLICA;

假如需要重启主从复制工作，则执行以下命令：

低于8.0.22版本的语法：

RESTART SLAVE;

自8.0.22版本后的语法：

RESTART REPLICA;

显示
Slave_IO_Running/Replica_IO_Running 和
Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及
Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！


（二）基于全局事务标识符（GTID）的方法

配置主数据库的 my.cnf 配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认为binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32-1. ；主数据库和从数据库的server-id不能重复
server-id=1     
     
#开启 GTID
gtid_mode=ON
enforce_gtid_consistency=ON

###可选配置
# 需要主从复制的数据库，如多个则重复配置
binlog-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步），如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062

启动主数据库，创建一个同步复制用户（非必需，也可以用root用户，但不建议）：

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

接着给创建的用户授权同步复制权限：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

最后刷新权限生效：

FLUSH PRIVILEGES;

接着执行命令查看状态，对比发现与基于二进制日志文件的方法不同的是Executed_Gtid_Set会有一个具体的全局事务id值：

SHOW MASTER STATUS;

配置从数据库的my.cnf配置文件（只贴核心部分）：

###主从数据库配置核心部分
[mysqld]
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=mysql-bin
# 服务器唯一id，默认为1，值范围为1～2^32-1. ；主数据库和从数据库的server-id不能重复
server-id=2

#开启 GTID
gtid_mode=ON
enforce_gtid_consistency=ON

###可选配置
# 需要主从复制的数据库 ，如多个则重复配置
replicate-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） ，如多个则重复配置
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存 
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一） 
binlog_format=row
# 配置二进制日志自动删除/过期时间，单位秒，默认值为2592000，即30天；8.0.3版本之前使用expire_logs_days，单位天数，默认值为0，表示不自动删除。
binlog_expire_logs_seconds=2592000
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断，默认OFF关闭，可选值有OFF、all、ddl_exist_errors以及错误码列表。8.0.26版本之前使用slave_skip_errors
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
replica_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin
# log_replica_updates表示slave是否将复制事件写进自己的二进制日志，默认值ON开启；8.0.26版本之前使用log_slave_updates
log_replica_updates=ON
# 防止改变数据(只读操作，除了特殊的线程)
read_only=ON

低于8.0.23版本的语法：

CHANGE MASTER TO MASTER_HOST='192.168.34.120',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

自8.0.23版本后的语法：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.34.120',SOURCE_PORT=3306,SOURCE_USER='repl',SOURCE_PASSWORD='123456',SOURCE_AUTO_POSITION=1;

最后开启主从复制工作：

低于8.0.22版本的语法：

START SLAVE;

自8.0.22版本后的语法：

START REPLICA;

可执行命令查看详细信息以及状态：

低于8.0.22版本的语法：

SHOW SLAVE STATUS;

自8.0.22版本后的语法：

SHOW REPLICA STATUS;

显示
Slave_IO_Running/Replica_IO_Running 和
Slave_SQL_Running/Replica_SQL_Running 为 Yes ，以及
Slave_IO_State/Replica_IO_State 为 Waiting for master to send event/Waiting for source to send event，则证明主从复制成功！


主从复制监控放在下一篇文章，感兴趣的进我主页查看！

相关推荐

redis的八种使用场景

前言:redis是我们工作开发中，经常要打交道的，下面对redis的使用场景做总结介绍也是对redis举报的功能做梳理。缓存Redis最常见的用途是作为缓存，用于加速应用程序的响应速度。...

基于Redis的3种分布式ID生成策略

在分布式系统设计中，全局唯一ID是一个基础而关键的组件。随着业务规模扩大和系统架构向微服务演进，传统的单机自增ID已无法满足需求。高并发、高可用的分布式ID生成方案成为构建可靠分布式系统的必要条件。R...

基于OpenWrt系统路由器的模式切换与网页设计

摘要：目前商用WiFi路由器已应用到多个领域，商家通过给用户提供一个稳定免费WiFi热点达到吸引客户、提升服务的目标。传统路由器自带的Luci界面提供了工厂模式的Web界面，用户可通过该界面配置路...

这篇文章教你看明白 nginx-ingress 控制器

主机nginx一般nginx做主机反向代理（网关）有以下配置...

如何用redis实现注册中心

一句话总结使用Redis实现注册中心：服务注册...

爱可可老师24小时热门分享(2020.5.10)

No1.看自己以前写的代码是种什么体验？No2.DooM-chip!国外网友SylvainLefebvre自制的无CPU、无操作码、无指令计数器...No3.我认为CS学位可以更好，如...

Apportable:拯救程序员，IOS一秒变安卓

摘要：还在为了跨平台使用cocos2d-x吗,拯救objc程序员的奇葩来了,ApportableSDK:FreeAndroidsupportforcocos2d-iPhone。App...

JAVA实现超买超卖方案汇总，那个最适合你，一篇文章彻底讲透

以下是几种Java实现超买超卖问题的核心解决方案及代码示例，针对高并发场景下的库存扣减问题：方案一：Redis原子操作+Lua脚本（推荐）//使用Redis+Lua保证原子性publicbo...

3月26日更新 快速施法自动施法可独立设置

2016年3月26日DOTA2有一个79.6MB的更新主要是针对自动施法和快速施法的调整本来内容不多不少朋友都有自动施法和快速施法的困扰英文更新日志一些视觉BUG修复就不翻译了主要翻译自动施...

Redis 是如何提供服务的

在刚刚接触Redis的时候，最想要知道的是一个’setnameJhon’命令到达Redis服务器的时候，它是如何返回’OK’的？里面命令处理的流程如何，具体细节怎么样？你一定有问过自己...

lua _G、_VERSION使用

到这里我们已经把lua基础库中的函数介绍完了，除了函数外基础库中还有两个常量，一个是_G，另一个是_VERSION。_G是基础库本身，指向自己，这个变量很有意思，可以无限引用自己，最后得到的还是自己，...

China's top diplomat to chair third China-Pacific Island countries foreign ministers' meeting

BEIJING,May21(Xinhua)--ChineseForeignMinisterWangYi,alsoamemberofthePoliticalBureau...

移动工作交流工具Lua推出Insights数据分析产品

Lua是一个适用于各种职业人士的移动交流平台，它在今天推出了一项叫做Insights的全新功能。Insights是一个数据平台，客户可以在上面实时看到员工之间的交流情况，并分析这些情况对公司发展的影响...

Redis 7新武器:用Redis Stack实现向量搜索的极限压测

当传统关系型数据库还在为向量相似度搜索的性能挣扎时，Redis7的RedisStack...

Nginx/OpenResty详解，Nginx Lua编程，重定向与内部子请求

重定向与内部子请求Nginx的rewrite指令不仅可以在Nginx内部的server、location之间进行跳转，还可以进行外部链接的重定向。通过ngx_lua模块的Lua函数除了能实现Nginx...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12