MySQL【主从架构】

最新推荐文章于 2024-09-07 08:07:39 发布

散落在麦田的星

最新推荐文章于 2024-09-07 08:07:39 发布

阅读量799

点赞数 12

分类专栏： MySQL 文章标签： mysql 架构数据库

本文链接：https://blog.csdn.net/qq_43606536/article/details/140880602

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

MySQL 主从复制（Master-Slave Replication）是一种数据复制技术，用于在多个数据库服务器之间的数据同步。在主从复制架构中，一个服务器被设置为主服务器（Master），充当数据源，其他服务器被设置为从服务器（Slave），用来复制主服务器的数据。

主从复制的作用：

1. 高可用性：通过将主数据库的数据复制到一个或多个从数据库，可以在主数据库故障时快速切换到从数据库，以实现系统的高可用性和容错能力，从而保证系统的持续可用性。

2. 提高整体性能和吞吐量：通过将读请求分散到多个从服务器上进行处理，从而减轻了主服务器的负载压力，提高数据库系统的整体性能和吞吐量。主服务器主要负责写操作，而从服务器主要负责读操作，从而分担了主服务器的压力。

3. 数据备份和恢复：通过主从同步，可以将主服务器上的数据异步复制到从服务器上，从而实现数据备份和灾难恢复的需求。在应对意外数据丢失、灾难恢复或误操作时，可以使用从服务器作为数据的备份源来进行数据恢复。

总结：

解决数据可靠性的问题需要用到主从同步；

解决 MySQL 服务高可用要用到主从同步；

应对高并发的时候，还是要用到主从同步。

主从复制原理【重点】

步骤一：主库db的更新事件（增、删、改）被写到binlog
步骤二：从库IO线程发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到 中继日志.
步骤五：从库还会创建一个SQL线程，从 中继日志 里面读取内容，将更新内容写入到slave的db.

原理：

实现整个主从复制：Master开启 IO线程，Slave开启 IO线程 和 SQL线程

要实现主从复制，首先必须打开Master端的bin-log功能，因为整个MySQL复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行日志中所记录的各种操作。

===========================================

1. 在 Master 上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。当 Slave 连接到 Master 的后，Master 机器会为 Slave 开启 binlog dump线程,该线程会去读取bin-log日志；

2. Slave 连接到 Master 后，Slave 有一个I/O线程通过请求binlog dump线程读取bin-log日志，然后写入Slave 的relay log日志中。

3. Slave 还有一个 SQL线程，实时监控 relay-log日志内容是否有更新，解析文件中的SQL语句,在 Slave 数据库中去执行。

===========================================

master 负责写 -----A

slave relay-log -----B

I/O 负责通信读取binlog日志

SQL 负责写数据

主从同步的模式：

MySQL 中主要有以下三种主从复制的模式，分别是全同步复制、异步复制和半同步复制。

1. 全同步复制：在全同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待全部从服务器确认已接收到并应用了这些日志后才继续执行后续操作。

优点：数据一致性和可靠性，全部从服务器与主服务器保持同步。如果主服务器故障，应用到从服务器的数据不会丢失。
缺点：由于全同步复制需要等待从服务器的确认，因此相对于其他复制方式，延迟很大。严重影响主服务器的性能。

2. 异步复制：MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到二进制日志（Binary Log）中，并将日志传输给从服务器。从服务器接收到二进制日志后，会异步地应用这些日志进行数据复制。

优点：它的优点是及时响应给使用者，主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
缺点：由于是异步复制，可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。

3. 半同步复制：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才继续执行后续操作。

优点：可以提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
缺点：由于半同步复制需要等待从服务器的确认，因此相对于异步复制，会增加一定的延迟，可能会影响主服务器的性能。

如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制或者全同步；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。

M-S 架构GTID：基于事务ID复制

GTID 的全称是 Global Transaction Identifier，全局事务ID，当一个事务提交时，就会生成一个 GTID，相当于事务的唯一标识。

GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

GTID主从复制的工作原理

1. master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。

2. slave端的i/o 线程将变更的binlog，写入到本地的relay log中。

3. sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。

4. 如果有记录，说明该GTID的事务已经执行，slave会忽略。

5. 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

流程：

1.从库 B 指定主库 A，基于主备协议建立连接。

2.从库 B 把集合 y 发给主库 A。

3.主库 A 计算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1~100，集合 y 是 1~90，那么这个差集就是 91~100。这里会判断集合 x 是不是包含有集合 y 的所有 GTID，如果不是则说明主库 A 删除了从库 B 需要的 binlog，主库 A 直接返回错误。

4.主库 A 从自己的 binlog 文件里面，找到第一个不在集合 y 中的事务 GTID，也就是找到了 91。

5.主库 A 从 GTID = 91 的事务开始，往后读 binlog 文件，按顺序取 binlog，然后发给 B。

6.从库 B 的 I/O 线程读取 binlog 文件生成 relay log，SQL 线程解析 relay log，然后执行 SQL 语句。

注意：GTID 方案是通过主库来自动计算位点的，不需要人工去设置位点，对运维人员友好。

基于Gtid：主从复制（一主一从）

1.master和slave都操作：都安装了MySQL5.7

1. 关闭防火墙和selinux,两台机器环境必须一致,时间也得一致;改名：
systemctl disable --now firewalld
setenforce 0
hostnamectl set-hostname 新主机名
reboot

2. 所有主机配置本地解析
vim /etc/hosts
# 添加：
192.168.137.10 master01
192.168.137.50 slave01

3.测试连通性
ping 192.168.137.50

2.master01配置操作

1. 修改mysql配置文件
vim /etc/my.cnf
# [mysqld]下添加
server-id=1
log-bin=mysql
gtid_mode=ON
enforce_gtid_consistency=1

2. 重启MySQL
systemctl restart mysqld 

3. 查看日志是否报错：
less /var/log/mysqld.log

server-id ：指定了当前 MySQL 服务器的唯一标识符（Server ID）。每个 MySQL 服务器都必须有一个唯一的 Server ID。在主从复制中，Slave 服务器根据 Master 的 Server ID 来识别来自不同 Master 的数据变更。这个值在整个复制拓扑结构中必须是唯一的。

log-bin=mylog #开启binlog日志，master必写；mylog是前缀

gtid_mode=ON ：启用 GTID 模式。GTID 是用于在分布式数据库系统中唯一标识事务的全局事务标识符。它可以确保主从服务器之间数据一致性，简化配置和管理复制拓扑结构。

enforce_gtid_consistency=1 ：开启强制 GTID 一致性。强制所有事务必须使用 GTID 标识，以确保主从服务器之间的数据一致性。如果某个事务没有 GTID 标识，MySQL 将不会执行该事务，并且会记录错误。

4. 登录MySQL：主服务器创建账户：
grant replication  slave,reload,super on *.*  to 'alan'@'%' identified by 'Alan@1234';
flush privileges;

# 注意：如果不成功删除以前的binlog日志
-- replication slave权限：拥有此权限可以查看从服务器，从主服务器读取二进制日志。
-- super权限：允许用户使用修改全局变量的SET语句以及CHANGE  MASTER语句
-- reload权限：必须拥有reload权限，才可以执行flush  [tables | logs | privileges]

3.slave01服务器配置

1. 修改从服务器配置
vim /etc/my.cnf
# 添加如下配置
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

2. 重启MySQL
systemctl restart mysqld
less /var/log/mysqld.log

3. 登录MySQL：
mysql> \e
change master to
master_host='master01',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1;

master-info-repository=TABLE：将主服务器信息存储在一个表中，而不是传统的文件中。配置 MySQL 将复制过程中的主服务器信息（如复制位置、主服务器的连接信息等）存储在一个表中（mysql.slave_master_info 表）。这种方式比存储在文件中更加可靠和可管理，尤其是在需要备份和恢复时。

