当前位置： 首页 > news >正文

MySQL--组从复制的详解及功能演练

news 2025/8/2 6:31:48

2.MySQL的组从复制

2.1 配置mastesr

[root@mysqlaa ~]# vim /etc/my.cnf
[mysqld]
server-id=10
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
log-bin=mysql-bin[root@mysqlaa ~]# /etc/init.d/mysqld restart

# 进入数据库配置用户权限
[root@mysql-node10 ~]# mysql -uroot -p123456# 生成专门用来做复制的用户，此用户是用于slave端做认证用
mysql> create user dhj@'%' identified by '123456';				
mysql> grant replication slave on *.* to 'dhj'@'%';				# 对这个用户进行授权
mysql> show master status;										# 查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      658 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
**image-20250801141359365** 
[root@mysql-node10 ~]# cd /data/mysql/
[root@mysql-node10 mysql]# mysqlbinlog mysql-bin.000001	-vv			# 查看二进制日志

2.2 配置salve

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password[root@mysql-node2 ~]# /etc/init.d/mysqld restart

[root@mysqlb ~]# mysql -uroot -p123456mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='dhj',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=658;						# 这里要去master主机中去查看一遍mysql> start slave;mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.25.254.10Master_User: dhjMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1606Relay_Log_File: mysqlb-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes						# 一定要保证此参数为yesSlave_SQL_Running: Yes						# 一定要保证此参数为yes
image-20250801152150489 
# 如果上述内容输入错误可以reset重新填入信息即可
mysql> RESET SLAVE ALL;

# 测试：# 在master主机里面进行建表
[root@mysqlaa ~]# mysql -uroot -p123456mysql> create database ceshi;mysql> create table ceshi.userlist (username varchar(20) not null, password varchar(50) not null);mysql> insert into ceshi.userlist value ('dhj','123');mysql> select * from ceshi.userlist;
+----------+----------+
| username | password |
+----------+----------+
| dhj      | 123      |
+----------+----------+# 在slave中查看数据是否有同步过来
[root@mysqlb ~]# mysql -uroot -p123456mysql> select * from ceshi.userlist;
+----------+----------+
| username | password |
+----------+----------+
| dhj      | 123      |
+----------+----------+
image-20250801152710614

image-20250801152852953

2.3 当有数据时添加slave2

#完成基础配置
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
server-id=30
datadir=/data/mysql                                     # 指定数据目录
socket=/data/mysql/mysql.sock           				# 指定套接字
default_authentication_plugin=mysql_native_password[root@mysql-node3 ~]# /etc/init.d/mysqld restart

#从master节点备份数据
[root@mysql-node1 ~]# mysqldump -uroot -p123456 ceshi  > /mnt/ceshi.sql

[!NOTE]

生产环境中备份时需要锁表，保证备份前后的数据一致

mysql> FLUSH TABLES WITH READ LOCK;

备份后再解锁

mysql> UNLOCK TABLES;

mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

--
-- Table structure for table `userlist`
--DROP TABLE IF EXISTS `userlist`;		#需要合并数据时需要删除此语句
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

[root@mysqlaa ~]# scp /mnt/ceshi.sql root@172.25.254.30:/mnt
root@172.25.254.30's password:
ceshi.sql                           100% 1947     2.2MB/s   00:00# 利用master节点中备份出来的lee.sql在slave2中拉平数据
[root@mysql-node3 ~]# mysql -uroot -p123456 -e "create database ceshi;"
[root@mysql-node3 ~]# mysql -uroot -p123456 ceshi </mnt/ceshi.sql
[root@mysql-node3 ~]# mysql -uroot -p123456 -e "select * from ceshi.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
+----------+----------+

#配置slave2的slave功能#在master中查询日志pos
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     3656 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+[root@mysqlc ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='dhj', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3656;mysql> start slave;
mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.25.254.10Master_User: dhjMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 3656Relay_Log_File: mysqlc-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes

测试：

[root@mysql-node1 ~]# mysql -uroot -p123456 -e  "INSERT INTO ceshi.userlist VALUES('user2','123');"[root@mysql-node2 mysql]# mysql -uroot -p123456 -e 'select * from ceshi.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
+----------+----------+[root@mysql-node3 ~]# mysql -uroot -p123456 -e 'select * from ceshi.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
+----------+----------+

# 为slave两台主机开启只读操作+超级只读操作（root在slave里面都不能写数据）
# 以下仅为20主机的，30的在此不做演示
[root@mysqlb ~]# more /etc/my.cnf
[mysqld]
server-id=10
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
read-only=1
super-read-only=1[root@mysqlb ~]# /etc/init.d/mysqld restart

image-20250801164929743

2.4 延迟复制

延迟复制时用来控制sql线程的，和i/o线程无关

这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

是日志已经保存在slave端了，那个sql要等多久进行回放

#在slave端
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY=60;
mysql> START SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS\G;Master_Server_Id: 1Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355eaMaster_Info_File: /data/mysql/master.infoSQL_Delay: 60			##延迟效果SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400

测试：

在master中写入数据后过了延迟时间才能被查询到

2.5 慢查询日志

  • 慢查询，顾名思义，执行很慢的查询

  • 当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的

  • 慢查询被记录在慢查询日志里

  • 慢查询日志默认是不开启的

  • 如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

mysql> SHOW variables  like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)

