MySQL主从复制之原理&一主一从部署流程—2023.04

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: MySQL主从复制之原理&一主一从部署流程—2023.04

一、MySQL主从复制介绍


1、主从复制概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL主从复制是一个异步的复制过程,底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库(slave,即从库)从另一台MySQL数据库(master,即主库)进行日志的复制,然后再解析日志并应用到自身,最终实现从库的数据和主库的数据保持一致。


2、为什么要做主从复制

  1. 在业务复杂的系统中,有这么一个情景,有一句sql语句需要锁表,导致暂时不能使用读的服务,那么就很影响运行中的业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。
  2. 做数据的热备
  3. 架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。


3、主从复制原理

  • 原理
  1. master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;
  2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件;
  3. 同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志(relay-log)中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。

e39aac7afbab418f8163b11415d462b3.png


也就是说:

从库会生成两个线程,一个I/O线程,一个SQL线程;

I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

主库会生成一个log dump线程,用来给从库I/O线程传binlog;

SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;


具体步骤:

从库通过手工执行change master to 语句连接主库,提供了连接的用户一切条件(user 、password、port、ip),并且让从库知道,二进制日志的起点位置(file名 position 号); start slave

从库的IO线程和主库的dump线程建立连接。

从库根据change master to 语句提供的file名和position号,IO线程向主库发起binlog的请求。

主库dump线程根据从库的请求,将本地binlog以events的方式发给从库IO线程。

从库IO线程接收binlog events,并存放到本地relay-log中,传送过来的信息,会记录到master.info中

从库SQL线程应用relay-log,并且把应用过的记录到relay-log.info中,默认情况下,已经应用过的relay 会自动被清理purge


主从同步事件binlog模式有3种形式:statement、row、mixed。

statement: 会将对数据库操作的 sql 语句写入到 binlog 中。

row: 会将每一条数据的变化写入到 binlog 中。

mixed: statement 与 row 的混合。MySQL 决定什么时候写 statement 格式的,什么时候写 row 格式的 binlog。


注意:

①master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。

②slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。

③Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。

④Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)

⑤master和slave两节点间时间需同步


4、主从复制形式

  1. 一主多从,提高系统的读性能

c7fddca8ef78406e88fd16c60c308811.png

一主一从和一主多从是最常见的主从架构,实施起来简单并且有效,不仅可以实现HA,而且还能读写分离,进而提升集群的并发能力。

2、多主一从 (从5.7开始支持)

82e905e8d41547dd8b7590fe503d2f18.png

多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

3、双主复制
双主复制,也就是互做主从复制,每个master(主)既是master,又是另外一台服务器的slave(从)。这样任何一方所做的变更,都会通过复制应用到另外一方的数据库中。

4、级联复制

071d2a237d7b45e7a0b1b786ae50e9f8.png

级联复制模式下,部分slave的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用于replication(复制),那么我们可以让3~5个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。级联复制下从节点也要开启binary log(bin-log)功能。


5、主从复制主要用途

1、读写分离

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

2、数据实时备份,当系统中某个节点发生故障时,可以方便的故障切换(主从切换)

提高数据安全-因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据;

3、高可用(HA)

1)因为数据库服务器中的数据都是相同的,当Master挂掉后,可以指定一台Slave充当Master继续保证服务的运行,因为数据是一致性的(如果当插入时Master就挂掉,可能不一致,因为同步也需要时间)当然这种配置不是简单的把一台Slave充当Master,毕竟还要考虑后续的Slave的数据同步到Master

2)在主服务器上执行写入和更新,在从服务器上向外提供读功能,达到读写分离的效果,也可以动态地调整从服务器的数量,从而调整整个数据库的性能。

3)在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能。

4、架构扩展

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


二、MySQL一主一从搭建


1、准备工作

服务器 IP 要求
master 192.168.2.31 Docker、MySQL5.7
slave 192.168.2.32 Docker、MySQL5.7


首先在两台服务器上提前部署好docker以及MySQL5.7版本。

MySQL安装流程参考这篇文章:https://blog.csdn.net/weixin_64124795/article/details/129110551


2、MySQL主从复制部署流程

1. 关闭防火墙,两台主机都操作
systemctl stop firewalld  关闭防火墙
systemctl disable firewalld 关闭开机自启


2. 修改配置文件,两台主机都操作

mysql配置文件路径:/etc/my.cnf

增加如下内容:

# MySQL 服务的唯一编号,每个 MySQL 服务的 id 需唯一
server-id = 1
# 服务端口号,默认为 3306
port = 3306
# 是否对sql语句大小写敏感,默认值为0,1表示不敏感
lower_case_table_names=1    #1代表关闭大小写区别 0代表开启大小写
default_storage_engine=InnoDB   #存储引擎  lnnoDB
# 配置默认编码为utf8
character_set_server=utf8
init_connect='SET NAMES utf8'


最终配置文件内容:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# MySQL 服务的唯一编号,每个 MySQL 服务的 id 需唯一
server-id = 1
# 服务端口号,默认为 3306
port = 3306
# 是否对sql语句大小写敏感,默认值为0,1表示不敏感
lower_case_table_names=1    #1代表关闭大小写区别 0代表开启大小写
#存储引擎  lnnoDB
default_storage_engine=InnoDB   
# 配置默认编码为utf8
character_set_server=utf8
init_connect='SET NAMES utf8'


