MySQL数据库复制

ops/2025/3/14 21:24:56/

文章目录

  • MySQL数据库复制
    • 一、复制的原理
    • 二、复制的搭建
      • 1.编辑配置文件
      • 2.在主库上创建复制的用户
      • 3.获取主库的备份
      • 4.基于从库的恢复
      • 5.建立主从复制
      • 6.开启主从复制
      • 7.查看主从复制状态

MySQL数据库复制

MySQL作为非常流行的数据库，支撑它如此出彩的因素主要有两个，InnoDB存储引擎和复制。InnoDB存储引擎支持事务、行级别锁、MVCC。基于此，MySQL能实现高并发。而复制可以将主库的数据同步到另一个实例，基于此我们可以实现读写分离和数据容灾。

一、复制的原理

MySQL主从复制的核心原理是通过二进制日志（binlog）实现的，熟悉Oracle的同学会问：MySQL不是有redo log吗？为什么不直接基于redo log来实现呢？
实际上redo log是InnoDB存储引擎独有的，作为一个支持可插拨存储引擎的数据库，MySQL不仅支持InnoDB存储引擎，还支持MyISAM、CSV、Memory等存储引擎，对于这些存储引擎的操作同样需要持久化到binlog中。

在这里插入图片描述

MySQL的复制主要涉及以下3个线程：

  • 主库 binlog dump 线程
  • 从库 I/O 线程
  • 从库 SQL 线程

MySQL的复制其运作流程大致如下：

  1. 从库执行完START SLAVE命令后，会创建两个线程：I/O线程和SQL线程。
  2. I/O线程会建立一个到主库的连接，相应地，主库会创建一个binlog dump线程来响应这个连
  3. 接的请求。此时，对于主库来说，从库的1/O线程就是一个普通的客户端。
  4. I/O线程首先告诉主库应该从何处开始发送二进制日志事件。
  5. 主库的binlog dump线程开始从指定位置点读取二进制日志事件，并发送给I/O线程。
  6. I/O线程接收到二进制日志事件后，会将其写入relay log。
  7. SQL线程读取relay log中的二进制日志事件，然后进行重放。

二、复制的搭建

实验版本为 MySQL 8.0.41，部署MySQL异步复制，机器环境如下, 需要分别提前安装数据库软件以及初始化好数据库实例，可以参考我之前的《MySQL数据库安装》的文章

角色IP
主库192.168.50.121
从库192.168.50.122

1.编辑配置文件

主库编辑/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 1
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

从库/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 2
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

这里给出的只是搭建复制最简单的参数:

  • log-bin 开启 binlog , 这里的mysql-bin是binlog文件的前缀(8.0之后的版本可以不用配置该数,但会习惯性的进行设置)
  • server-id 是服务端ID, 在一个复制组内必需全局唯一,有效值为 1~2^32^-1

注意事项:

  • server-id 支持在线调整,但开启binlog 需要重启实例
  • 在MySQL8.0之前,binlog默认是关闭的,不显示设置log-bin参数,则不开启binlog。而在MySQL8.0中,binlog默认是开启的,如果要关才binlog,需要显示设置skip_log_bin 或 disable_log_bin。

2.在主库上创建复制的用户

在MySQL 8.0 之前可以直接使用第二第命令会隐式创建用户,但官方不推荐这种方式。

create user 'repl'@'192.168.50.122' identified by 'test123';grant replication slave on *.* to 'repl'@'192.168.50.122';

创建用户后在从库环境验证帐号是否能正常登陆

mysql -urepl -ptest123 -h 192.168.50.121

3.获取主库的备份

这里使用mysqldump工具进行备份,备份集通过scp命令拷贝到从库

mysqldump -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock --single-transaction --source-data=2 -E -R --triggers -A > full_backup.sqlscp  full_backup.sql 192.168.50.122:/data

从MySQL 8.0.26开始,开始使用 --source-data 代替之前版本的 --master-data 否则备份文件中会写入以下信息，影响到后续的导入。

WARNING:–master-data is deprecated and will be removed in a future version.use --source-data instead.

4.基于从库的恢复

在从库对备份文件进行导入

mysql -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock < /data/full_backup.sql

5.建立主从复制