relay-log-info-repository=TABLE：将中继日志信息存储在一个表中，而不是传统的文件中。配置 MySQL 将复制过程中的中继日志信息（如当前正在处理的中继日志位置等）存储在一个表中（mysql.slave_relay_log_info 表）。与存储在文件中相比，这种方式可以更容易地进行管理和恢复。

# 启动slave角色
start slave; 
# 查看状态，验证sql和IO是不是yes。
show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event  #从服务器 I/O 线程的当前状态。
-- 值: "Waiting for master to send event" 表示从服务器正在等待主服务器发送新的事件。
Master_Host: 192.168.174.210   #主服务器的主机名或 IP 地址。
Master_User: slave             #从服务器用于连接主服务器的用户名。
Master_Port: 3306              #主服务器监听的端口。
Connect_Retry: 60              #从服务器重新尝试连接主服务器的时间间隔（以秒为单位）。
Master_Log_File: mylog.000001  #从服务器正在读取的主服务器的二进制日志文件。
Read_Master_Log_Pos: 597       #从服务器在 主服务器的二进制日志 中的读取位置。
Relay_Log_File: mysql-slave01-relay-bin.000002    #当前正在处理的中继日志文件。
Relay_Log_Pos: 802                                #从服务器在中继日志中的当前处理位置。
Relay_Master_Log_File: mylog.000001               #从服务器当前正在处理的主服务器的二进制日志文件。
Slave_IO_Running: Yes                             #从服务器的 I/O 线程状态。                    
Slave_SQL_Running: Yes                            #从服务器的 SQL 线程状态。
Replicate_Do_DB:  
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0                              #SQL 线程遇到的最后一个错误的错误号。0 表示没有错误。
Last_Error:                                #SQL 线程遇到的最后一个错误的错误信息。空白，表示没有错误信息。
Skip_Counter: 0
Exec_Master_Log_Pos: 597                   #从服务器已执行的主服务器二进制日志的位置。
Relay_Log_Space: 1017                      #中继日志文件的总大小（以字节为单位）。
Until_Condition: None                      #复制停止的条件。
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0                   #从服务器落后主服务器的时间（以秒为单位）：0 表示从服务器与主服务器同步。
Master_SSL_Verify_Server_Cert: No          # No：从服务器在通过 SSL 连接到主服务器时不会验证证书的有效性。
Last_IO_Errno: 0                           #I/O 线程遇到的最后一个错误的错误号：0表示没有错误。
Last_IO_Error:                             #I/O 线程遇到的最后一个错误的错误信息空白，表示没有错误信息。
Last_SQL_Errno: 0
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 1                        #主服务器的 Server ID：1 表示主服务器的唯一标识符。
Master_UUID: c5552e0d-3ecc-11ef-a940-0050562892cb     #服务器的 UUID（唯一标识符）
Master_Info_File: mysql.slave_master_info             #存储主服务器信息的文件或表；mysql.slave_master_info：表示使用表存储主服务器信息
SQL_Delay: 0                                          #SQL 线程的延迟时间；0 表示没有延迟
SQL_Remaining_Delay: NULL                             #SQL 线程的剩余延迟时间；NULL 表示没有延迟
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates   #SQL 线程的当前状态；
-- Slave has read all relay log; waiting for more updates： 表示从服务器已读取所有中继日志，正在等待更多更新
Master_Retry_Count: 86400                             #主服务器重试连接次数；86400 表示最大重试次数
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: c5552e0d-3ecc-11ef-a940-0050562892cb:1-2     #从主服务器检索到的 GTID 集合
Executed_Gtid_Set: c5552e0d-3ecc-11ef-a940-0050562892cb:1-2      #从服务器已执行的 GTID 集合
Auto_Position: 1                                                 # 是否启用了自动位置（基于 GTID 的复制）
Replicate_Rewrite_DB:  
Channel_Name: 
Master_TLS_Version:

