Day05 03-MySQL主从-主主原理与搭建详解

已于 2023-06-12 16:17:02 修改
阅读量1.7k 收藏 5
点赞数 1
分类专栏： 全链路数据仓库 文章标签： mysql 数据库 MySQL主从 MySQL集群 MySQL集群架构
于 2023-06-12 16:15:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010839779/article/details/131171075
版权

文章目录

第十六章 MySQL的系统架构（主从架构）

16.1 MySQL集群架构的介绍

我们在前面使用到MySQL数据库的时候，只是一个单机的数据库服务。在实际的生产环境中，数据量可能会非常庞大，这样单机服务的MySQL在使用的时候，性能会受到影响影响。并且单机服务的MySQL的数据安全性也会受到影响。因此在生产环境中，我们通常搭建MySQL的集群架构，来提高庞大数据量的基础上的高性能读写的需求。

在常见的集群架构中，最常见的就是主从架构(Master-Slaves)

16.1.1 主从架构介绍

MySQL的主从架构，又有一些其他的名称：主从模式、主从复制等。所谓的主从架构指的是建立多个完全一样的数据库，其中一个数据库作为主库（主要是用的数据库），其他的作为从库（次要的数据库）。主从架构分为很多种：一主一从、双主架构、一主多从、多主多从等模式。通常主库可读可写，从库只读。

MySQL最常见也是最简单的主从架构的实现就是**主从复制(MySQL Replication)**模式，这也是MySQL自带的功能，无需借助第三方的工具，就可以实现一个主从架构的集群模式。

主从架构相比较于单机服务的MySQL来说，优势有很多，最常见的优势就是：写操作连接主库，读操作连接从库，实现读写分离。

16.1.2 主从复制的原理

主从复制是通过重演binlog来实现主库数据的异步复制。即在主库上打开binlog记录每一次的数据库操作，然后从库会有一个IO线程，负责跟主库建立TCP连接，请求主库将binlog传输到从库。此时主库上会有一个Log Dump线程，负责通过这个TCP连接吧binlog日志传输给从库的IO线程。接着从库的IO线程会把读取到的binlog日志数据写入自己的中继日志文件(Relay)中。然后从库上另外一个SQL线程会读取中继日志文件中的操作，进行操作重演，达到还原数据的目的。

MySQL主从复制原理图

  1. 主库的数据发生了变更，将日志写入到主库的binlog中。
  2. 主库的LogDump线程，将binlog文件传输到从库的IO线程。
  3. 从库的IO线程将接收到的binlog写入到relay log中。
  4. 从库的SQL线程读取relay log中的日志，并操作重演，将结果同步到从库中。
16.2 MySQL主从复制的实现
16.2.1 环境说明

要实现MySQL的主从架构的搭建，需要满足以下条件：

  • 所有节点的MySQL版本必须一致。
  • 所有节点的时间必须同步。
  • 所有节点需要启动binlog服务。
16.2.2 主库配置

我们需要编辑MySQL配置文件，这个配置文件在不同的操作系统中的位置和名字都不同，需要根据自己的操作系统来查找这个文件：

  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • Linux: /etc/my.cnf
  • macOS:
    • dmg安装: /etc/my.cnf
    • homebrew安装:
      • Intel CPU: /usr/local/homebrew/etc/my.cnf
      • AppleSilicon CPU: /opt/homebrew/etc/my.cnf
# 在[mysqld]的下方添加或修改如下属性:
# 服务节点的唯一标识，需要给集群中的每个服务分配一个单独的ID
server-id=101
# 打开binlog日志，并指定文件名
log_bin=master-bin
# binlog日志文件
log_bin-index=master-bin.index

修改完成之后，需要重启MySQL服务。

为root用户分配replication slave的权限：

# 登录到主库
mysql -uroot -p

# 为root用户分配权限
# MySQL8中，需要先添加 'root'@'%' 这个用户
# create user 'root'@'%' identified by '123456'
mysql> grant replication slave on *.* to 'root'@'%';
mysql> flush privileges;
# 查看主节点同步状态
mysql> show master status;
# +-------------------+----------+--------------+------------------+-------------------+
# | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
# +-------------------+----------+--------------+------------------+-------------------+
# | master-bin.000001 |      543 |              |                  |                   |
# +-------------------+----------+--------------+------------------+-------------------+
# 1 row in set (0.00 sec)

