阶段七：MySQL主从架构实现

一、主从架构概述

1. 场景说明

某同学刚入职公司，在熟悉公司业务环境的时候，发现他们的数据库架构是一主两从，但是两台从数据库和主库不同步。询问得知，已经好几个月不同步了，但是每天会全库备份主服务器上的数据到从服务器上，由于数据量不是很大，所以一直没有人处理主从不同步的问题。这次正好问到了，于是乎就安排该同学处理一下这个主从不同步的问题。

主服务器对外提供业务数据，负责业务数据的增删改查操作。
从服务器默认不对外提供服务，和主服务器一样，都处于长时间运行状态，在运行过程中，从服务器会自动从主服务器拉取并同步数据，提供了一个在线热备解决方案。

2. 主从架构学习目标

① 熟悉MySQL数据库常见的主从架构

② 理解MySQL主从架构的实现原理（背诵、记忆）

③ 掌握MySQL主从架构的搭建（重点掌握）

3. 什么是主从复制？

主从复制可以实现将数据从一台数据库服务器（master）复制到一台到多台数据库服务器(slave)

slave：奴隶，从属。

默认情况下，属于异步复制，所以无需维持长连接

解决问题：

① 数据实时备份

② 缓解服务器压力（读操作可以分散到slave服务器）=> MyCAT（读写分离软件）

简单来说：

master将数据库的改变写入二进制日志（Binary Log）；

slave同步这些二进制日志，并根据这些二进制日志进行数据重演操作，实现数据异步同步。

【扩展】
同步复制：从服务器拉取主服务器的数据时，主服务器增删改数据时，从服务器必须马上同步，等待从服务器同步完成后，主服务器才能继续新的事务操作。
优点：两端数据高度一致；
缺点：阻塞主服务器的事务操作
异步复制：从服务器拉取主服务器的数据时，主服务器增删改数据时，从服务器可以异步复制，等待空闲时间在进行拉取，在这个过程中，不会阻塞主服务器业务。
优点：不会阻塞主服务器的事务操作；
缺点：可能会出现主从同步延迟的情况。

4. 主从复制原理（背诵）

image
binlog二进制日志 vs relaylog中继日志（负责把主服务器的DML在slave服务器重写执行一遍）
binlog保存了用户对数据库的增删改事务操作（SQL语句）、relaylog中继日志，当从服务器从主服务器拉取到二进制日志数据时，会首先写入到relaylog中继日志中。

mysqldump --single-transaction --master-data

详细描述：

前提：主服务器开启binlog二进制日志，从服务器开启relaylog中继日志。

① slave端的IO线程发送请求给master端的binlog dump线程

② master端binlog dump线程获取二进制日志信息(文件名和位置信息)发送给slave端的IO线程

③ salve端IO线程获取到的内容依次写到slave端relay log里，并把master端的bin-log文件名和位置记录到master.info里

④ salve端的SQL线程，检测到relay log中内容更新，就会解析relay log里更新的内容，并执行这些操作，从而达到和master数据一致

master：主服务器；slave：从服务器。
注：主从复制也是备份的一种，属于在线热备。到这里就学过3种备份了：逻辑备份、物理备份、在线热备。

二、传统主从复制（AB复制）设计

环境准备

传统AB复制架构(M-S)，说明：mysql数据库，版本为8.0.42

环境说明：

IP主机名角色
192.168.88.101node1.itcast.cnmaster(主)
192.168.88.102node2.itcast.cnslave(从)

安装前准备：

① 安装必备软件，如vim、wget、rsync

② 配置IP、主机名

③ 配置IP与主机映射 => /etc/hosts

④ 关闭防火墙与SELinux

⑤ 时间同步

安装一些依赖软件（系统必备软件）

dnf install vim wget rsync telnet net-tools -y

命令说明：

注意事项：


**关键参数：**

**注意事项：**



在master主数据库中，创建同步账号


...

注意事项：

【注释】 1、auto.cnf文件里保存的是每个数据库实例的UUID信息，代表数据库的唯一标识。


**启动master和slave数据库**

master:

注意事项：


**注意事项：**





...

注意事项：


**注意事项：**



修改主库的配置文件