在从库执行 CHANGE MASTER TO命令创建主从关系

change master to
master_host='192.168.50.121',
master_port=3306,
master_user='repl',
master_password='test123',
master_log_file='mysql-bing.000001',
master_log_pos=717;

参数具体含义：

参数作用
master_host主库的主机信息，可以为主机名或IP
master_port主库端口，若不指定则默认为3306
master_use复制的用户名
master_password复制用户的密码
master_log_file从库I/O线程启动时，应该从主库的哪个binlog由这个参数决定
master_log_posI/O线程确定binlog后,从哪个位置开始读取二进制日志事件.

我们在备份的时候通过 mysqldump 指定的 --source-data=2 (旧版本用--master-data=2) 他会将备份时的binlog位置信息记录到备份文件中,内容如下

--
-- Position to start replication or point-in-time recovery from
---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bing.000001', MASTER_LOG_POS=717;

当从库执行完CHANGE MASTER TO命令后，复制的相关信息会保存在两张表中：mysqL.slave_master.info和mysql.slave_relay_.log_info

mysql.slave_master_.info主要记录了两类信息：

  • 主库的连接信息，包括主库的IP、端口、复制用户和密码;
  • I/O线程读取的主库binlog的位置点信息。

注意，这个位置点不是实时更新的，它的更新频率与sync_master_.info参数有关。该参数默认为10000,即I/O线程每写入10000个事务会更新mysql.slave_.master_info一次。

mysql.slave_relay_log_info主要记录了以下信息：

  • SQL线程重放relay log的位置点信息。

事务每次提交时都会更新mysqL.slave._relay._log_info

这两张表是MySQL5.6引入的。在此之前复制的相关信息保存在master.info(对应mysql.slave_master.info表)和relay-log.info(对应mysql.slave_.relay_.log_info表)文件中，这两个文件默认位于从库的数据目录下。复制的相关信息是保存在文件还是系统表中由master_info_repository参数决定，其中，FILE代表文件，TABLE代表系统表。从MySQL8.0.2开始，该参数的默认值由FILE调整为TABLE。

6.开启主从复制

在从库上执行如下以下命令

mysql> start slave;

7.查看主从复制状态

重点关注 Slave_IO_Running和 Slave_SQL_Running 的结果均为YES代表主从复制搭建成功

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.50.121Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bing.000001Read_Master_Log_Pos: 717Relay_Log_File: mysql02-relay-bin.000003Relay_Log_Pos: 327Relay_Master_Log_File: mysql-bing.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 717Relay_Log_Space: 696Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: cdc9afd5-fcd8-11ef-9d33-000c29ea0d0aMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:1 row in set, 1 warning (0.00 sec)

在上面使用 SHOW SLAVE STATUS 命令时,提示了warning通过 show warnings 可以看到,SHOW SLAVE STATUS 已被弃用, 代用SHOW REPLICA STATUS替代。

  • 实际上在MySQL 8.0.22 开始 start slave 、stop slave 、show slave staus 、show slave hosts 、reset slave 命令都补弃用了,取而代之的是 start replica、stop rplica 、show replica status、show replicas、reset replica。
  • 从MySQL8.O.23开始，CHANGE MASTER TO命令被弃用，取而代之的是CHANGE REPLICATION SOURCE TO
  • 从MySQL8.0.26开始，标识符（如系统参数、状态变量）中的MASTER、SLAVE和MTS(multithreadedslave的缩写)将分别被SOURCE、REPLICA和MTA(multithreaded applier的缩写)替换，这一点需要注意。
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在主库上执行 show processlist 命令，可以看到 ID为23 连接对应的即是 binlog dump线程。

mysql> show processlist\G;*************************** 3. row ***************************Id: 23User: replHost: 192.168.50.122:52016db: NULL
Command: Binlog DumpTime: 152State: Source has sent all binlog to replica; waiting for more updatesInfo: NULL

在从库是执行 show processlist 命令, 5和6 的连接分别为 I/O线程和SQL线程

mysql>  show processlist\G;
*************************** 1. row ***************************Id: 5User: system userHost: connecting hostdb: NULL
Command: ConnectTime: 226State: Waiting for source to send eventInfo: NULL
*************************** 2. row ***************************Id: 6User: system userHost:db: NULL
Command: QueryTime: 226State: Replica has read all relay log; waiting for more updatesInfo: NULL