在上述输出结果中:

File: 当前日志文件

Position: 日志文件中的索引

Binlog_Do_DB: 需要记录binlog日志的库，不设置表示全部的库

Binlog_Ignore_DB: 不需要记录binlog日志的库

16.2.3 从库配置

我们需要编辑MySQL配置文件，这个配置文件在不同的操作系统中的位置和名字都不同，需要根据自己的操作系统来查找这个文件：

  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • Linux: /etc/my.cnf
  • macOS:
    • dmg安装: /etc/my.cnf
    • homebrew安装:
      • Intel CPU: /usr/local/homebrew/etc/my.cnf
      • AppleSilicon CPU: /opt/homebrew/etc/my.cnf
# 在[mysqld]的下方添加或修改如下属性:
# 服务节点的唯一标识，需要给集群中的每个服务分配一个单独的ID
# 一定要注意，不能和其他节点重复
server-id=102
# 打开binlog日志，并指定文件名
log_bin=slave-bin
# 打开relaylog日志
relay_log=slave-relay-bin
relay_log-index=slave-relay-bin.index
skip-slave-start

修改完成之后，需要重启MySQL服务。

然后登录到其他从库，设置从主库同步状态:

# 登录从库
mysql -uroot -p
# 设置同步主节点
change master to
master_host='192.168.10.101',		# 设置主库的地址
master_port=3306,					 # 设置主库使用的端口号
master_user='root',					 # 设置主库的用户名
master_password='123456',			 # 设置主库的密码
master_log_file='master-bin.000001', # 设置主库正在使用的binlog文件，可以在主库使用 show master status 查询
master_log_pos=543;					  # 设置从什么位置同步
# 开启slave
start slave;
# 查看主从同步状态
show slave status;
# 也可以使用 show slave status \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.10.101
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 916
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 1133
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

正常的结果是如上所示的结果，但是总有意外的时候:

有些同学可能会出现 Slave_IO_Running: Connecting 的状态，甚至是NO的状态，说明从库的IO线程启动失败。原因如下:

  1. 可能是主库设置错误，检查 master_host 和 master_port 的设置是否正确

  2. 可能是主库的用户名和密码错误，检查 master_user 和 master_password 是否正确

  3. 可能是主库防火墙未关闭，检查防火墙

  4. 可能是主库不能远程登录，检查主库用户的远程登录权限

  5. 可能是 master_log_file 文件设置出问题

  6. 可能是虚拟机克隆，导致的两个节点的uuid相同

    检查 /var/lib/mysql/auto.cnf 中记录的uuid，如果相同的话，随便修改一个，重启服务即可

上述几种错误情况在修改之后，都是需要重新启动slave服务的。先使用 stop slave 停止服务；再使用 start slave 开启

有些同学可能会出现 Slave_SQL_Running: No 的状态，说明从库的SQL线程启动失败，一般是因为执行主库同步过来的数据的时候失败了，例如需要创建的数据库、表已经存在导致。

解决方案：

  1. 删除从库中同名的库、表，从主库的日志中恢复数据。
  2. 如果想要保留从库中的库、表，先停止slave服务，设置 set global sql_slave_skip_counter = 1; 来设置需要跳过的错误的个数。1是可以修改的，想要跳过几个错误，就设置为多少。然后启动slave服务即可。
16.2.4 主从复制测试

我们在主库中创建数据库、创建表，可以在从库中看到有数据同步过来了。而且在从库中使用 show slave status \G 来查看从库的状态的时候，会发现记录的Pos位置已经更新。

16.3 MySQL主主复制的实现
16.3.1 主主复制介绍

MySQL的主从复制架构下，可以实现读写分离、业务分流，来降低单个数据库的压力。但是这种模式下会存在单点故障的问题，即如果主库节点宕机的情况下，对从库进行的操作并不会同步到主库中。这个数据库也就无效了。因此有的时候我们会搭建主主复制的架构，也叫做双主架构。

双主架构的实现，是在主从架构的基础之上的。将两台MySQL之间互为彼此的主库，同时又互为对方的从库。这样的实施方案下，既能做到分流，也能解决单点故障的问题。因为任何的一台节点故障，另外的一台都可以继续提供服务。