重启mysql使配置生效

systemctl restart mysqld


默认配置文件路径:

配置文件:/etc/my.cnf

日志文件:/var/log/mysqld.log

服务启动脚本:/usr/lib/systemd/system/mysqld.service

socket文件:/var/lib/mysql/mysql.sock


3. 修改主节点配置文件,主节点操作

vim /etc/my.cnf添加以下参数:

log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=test #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-ignore-db=mysql #禁止同步 mysql 数据库

8eaf20207576417f9cab7eced1b24571.png

# 重启mysql
systemctl restart mysqld
  • 配置主服务器用户并清空日志
    进入MySQL
mysql -uroot -p


mysql> show privileges;
# 创建远程登录账户
## 注意这里的密码不能设置的太简单,否则会报错 ERROR 1819 (HY000): Unknown error 1819
mysql> grant replication client,replication slave on *.* to 'tongwz'@'192.168.2.%' identified by 'Tongwz123.';
mysql> reset master;
mysql> show binary logs;

99f0504e1d0944949651797370e3d9cd.png

4. 修改从节点配置文件,从节点操作

/etc/my.cnf中的server-id 改为2, 注意:server-id不可跟主服务器的一样

另外,加入下面一行命令,启用中继日志

#启用中继日志
relay-log=mysql-relay

582d658d57534a95b712f5ae7ea79961.png

# 重启mysql
systemctl restart mysqld

进入MySQL:

mysql -uroot -p


执行如下命令:

mysql> change master to master_host='192.168.2.31', master_user='tongwz', master_password='Tongwz123.', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154;
说明:
# change master to master_host=‘主服务器ip’,
# master_user=‘刚在主服务器创建的用户名’,
# master_password=‘刚在主服务器创建的用户密码’, 
# master_port=3306, 
# master_log_file=‘刚查看主服务器下的File值’,
# master_log_pos=刚查看主服务器下的Position值,必须要一致


先查看slave状态:红框中显示是No,由于slave还没有开启,表示还没有加入主节点
mysql> show slave status \G;

50b4badf7d744cf0a1ce9d0751a41749.png

正式开启slave:
mysql> start slave;
再次查看状态:此时已经显示为Yes了
mysql> show slave status \G;

63830b998f4c47abb4bd301eba284007.png

注意:两个必须都显示为Yes才表示成功。如果还是显示No,就要重新执行show binary logs;命令查看master节点aster_log_pos值,再执行change master to master_host='192.168.2.31', master_user='tongwz', master_password='Tongwz123.', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154;这段命令,然后start slave;

此时,slave已经配置好,进一步查看:

# 查看relay-log.info文件位置,进入查看
find / -name relay-log.info
cat /var/lib/mysql/relay-log.inf
# 查看master.info文件位置,进入查看
find / -name master.info
cat /var/lib/mysql/master.info

5051452400ed4493a193dd9ca72437f8.png

33209d52a637483b9dfba02f14a3b8d1.png

5. 测试

在master节点中数据库test里创建表test,插入数据;在slave节点查看数据是否同步。

因为前面配置文件中,写明了只同步test数据库。

master节点中创建:
mysql> use test;
mysql> create table test(id int(11));
mysql> insert into test values(1);
mysql> select * from test;


slave节点中查看:
mysql> use test;
mysql> show tables;
mysql> select * from test;

bd2e7a3ec27b44f5950376d2a2a09c52.png

可以看到在master节点上加入的数据,这就说明主从复制成功。


6. 特别说明
  1. 以上这种方式搭建的一主一从,主要实现读写分离,master节点上写入数据会自动同步到slave节点。如果反过来,在slave节点写入数据,是无法同步到master节点上的,反而还会中断主节点与从节点的连接。所以为了避免这个问题,一般只给slave节点只读的权限。


增加同步的数据库到slave
  1. 上面的操作我们只同步了test数据库,如果我们想要再同步一个数据库到slave节点,该怎么操作呢?

首先,我们要修改master节点的配置文件vim /etc/my.cnf,里面增加数据库test1
master节点操作:

vim /etc/my.cnf
binlog-do-db=test  #二进制需要同步的数据库名,如果需要同步多个库,例如要再同步westos库,再添加一行“binlog-do-db=westos”,以此类推
binlog-do-db=test1   # 增加这一行


然后重启

systemctl restart mysqld


然后,进入slave节点的mysql中,等待从节点同步主节点数据,待查看状态show slave status \G;后,两行都显示为Yes,表示已经连接成功;

其次,再master节点上的mysql中创建test1数据库,并往数据库内创建表,写入内容;

最后到slave节点查看是否同步即可。

注意:

必须先修改配置文件中的数据库名称,然后待slave与master连接后,再在master上创建test1数据库,顺序不能改变。

如果反过来,先在master节点上创建了test1数据库,然后修改配置文件、slave连接master,那么所创建的数据库就不会同步到slave。

所以,顺序很重要。

slave只同步连接master节点后,在master节点上执行的命令。


3、流程图

48bd0203d28d44f08f65c63985292124.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术,实现读写分离,提高系统性能和可用性。主服务器记录变更日志,从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
1月前
|
存储 SQL NoSQL
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1664 14
|
2月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
2月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
4天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
24天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
26天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
37 4
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
183 1