至此一个简单的异步复制环境搭建完毕。


http://www.ppmy.cn/ops/165767.html

相关文章

Stable Diffusion/DALL-E 3图像生成优化策略

Stable Diffusion/DALL-E 3图像生成优化策略

Stable Diffusion的最新版本或社区开发的插件&#xff0c;可以补充这些信息以保持内容的时效性。 云端源想 1. 硬件与部署优化&#xff08;进阶&#xff09; 显存压缩技术 使用--medvram或--lowvram启动参数&#xff08;Stable Diffusion WebUI&#xff09;&#xff0c;通过分…
阅读更多...
cesium地图设置3d,2d,2.5d动态切换

cesium地图设置3d,2d,2.5d动态切换

通过修改cesium实例vw的scene的显示模式&#xff0c;来切换最终的显示模式。 Cesium.SceneMode总共有四个变量值&#xff0c;分别如下&#xff1a;NameTypeDescriptionMORPHINGnumber在3d与2d之间切换变体 between mode, e.g., 3D to 2D.COLUMBUS_VIEWnumber2.5d模式&#xff0…
阅读更多...
# RAG 框架 # 一文入门 全链路RAG系统构建与优化 —— 架构、策略与实践

# RAG 框架 # 一文入门 全链路RAG系统构建与优化 —— 架构、策略与实践

本文全面阐述了RAG系统从数据收集、数据清洗&#xff08;包括领域专有名词处理&#xff09;、智能数据分块与QA对生成&#xff0c;到向量化、向量数据库选择与配置&#xff0c;再到检索方式及重排序&#xff0c;直至整合输出、监控反馈和安全保障的全流程。通过这一完整方案&am…
阅读更多...
makefile详解

makefile详解

1.背景介绍 linux中C语言可执行文件a.out的由来如下&#xff1a; test.c经过预编译到test.itest.i经过编译到test.stest.s经过汇编到test.otest.o经过链接到a.out 但是对于一个大型工程&#xff0c;每次生成新的可执行文件都要重新对每个文件(或者对修改了的文件)进行编译、…
阅读更多...
云原生性能测试全解析：如何构建高效稳定的现代应用？

云原生性能测试全解析：如何构建高效稳定的现代应用？

一、引言 随着云计算技术的快速发展&#xff0c;云原生&#xff08;Cloud Native&#xff09;架构成为现代应用开发的主流模式。云原生应用通常采用微服务架构、容器化部署&#xff0c;并利用 Kubernetes&#xff08;K8s&#xff09;等编排工具进行管理。然而&#xff0c;云原…
阅读更多...
在线Doc/Docx转换为PDF格式 超快速转换的一款办公软件 文档快速转换 在线转换免费转换办公软件

在线Doc/Docx转换为PDF格式 超快速转换的一款办公软件 文档快速转换 在线转换免费转换办公软件

小白工具https://www.xiaobaitool.net/files/word-pdf/提供了一项非常实用的在线服务——将Doc或Docx格式的文档快速转换为PDF格式。这项服务不仅操作简单&#xff0c;而且转换效率高&#xff0c;非常适合需要频繁处理文档转换的用户。 服务特点&#xff1a; 批量转换&#x…
阅读更多...
微服务Sentinel组件:服务保护详解

微服务Sentinel组件:服务保护详解

目录 服务保护简介 服务保护方案 安装与介绍Sentinel Sentinel整合微服务 服务保护实现 请求限流 线程隔离 OpenFeign整合Sentinel 配置线程隔离 服务熔断 编写降级逻辑 实现服务熔断 服务保护总结 服务保护简介 微服务保护是为了保障系统整体的稳定性和可靠性&am…
阅读更多...
【PyTorch教学】pytorch 基本语法

【PyTorch教学】pytorch 基本语法

文章目录 PyTorch tensor cheatsheet PyTorch tensor cheatsheet from: https://github.com/hkproj/torch_notes/blob/main/TensorOperations.ipynb Tensor creation / initialization Convert between Numpy and Torch Tensor math Matrix multiplication Batch matrix multip…
阅读更多...
最新文章

Copyright @ 2022~2023