故障：

当Slave_IO_Running: NO，GTID主从异常报错1236

在master端执行：show variables like '%gtid_purged%';

mysql> show variables like '%gtid_purged%';

gtid_purged值为d6b8367a-3f62-11ef-bf44-000c292f6f5e:1-2

在slave端设置gtid_purged为master端gtid_purged值：

stop slave;

set global gtid_purged="d6b8367a-3f62-11ef-bf44-000c292f6f5e:1-2";

start slave;

show slave status\G

注意：

在关闭和启动mysql服务的时候按顺序先启动master。先将slave库停掉，再停止master库；启动先启动master库，再启动slave库；

1. master服务器：
create database test;
use test;
create table test.t1(id int);
insert into test.t1 values(1),(2);

2. slave服务器：
select * from test.t1;

基于Gtid：互为主从（一主一从）

此过程本篇基于《基于Gtid日志主从复制（一主一从）》，在此优化为互为主从。

1. 把slave改名为：master02
hostnamectl set-hostname master02
reboot

2. 修改两台主机的本地解析：
vim /etc/hosts
-- 192.168.137.10 master01
-- 192.168.137.50 master02

由于master01的配置文件等已经创建好和修改好，只针对master02操作：

1. master02 ：MySQL配置文件修改
vim /etc/my.cnf
# 添加：
server-id=2
log-bin=mysql
gtid_mode=ON
enforce_gtid_consistency=1

2.重启MySQL
systemctl restart mysqld
less /var/log/mysqld.log

master02操作：

1. 登录MySQL：主服务器（master02）创建账户：
grant replication  slave,reload,super on *.*  to 'alan'@'%' identified by 'Alan@1234';

2. 刷新权限
flush privileges;

注意：

一主一从中的”master-info-repository=TABLE; relay-log-info-repository=TABLE“，在互为主从中不适用，互为主从中的配置文件应该一致（除了id标识）

在master01中操作：

3. 配置同步用户
mysql> \e
change master to
master_host='master02',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1;

4. 启动slave
stop slave;
reset slsve;
start slave;
show slave status\G

故障：
reset slave;             #重置从服务器的复制状态。更改主服务器的配置或需要重新同步数据时使用。
start slave;             #启动从服务器的复制进程
show slave status\G;     #检查从服务器的复制状态，确保复制已经正确启动。
select user,host from mysql.user;   #查看用户和主机、
show grants for alan@'%';           #查看用户权限
mysql> show grants for alan@'%';
+-------------------------------------------------------------+
| Grants for alan@%                                           |
+-------------------------------------------------------------+
| GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'alan'@'%' |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
-- 当你使用 GRANT ALL PRIVILEGES ON mydb.* TO 'alan'@'%'; 时，实际上是为用户 alan 从任何主机连接时都授予了权限。
-- 这意味着，当你使用 show grants for 'alan'@'192.169.137.50'; 
-- 查看权限时，理论上应该能看到相应的权限，但 MySQL 可能会因为内部处理方式而显示错误。

基于GTID：实现双主双从【重点】

1.所有主机修改本地解析

vim /etc/hosts
#
192.168.137.10 master01
192.168.137.50 master02
192.168.137.20 slave01
192.168.137.30 slave02

# 互相ping以下，确保连接通畅
# 重启MySQL，关闭防火墙
systemctl restart mysqld
systemctl disable --now firewalld
setenforce 0

# 修改四台主机名称：master服务器上方操作已经更改过了
hostnamectl set-hostname slave01
hostnamectl set-hostname slave02
reboot

MySQL-master01 配置文件配置

MySQL-master01 创建同步用户

MySQL-master02 配置文件修改

MySQL-master02 创建同步用户

MySQL-master01 和MySQL-master02实现互为主从

2.修改slave01配置文件

vim /etc/my.cnf
# 添加：
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

# 重启
systemctl restart mysqld

3.修改slave02配置文件

