mysql主从复制,从搭建到使用

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: mysql主从复制,从搭建到使用

前言

数据库世界里有一种奇妙的舞蹈,它叫做主从复制。就像是一场华丽的芭蕾,主数据库和从数据库在节奏的指挥下,优美地协调着动作,完成着数据的同步传递。而今天,我们将一起走进这场舞台,探索MySQL主从复制技术的神奇之处。无论您是观众还是舞者,这都将是一场精彩绝伦的表演!

主从复制原理

MySQL的主从复制是一种数据库复制技术,用于在两个或多个MySQL服务器之间复制数据。它通常用于数据备份、读写分离、提高数据可用性和扩展性。以下是主从复制的工作原理:

Binlog 日志

  • 二进制日志(binlog):主服务器中的一个关键组件是二进制日志。二进制日志记录了所有更改数据的语句(如 INSERTUPDATEDELETE),以及DDL语句如 CREATE TABLEALTER TABLE。这些记录是按照它们发生的顺序来写入的,允许服务器在必要时重放这些事件。

复制线程

  • IO线程:从服务器上的IO线程连接到主服务器,并请求从当前的二进制日志文件位置开始复制数据。主服务器接收到这个请求后,通过IO线程发送二进制日志的内容到从服务器。
  • SQL线程:接收到二进制日志内容后,从服务器将这些内容写入自己的中继日志(relay log)。从服务器上的SQL线程会读取中继日志,并在本地执行这些日志记录的SQL语句,从而使得从服务器的数据库与主服务器的数据库保持同步。

数据同步流程

  1. 更改数据:在主服务器上执行写操作(如 INSERTUPDATEDELETE)。
  2. 写入binlog:主服务器将这些更改写入二进制日志。
  3. 从服务器请求数据:从服务器上的IO线程请求并获取主服务器上的二进制日志。
  4. 发送binlog到从服务器:主服务器发送二进制日志中的事件给从服务器。
  5. 写入中继日志:从服务器上的IO线程将接收到的事件写入中继日志。
  6. 执行SQL语句:从服务器上的SQL线程读取中继日志中的事件,并在从服务器上执行这些事件对应的SQL语句。

同步机制

  • 异步复制:在标准的主从复制中,数据的复制是异步进行的。这意味着主服务器在写入二进制日志后不会等待从服务器确认就继续处理其他操作,从服务器会在自己的节奏中读取和应用这些更改。
  • 半同步复制:MySQL还提供了半同步复制选项。在半同步复制中,主服务器在提交事务之后,会等待至少一个从服务器确认它已经接收到了二进制日志信息,然后才继续执行下一个事务。这可以保证数据在主服务器和至少一个从服务器之间的一致性。

主从复制的这种架构允许从服务器在出现故障时接管主服务器的角色,也允许在不影响主服务器性能的情况下,通过从服务器来处理读取请求,从而实现读写分离和负载均衡。

前置操作

创建一个数据库,一个数据表,这里就是演示,很简单的创建(主节点和从节点都需要创建)

-- 创建数据库 'acowbo'
CREATE DATABASE acowbo;
USE acowbo;
-- 创建表 'sys_user'
CREATE TABLE sys_user (
  Id INT PRIMARY KEY,
  username VARCHAR(255),
  password VARCHAR(255),
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  create_by VARCHAR(255),
  deleted TINYINT(1) DEFAULT 0
);

创建一个用户acowbo,具有这个数据库的所有执行权限

-- 创建用户 'acowbo' 并设置密码
CREATE USER 'acowbo'@'%' IDENTIFIED BY 'Test123Ok!';
-- 赋予 'acowbo' 用户对 'acowbo' 数据库的所有权限
GRANT ALL PRIVILEGES ON acowbo.* TO 'acowbo'@'%';
-- 更新权限
FLUSH PRIVILEGES;

配置主服务器

创建主从账号,在master节点

CREATE USER 'replicator'@'%' IDENTIFIED BY 'Test123Ok!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

删除操作

-- 删除用户及其权限
DROP USER 'username'@'host';
-- 如果需要,取消用户对特定数据库的所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
-- 如果需要,取消用户对特定表的所有权限
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';
-- 如果需要,取消用户对特定权限的权限
REVOKE privilege_type ON database_name.* FROM 'username'@'host';

修改master节点的配置文件

[mysqld]
server-id = 1                  # 主服务器的唯一ID
log_bin = /var/log/mysql/mysql-bin.log   # 启用二进制日志
binlog_do_db = acowbo          # 指定需要复制的数据库

重启service mysql restart

配置从服务器

修改slave的节点配置文件