[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

log-error=/export/server/mysql/master.err
log-bin=/export/server/mysql/data/binlog
server-id=10

character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

注意事项：

[mysqld] basedir=/export/server/mysql datadir=/export/server/mysql/data socket=/tmp/mysql.sock port=3306

log-error=/export/server/mysql/slave.err log-bin=/export/server/mysql/data/binlog relay-log=/export/server/mysql/data/relaylog server-id=20

character_set_server=utf8mb4 collation-server=utf8mb4_unicode_ci


**注意事项：**

注意事项：

master主服务器创建同步账号：




以下是官网提供的关键设置**参考**（仅参考，需要调整为自己master服务器信息）

https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html

在从节点上执行的信息，语法参考

具体代码为：

source_host='192.168.88.101', source_port=3306, source_user='slave', source_password='123', source_auto_position=1;




第三步：开启从库复制

在从库上执行，

查看从库的复制状态

在从库上执行， ...

...



在从库上执行下面命令，重置(reset)所有配置，再change，再启动服务，并查看记过

在主库中，增加数据


在从库查看数据
image

在从库查看复制状态


![](/assets/feishu-images/01b31b9ce2324f8808d787bd.png)


GTID表示为一对坐标，由冒号(:)分隔，如下所示:

例如，最初要在UUID为3E11FA47-71CA-11E1-9E33-C80AA9429562的服务器上提交的第23个事务具有此GTID

3E11FA47-71CA-11E1-9E33-C80AA9429562:23


GTID集合是由一个或多个GTID或GTID范围组成的集合。来自同一服务器的一系列gtid可以折叠成单个表达式，如下所示:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

源自同一服务器的多个单一gtid或gtid范围也可以包含在单个表达式中，gtid范围以冒号分隔，如下例所示:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49

1-3：事务1-3 11：第11个事务 47-49：事务47-49


GTID集合可以包括单个GTID和GTID范围的任意组合，也可以包括来自不同服务器的GTID。


2174B383-5441-11E8-B90A-C80AA9429562:1-3代表第1台服务器事务1-3
24DA167-0C0C-11E8-8442-00059A3C7B00：1-19代表第2台服务器事务1-19

GTID存储在mysql数据库中名为gtid_executed的表中。

该表中的一行包含它所代表的每个GTID或GTID集合的起始服务器的UUID，以及该集合的开始和结束事务id。

image

模拟从库写入数据、主库对表进行写入数据。

master数据库中：


slave数据库中，错误的插入一条记录

编辑slave服务器中的/etc/my.cnf文件

在从库中，
然后重启mysqld

注意事项：

在从库中，


回到master数据库，也重新插入一条记录

slave从服务器查看同步状态：


观察从库复制是否报错

查看从库同步状态：

...
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Until_Log_File:
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Last_IO_Error:
Replicate_Ignore_Server_Ids:
Slave_SQL_Running_State:
Master_Bind:
Last_IO_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Network_Namespace:

复制报错信息

事务接收了1-7，但7没有执行成功。f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7


在主库继续进行其他事务，观察gitd是否复制成功

从库状态

... Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Until_Log_File: Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Last_IO_Error: Replicate_Ignore_Server_Ids: Slave_SQL_Running_State: Master_Bind: Last_IO_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Network_Namespace:


事务，7-9未备执行，也就是说后续复制中断

解决方案：

在实际工作中，如果主从配置不同步，出现了异常情况，解决方案有二

情况一：如果错误事务较少，可以尝试跳过错误事务，进行修复。

情况二：如果错误事务较多，必须要重新配置了主从同步，把主服务器数据进行导出，然后在从服务器进行重新导入，然后重新配置主从。

采用从库跳过错误事务修复

停止slave进程




案例演示（实际改成你们自己的事务）：

第一步：找两者差异
接收到1-12，实际执行1-2，从第3个事务开始同步异常，所以要尝试跳过事务编号3的事务
第二步：找主机uuid

设置空事务，填充跳过的事务（让事务编号连续）


恢复自增事务号

启动slave进程


事务已经跳过，创建表已经同步

重新同步以后，可以在从节点，删除冲突数据或者异常数据，重新执行同步，让两端高度一致！

image

面试题：MySQL主从延迟比较高通常有哪些原因，如何解决？

可能原因

主库：

从库：

网络：

解决方案

安装一些依赖软件（系统必备软件）


**注意事项：**


su或者bash指令

配置IP与主机映射

编辑关键的部分 [connection] id=ens33 uuid=69814cb5-4f67-3069-860d-c0e8274eedad type=ethernet autoconnect-priority=-999 interface-name=ens33 timestamp=1740691392

[ethernet]

[ipv4] method=manual dns=8.8.8.8

[ipv6] addr-gen-mode=eui64 method=auto

[proxy]

注意，在尾部追加如下内容


**注意事项：**


...
...

注意事项：


**注意事项：**

[mysqld]
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306

character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci

log-error=/export/server/mysql/slave.err
log-bin=/export/server/mysql/data/binlog

relay-log=/export/server/mysql/data/relaylog
server-id=20

注意事项：


**注意事项：**



在master主数据库中，创建同步账号


...

注意事项：

【注释】 1、auto.cnf文件里保存的是每个数据库实例的UUID信息，代表数据库的唯一标识。