玖叶教程网

前端编程开发入门

2024年04月08日

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

一、原理部分

常见的主从方案

  • 一主一从 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，则证明主从复制成功！


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

标签：mysqlformat 

作者:gojiuye , 分类:技术精选 , 浏览:34 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言