16.3.2 主库1配置

我们需要编辑MySQL配置文件，这个配置文件在不同的操作系统中的位置和名字都不同，需要根据自己的操作系统来查找这个文件：

  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • Linux: /etc/my.cnf
  • macOS:
    • dmg安装: /etc/my.cnf
    • homebrew安装:
      • Intel CPU: /usr/local/homebrew/etc/my.cnf
      • AppleSilicon CPU: /opt/homebrew/etc/my.cnf
# 在[mysqld]的下方添加或修改如下属性:
# 服务节点的唯一标识，需要给集群中的每个服务分配一个单独的ID
server-id=101
# 打开binlog日志，并指定文件名
log_bin=master-101-bin
# binlog日志文件
log_bin-index=master-101-bin.index
# 打开relaylog日志
relay_log=master-101-relay-bin
relay_log-index=master-101-relay-bin.index
skip-slave-start
# 防止两个主库中同时操作自增的字段导致字段冲突
auto_increment_increment=2	# 自增步长，一般有几个MySQL就设置为几
auto_increment_offset=1		# 自增起始值

修改完成之后，需要重启MySQL服务。

为root用户分配replication slave的权限：

# 登录到主库
mysql -uroot -p

# 为root用户分配权限
# MySQL8中，需要先添加 'root'@'%' 这个用户
# create user 'root'@'%' identified by '123456'
mysql> grant replication slave on *.* to 'root'@'%';
mysql> flush privileges;
16.3.3 主库2配置

我们需要编辑MySQL配置文件，这个配置文件在不同的操作系统中的位置和名字都不同，需要根据自己的操作系统来查找这个文件：

  • Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • Linux: /etc/my.cnf
  • macOS:
    • dmg安装: /etc/my.cnf
    • homebrew安装:
      • Intel CPU: /usr/local/homebrew/etc/my.cnf
      • AppleSilicon CPU: /opt/homebrew/etc/my.cnf
# 在[mysqld]的下方添加或修改如下属性:
# 服务节点的唯一标识，需要给集群中的每个服务分配一个单独的ID
server-id=102
# 打开binlog日志，并指定文件名
log_bin=master-102-bin
# binlog日志文件
log_bin-index=master-102-bin.index
# 打开relaylog日志
relay_log=master-102-relay-bin
relay_log-index=master-102-relay-bin.index
skip-slave-start
# 防止两个主库中同时操作自增的字段导致字段冲突
auto_increment_increment=2	# 自增步长，一般有几个MySQL就设置为几
auto_increment_offset=2		# 自增起始值

修改完成之后，需要重启MySQL服务。

为root用户分配replication slave的权限：

# 登录到主库
mysql -uroot -p

# 为root用户分配权限
# MySQL8中，需要先添加 'root'@'%' 这个用户
# create user 'root'@'%' identified by '123456'
mysql> grant replication slave on *.* to 'root'@'%';
mysql> flush privileges;
16.3.4 设置同步

  • 查看master1的binlog

    +-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| master-101-bin.000001 |      156 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+

  • 设置master2同步master1

    # 登录从库
mysql -uroot -p
# 设置同步主节点
change master to
master_host='192.168.10.101',				
master_port=3306,							
master_user='root',					 		
master_password='123456',			 		
master_log_file='master-101-bin.000001', 	
master_log_pos=156;

  • 查看master2的binlog

    +-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| master-102-bin.000001 |      553 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+

  • 设置master1同步master2

    # 登录从库
mysql -uroot -p
# 设置同步主节点
change master to
master_host='192.168.10.102',				
master_port=3306,							
master_user='root',					 		
master_password='123456',			 		
master_log_file='master-102-bin.000001', 	
master_log_pos=553;

  • 开启同步

    # 分别启动两个数据库的slave
start slave

# 如果出现错误：Slave failed to initialize relay log info structure from the repository
# 说明之前存在主从模式下的relay log，使用reset slave命令清除即可

参考链接:https://blog.csdn.net/u010839779/article/details/128012245?spm=1001.2014.3001.5501