开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES like "long%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)mysql> SHOW VARIABLES like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |		##慢查询日志开启
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)[root@mysql-node1 ~]# cat  /data/mysql/mysql-node1-slow.log     #慢查询日志
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument

测试慢查询

mysql> select sleep (10);[root@mysql-node1 ~]# cat  /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-07-29T17:04:07.612704Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 10.000773  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1722272647;
select sleep (10);

2.6 mysql的并行复制

查看slave中的线程信息

image-20250730200425000

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

开启MySQL的多线程回放可以解决上述问题

# 在slaves中设定
# 以下仅为20,30不做演示[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
datadir=/data/mysql
socket=/data/mysql/mysql.sock
default_authentication_plugin=mysql_native_password
read-only=1
super-read-only=1slave-parallel-type=LOGICAL_CLOCK			#基于组提交，
slave-parallel-workers=16					#开启线程数量
master_info_repository=TABLE				#master信息在表中记录，默认记录在/data/mysql//master.info
relay_log_info_repository=TABLE				#回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON						#日志回放恢复功能开启[root@mysql-node2 ~]# /etc/init.d/mysql restart# 进行测试
mysql> show processlist;

image-20250801165536603

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

[!NOTE]

MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。

2.7 原理刨析

image-20250730195631273 image-20250801194633466

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。

  • 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

  • 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

  • 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作

1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息

2.master端在设定好binlog启动后会开启binlog dump的线程

3.master端的binlog dump把二进制的更新发送到slave端的

4.slave端开启两个线程，一个是I/O线程，一个是sql线程，

  • i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘
  • sql线程读取本地relog中继日志进行回放

5.什么时候我们需要多个slave？

当读取的而操作远远高与写操作时。我们采用一主多从架构

数据库外层接入负载均衡层并搭配高可用机制

2.8 架构缺陷

主从架构采用的是异步机制

master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测

master端直接保存二进制日志到磁盘

当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave

master出现问题slave端接管master，这个过程中数据就丢失了

这样的问题出现就无法达到数据的强一致性，零数据丢失

http://www.lryc.cn/news/606829.html

相关文章：

  • 从O(n²)到O(n log n)：深度剖析快速排序的内存优化与cache-friendly实现
  • 高级11-Java日志管理：使用Log4j与SLF4J
  • Oracle EBS 缺少adcfgclone.pl文件
  • 电商前端Nginx访问日志收集分析实战
  • 汇川ITS7100E触摸屏交互界面开发（一）调试事项说明
  • 25电赛e题杂乱环境稳定识别矩形框（附源码）
  • Vue3 Vue3中的响应式原理
  • StarRocks vs. Trino
  • 九联UNT403HS_海思MV320处理器_安卓9-优盘强刷刷机包
  • 嵌入式 Linux 深度解析：架构、原理与工程实践（增强版）
  • 企业级LLM智能引擎 的完整解决方案，整合了 SpringAI框架、RAG技术、模型控制平台(MCP)和实时搜索，提供从架构设计到代码实现的全面指南：
  • cloudflare worker + Cloudflare AI Gateway
  • 如何在不依赖 Office 的情况下转换 PDF 为可编辑文档
  • python中appium
  • K8S周期性备份etcd数据实战案例
  • 精通分类：解析Scikit-learn中的KNN、朴素贝叶斯与决策树（含随机森林）
  • 应用药品注册证识别技术，为医药行业的合规、高效与创新发展提供核心驱动力
  • 智能图书馆管理系统开发实战系列（四）：后端C++ DLL开发与模块化设计
  • Dify版本升级实操
  • 体育直播系统搭建：核心数据详细接入指南
  • 网络编程-加密算法
  • CCleaner是否被过誉了？C盘满了怎么办？用ccleaner清除C盘垃圾，3款电脑系统磁盘清理和优化软件
  • 应用Builder模式在C++中进行复杂对象构建
  • Vue与Ajax快速入门
  • GXP6040K压力传感器可应用于医疗/汽车/家电
  • Python Flask框架Web应用开发完全教程
  • 【stm32】GPIO
  • 第3章 AB实验的统计学知识
  • 从Web2.0到Web3.0：社交参与方式的重塑与延伸
  • 报错[Vue warn]: Failed to resolve directive: else如何解决？