[mysqld]
server-id = 2                  # 从服务器的唯一ID,必须与主服务器不同
relay_log = /var/log/mysql/mysql-relay-bin.log   # 启用中继日志
binlog_do_db = acowbo          # 指定需要复制的数据库
read_only = 1                  # 设置为只读模式,以防止直接在从服务器上修改数据

执行主从同步

主节点操作

在主节点执行SHOW MASTER STATUS;,记住file的值与position的值,等等要在slave用到

从节点操作

从节点执行以下命令

CHANGE MASTER TO
  MASTER_HOST='ubtone.local',
  MASTER_USER='replicator',
  MASTER_PASSWORD='Test123Ok!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=157;    
# 启动从节点
START SLAVE;
# 查看状态
SHOW SLAVE STATUS\G;

配置成功展示

出现下面的即证明成功了

验证,首先我们验证一下slave节点只读,插入数据报错如下

验证主从

主库模拟插入,从库查看是否同步成功

# 主库执行以下命令
INSERT INTO `sys_user` (`Id`, `username`, `password`, `create_time`, `create_by`, `deleted`) 
VALUES 
(1, 'user1', 'password1', '2024-04-24 10:00:00', 'admin', 0),
(2, 'user2', 'password2', '2024-04-24 10:15:00', 'admin', 0),
(3, 'user3', 'password3', '2024-04-24 10:30:00', 'admin', 0);

结果显示

主库
mysql> use acowbo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO `sys_user` (`Id`, `username`, `password`, `create_time`, `create_by`, `deleted`)  
    -> VALUES 
    -> (1, 'user1', 'password1', '2024-04-24 10:00:00', 'admin', 0),
    -> (2, 'user2', 'password2', '2024-04-24 10:15:00', 'admin', 0),
    -> (3, 'user3', 'password3', '2024-04-24 10:30:00', 'admin', 0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> 
从库
mysql> use acowbo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from sys_user;
+----+----------+-----------+---------------------+-----------+---------+
| Id | username | password  | create_time         | create_by | deleted |
+----+----------+-----------+---------------------+-----------+---------+
|  1 | user1    | password1 | 2024-04-24 10:00:00 | admin     |       0 |
|  2 | user2    | password2 | 2024-04-24 10:15:00 | admin     |       0 |
|  3 | user3    | password3 | 2024-04-24 10:30:00 | admin     |       0 |
+----+----------+-----------+---------------------+-----------+---------+
3 rows in set (0.00 sec)
mysql> 


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
MySQL主从复制
MySQL主从复制
55 0
|
2月前
|
关系型数据库 MySQL Java
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
57 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL中主从复制的原理和配置命令
要原因包括提高性能、实现高可用性、数据备份和灾难恢复。了解两大线程( I/O 和 SQL)I/O线程:目的:I/O线程主要负责与MySQL服务器之外的其他MySQL服务器进行通信,以便复制(replication)数据。 功能: 当一个MySQL服务器作为主服务器(master)时,I/O线程会将变更日志(binary log)中的事件传输给从服务器(slave)。从服务器上的I/O线程负责接收主服务器的二进制日志,并将这些事件写入本地的中继日志(relay log)。 配置: 在MySQL配置文件中,你可以通过配置参数如和来启用二进制日志和指定服务器ID。log-bin server
MySQL中主从复制的原理和配置命令
|
2月前
|
负载均衡 容灾 关系型数据库
mysql主从复制
mysql主从复制
117 1
|
27天前
|
SQL 运维 关系型数据库
MySQL数据库运维第一篇(日志与主从复制)
MySQL数据库运维第一篇(日志与主从复制)
|
2月前
|
SQL 关系型数据库 MySQL
mysql 主从复制与读写分离
mysql 主从复制与读写分离
|
1月前
|
SQL 监控 关系型数据库
探秘MySQL主从复制的多种实现方式
探秘MySQL主从复制的多种实现方式
18 0
|
2月前
|
SQL 存储 运维
MySQL高可用性:主从复制和集群
MySQL高可用性:主从复制和集群
77 0
|
7天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
930 6
Mysql 数据库主从复制
|
28天前
|
存储 关系型数据库 MySQL
Java大佬必知必会——MySQL主从复制
如果你现在有两台MySQL,一台版本是03年的MySQL5.0,另一台是18年的MySQL8.0.11。新版本可以作为老版本的从服务器,但反过来是不可行的。如果二进制文件包含了已存在的数据,就会造成数据重复了。如果从服务器复制该二进制文件后的数据库状态是混乱无序的,那整个复制的过程就没有意义了。如果主、从服务器存储数据的顺序不一样,就会导致每次执行删除的数据都是不同的。,老版本可能无法解析新版本的新特性,甚至复制的文件格式都差异太大。MySQL从库只会复制它本身缺失的最新数据,利用二进制文件里的。
Java大佬必知必会——MySQL主从复制