vim /etc/my.cnf
# 添加：
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

# 重启
systemctl restart mysqld

4.master01 备份所有数据

mysqldump -p'Alan@2403' --all-databases --single-transaction --master-data=2  --flush-logs > `date +%F`-mysql-all.sql

# master01 将备份数据传输至 slave0 1和 slave02
scp 备份文件 slave01:~
scp 备份文件 slave02:~

5.slave导入数据

# slave01：
mysql -p'Alan@1234' < 备份文件

# slave02：
mysql -p'Alan@1234' < 备份文件

6.slave01 配置同步用户信息

1. 将 master01 设为主节点，登录MySQL
mysql> \e
change master to
master_host='master01',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1 for channel 'master01';

2. 将 master02 设为主节点，登录MySQL
mysql> \e
change master to
master_host='master02',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1 for channel 'master02';
-- FOR CHANNEL 'mysql-master01'：指定复制通道的名称。
-- 'master1'：通道名称。MySQL 支持多源复制（即一个从服务器可以从多个主服务器复制数据），
-- 每个通道可以用于不同的主服务器。此选项用于多源复制环境中，标识具体的复制通道。

3. 启动slave,查看slave状态
start slave;
show slave status\G

# 或root用户
change master to
master_host='master01',
master_user='root',
master_password='Alan@2403',
master_auto_position=1 for channel 'master01';
change master to
master_host='master02',
master_user='root',
master_password='Alan@2403',
master_auto_position=1 for channel 'master02';

7.slave02 配置同步用户信息

1. 将 master01 设为主节点，登录MySQL
mysql> \e
change master to
master_host='master01',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1 for channel 'master01';

2. 将 master02 设为主节点，登录MySQL
mysql> \e
change master to
master_host='master02',
master_user='alan',
master_password='Alan@1234',
master_auto_position=1 for channel 'master02';

3. 启动slave,查看slave状态
start slave;
show slave status\G

基于binlog日志的主从复制（一主一从）

1.所有主机和从机修改本地解析配置：

# 关闭防火墙：
setenforce 0
systemctl disable --now firewalld
vim /etc/hosts
# 添加 所有主机的ip和名称

2.配置主机：

master服务器上，必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。

vim /etc/my.cnf
# 添加
log-bin=mysql
server-id=1

# 重启：
systemctl restart mysqld
# 查看错误日志，查看是否报错
# 查看二进制文件：
ls /var/lib/mysql

3.master01：创建主从同步用户：

# mater登录MySQL：
# 创建主从同步用户、密码：所有权限
grant replication slave on *.*  TO  '用户'@'%'  identified by '密码';
grant replication slave on *.*  TO  'alan'@'%'  identified by 'Alan@1234';
--replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志。
#
flush privileges;
# 检查权限
slelct user,host from mysql.user;
show grants for 'alan'@'%';
# master01查看二进制日志偏移量
show master status\G

4.slave01：配置server-id

vim /etc/my.cnf
# 添加
server-id=2

# 重启：
systemctl restart mysqld
# 查看错误日志，查看是否报错

# 登录MySQL，添加主从同步用户
mysql> \e
CHANGE MASTER TO
MASTER_HOST='master01',
MASTER_USER='alan',
MASTER_PASSWORD='Alan@1234',
MASTER_LOG_FILE='mysql.000001',
MASTER_LOG_POS=589;
#
MASTER_HOST        # master01：主机地址，可以写成IP地址
MASTER_USER        # alan：主动同步用户
MASTER_PASSWORD    # 密码
MASTER_LOG_FILE=   # mysql.000001：二进制文件名称
MASTER_LOG_POS=    # 二进制日志文件偏移量，与master端的position一致

# 重启slave服务器
start slave;
# 查看主从同步状态
show slave status\G

5.测试：

1. master服务器：
create database test;
create table test.t1(id int);
insert into test.t1 values(10);

2. slave服务器：
select * from test.t1;