大数据东哥(Aidon)
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
python-mysql day03.txt
04-27
Day02回顾 1、数据类型 1、数值类型 2、字符类型 3、枚举类型 1、单选 ：enum(值1,...) 2、多选 ：set(值1,值2,...) ## "MySQL,Python,Study" 4、日期时间类型 1、date 2、time 3、datetime # 不给值默认返回NULL 4、timestamp # 不给值默认返回系统当前时间 2、日期时间函数 1、NOW() 2、CURDATE() 3、CURTIME() 4、YEAR(字段名) 5、DATE(字段名) 6、TIME(字段名) 7、日期时间运算 select ... from 表名 where 字段名 运算符(时间-interval 时间间隔单位) 1 day | 2 day | 3 hour 3、表字段操作 1、添加(add) alter table 表名 add 字段名 数据类型 first|after.; 2、修改(modify) alter tab
python-mysql day05.txt
04-27
Day04回顾 1、外键 1、使用规则 1、数据类型要一致 2、主表被参考字段必须为KEY的一种 ：PRI 2、语法格式 constraint 外键名 foreign key(字段) references 主表(字段) on delete 级联动作 on update 级联动作 3、级联动作 1、cascade ：删除、更新同步(被参考字段) 2、restrict ：不让主表更新、删除 3、set null ：删除、更新,从表该字段设置为 NULL 4、删除 1、show create table 表名; 2、alter table 表名 drop foreign key 外键名; 5、已有表 alter table 表名 add constraint 外键名 foreign key(字段) references 主表(字段) on delete ... on update ... 6、注意 在已有表中添加外键时,会受到表中原有数据的
day01-项目概述、环境搭建
11-03
day01-项目概述、环境搭建
day05-Elasticsearch01
12-13
day05-Elasticsearch01
mysql中的双主模式
荣山的博客
11-30 2463
mysql中的双主模式
mysql 双主架构详解
悟已往之不谏，知来者之可追
04-02 2074
一、背景 二、MySQL双主（主主）架构方案 三、MySQL双主架构图 四、MySQL双主架构的优缺点 五、MySQL双主架构，会存在什么问题？
Mysql 数据库 主从数据库 (主从)(主主)
热门推荐
qq_53840970的博客
05-12 1万+
一、什么是主从数据库 主从数据库就是把数据库架构分为主数据库和从数据库。从数据库是主数据库的备份，以提高数据的安全性，不至于一个数据库崩掉而导致整个项目也运行不起来。可以有效的防止财产的损失。一般从库只做查询操作，而主库则做更改操作，也就是所谓的读写分离。 二、什么是主主数据库 所谓的主主，其实也就是两个数据库互为主从。两个库都可以对数据库进行更改和读写操作，个人感觉是主从数据库的进化版，更加的实用，但是不足之处就是，如果有自增主键的话，很大可以会产生主键冲突，所以就需要一定的算法进行改进。 三
mysql 主主模式（keepalived）
luzhuhong1的博客
09-20 3643
mysql 主主模式（keepalived） 服务器2台 214 和213 1.安装mysql 可以参考web环境安装那个博客https://blog.csdn.net/luzhuhong1/article/details/82769344 本文主要介绍mysql主主模式配置 2.mysql主主配置 2.1配置文件（my.cnf） 配置my.cnf文件（先检查cd /u01/mysql-5.6.2...
实战 MySQL 高可用架构
dyuan134的博客
04-17 335
对于 MySQL 数据库作为各个业务系统的存储介质，在系统中承担着非常重要的职责，如果数据库崩了，那么对于读和写数据库的操作都会受到影响。如果不能迅速恢复，对业务的影响是非常大的。之前 B 站不是出过一次事故么，2 小时才恢复过来，详细可以看之前写的文章。B 站崩了，总结下「高可用」和「异地多活」上次折腾完 ELK 日志检索平台后，开发环境可以正常查询日志了。最近在做系统高可用相关的工作，这次我来分享下 MySQL 双主 + Keepalived 的高可用落地和踩坑之路。一文带你搭建一套 ELK Stack
mysql主主架构配置
weixin_53391875的博客
08-22 487
MySQL主主同步存在数据同步延迟的问题，即在数据同步过程中，可能会出现数据不一致的情况。：MySQL主主同步可以将多个MySQL服务器之间的数据同步，当其中一个服务器出现故障时，其他服务器可以继续提供服务，从而提高了数据的可用性。：MySQL主主同步可以实现数据的备份和复制，当其中一个服务器出现数据丢失或损坏时，其他服务器可以提供备份数据，从而增强了数据的安全性。：MySQL主主同步可能会降低数据库的性能，特别是在数据同步过程中，可能会占用过多的系统资源，从而影响数据库的性能。
day03-菜品管理 day03-菜品管理
11-03
day03-菜品管理
day05-Elasticsearch01day05-Elasticsearch01
10-25
day05-Elasticsearch01day05-Elasticsearch01
day05-店铺营业状态设置
11-03
day05-店铺营业状态设置
『MySQL 实战 45 讲』20 - 幻读是什么，幻读有什么问题？
最新发布
Gavinjou大笨象的博客
05-04 975
3. 当执行 select * from t where d=5 for update 的时候，6 个记录加上了行锁，还同时加了 7 个间隙锁，这样就确保无法再插入新的记录。
MySQL —— 表的基本操作
学习C++的小陈同学
04-30 476
本篇整理了和表结构相关的各种基本操作（增删查改等等）
【MySQL】1.安装与配置
2301_79719531的博客
04-28 854
文件的安全性问题。文件不利于数据查询和管理。文件不利于存储海量数据。文件在程序中控制不方便。数据库存储介质：磁盘+内存。为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，他能更加有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。
Debian 德比安 Nginx + PHP + MySql + beanstalkd + Redis + Node.js
HzqGhost's Blog
04-29 591
网卡模式选择桥接 mirrors.163.com阿里镜像源DeBian 安装软件选择时勾选上apt updateapt install sudo #安装 sudousermod -aG sudo username #添加普通账号到 sudo让 root 可以 SSH配置文件 /etc/ssh/sshd_config找到 PermitRootLogin 选项 并将其值修改为重启ssh先在 VirtualBox 上添加好。
MySQL的数据备份和恢复
本科在读，致力于Linux/C++方向的学习，同时涉及数据结构与算法，操作系统，数据库mysql等等
04-28 679
本博客主要内容涉及到mysql数据库的备份和恢复
基于springboot+vue+Mysql的生鲜交易系统
老卓爱爪哇 的博客
04-29 1056
理员使用本系统涉到的功能主要有：首页，个人中心，用户管理，商家管理，生鲜信息管理，生鲜分类管理，广告信息管理，生鲜仓库管理，生鲜出库管理，管理等功能。商家使用本系统涉到的功能主要有：首页，个人中心，生鲜信息管理，广告信息管理，生鲜仓库管理，生鲜出库管理，订单管理等功能。用户主要包括首页、个人中心功能。
day03-手动组建fabric网络.pdf
08-01
《day03-手动组建fabric网络.pdf》是一份指导手册，用于教授如何手动组建Fabric网络。Fabric网络是一个分布式账本技术，可用于构建区块链解决方案。这个手册详细介绍了在组建Fabric网络时的步骤和注意事项。 首先，手动组建一个Fabric网络需要一些技术基础。手册在开始部分介绍了一些必备的知识，例如区块链和分布式账本的基本概念。学习者需要理解这些基础概念，以便更好地理解后续的内容。 手册还提供了一些实践操作的步骤。首先，需要准备网络中的各个组件，例如Peer节点、Orderer节点和Channel。手册详细介绍了如何配置这些组件，并给出了一些示例。 接下来，手册重点介绍了如何连接这些组件，以构建一个完整的Fabric网络。手册详细介绍了如何配置节点之间的通信，如何创建和加入Channel，以及如何运行智能合约。这些步骤是手动组建Fabric网络的核心内容，学习者需要仔细理解和熟悉。 除了步骤之外，手册还提供了一些注意事项和最佳实践。例如，手册强调了网络的安全性和可扩展性，提供了一些建议和建议，帮助学习者更好地设计和管理Fabric网络。 总之，《day03-手动组建fabric网络.pdf》是一份非常实用的手册，适合想要了解如何手动组建Fabric网络的人士。通过学习这个手册，学习者可以获得丰富的知识和实践经验，从而能够独立地组建和管理自己的Fabric网络。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

大数据东哥(Aidon)

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值