您当前的位置：首页 > 计算机 > 软件应用 > 数据库 > MySQL

搞懂MySQL主从复制原理及搭建过程，只需这篇文章！

时间：09-30来源：作者：点击数：

主从复制是指将主库的数据变更同步到从库，从而保证主库和从库数据一致，可用于数据备份、失败迁移、读写分离、降低单库读写压力等场景。对于面向大企业的B端产品而言，主从复制技术尤为重要。

本期文章便跟大家讲讲主从复制的原理及搭建过程，干货满满，快往下看~

1 什么是主从复制

主从复制是指将主库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持一致。

MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的优点，主要包含以下三个方面：

1. 实时灾备，用于故障切换：主库出现问题，可以快速切换到从库提供服务。

2. 读写分离，提供查询服务：实现读写分离，降低主库的访问压力。

3. 备份，避免影响业务：可以在从库中执行备份，以避免备份期间影响主库服务。

2 主从复制应用场景

1. 主库压力较大扛不住时，可通过主从复制把读压力分担一部分到从库上；

2. 业务高峰数据库连接数太多时，例如高峰的认证，只需要把这个业务分拆到从库上；

3. 低频复杂类且难以优化的 SQL（报表统计分析类的 SQL），专门在从库上执行；

4. 利用从库做高可用切换。

3 主从复制原理

主从复制原理图示

如上图所示，主从复制的过程原理可分为三个部分：

1. 主库汇报数据变更记录在二进制日志文件 binlog 中：

a) 启动（Slave I/O Thread->master.info；Slave SQL Thread，Relay-log.info）；

b) 建立连接（slave I/O Thread->master.info；Master log Dump Thread->binlog ）。

2. 从库连接主库，读取 binlog 日志，并写入自身中继日志 Relay log：

a) 获取更新日志（Master log dump Thread->binlog）；

b) 传递更新日志（Master log dump Thread->binlog；slave I/O Thread->master.info）；

c) 保存更新日志（slave I/O Thread->master.info；slave Relay log）。

3. slave 重做中继日志，将改变反映它自身的数据：应用中继日志（Slave SQL Thread；relay-log.info；slave Relay Log）。

4 MySQL主从复制工作方式

1. master 记录二进制日志。在每个事务完成数据更新之前，master 在二进制日志记录这些改变。MySQL 将事务串行地写入二进制日志，完成后，master 通知存储引擎提交事务。

2. slave 将 master 的 binary log 拷贝到它自身的中继日志：

a) 首先，slave 开始一个工作线程：I/O 线程；

b) I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件（接收的单位是 event），如果已经跟上 master，它会睡眠并等待 master 产生新的事件；

c) I/O 线程将这些事件写入中继日志。

3. SQL slave 重做中继日志

a) Thread（SQL 从线程）是处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件（回放的单位也是 event）更新 slave 的数据，使其与 master 中的数据一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小；

b) 在 master 中也有一个工作线程：和其他 MySQL 的连接一样，slave 在 master 中打开一个连接也会使得 master 开始一个线程。

5 主从复制分类

主从复制可分为异步复制、同步复制和半同步复制三种：

异步复制

异步复制为 MySQL 默认的复制模式，指主库写 binlog、从库 I/O 线程读 binlog 并写入 relaylog、从库 SQL 线程重放事务这三步之间是异步的。

异步复制的主库不需要关心备库的状态，主库不保证事务被传输到从库，如果主库崩溃，某些事务可能还未发送到从库，切换后可能导致事务的丢失。其优点是可以有更高的吞吐量，缺点是不能保持数据实时一致，不适合要求主从数据一致性要求较高的应用场景。

同步复制

同步复制的模式下，主库在提交事务前，必须确认事务在所有的备库上都已经完成提交。即主库是最后一个提交的，在提交前需要将事务传递给从库并完成重放、提交等一系列动作。其优点是任何时候主备库都是一致的，主库的崩溃不会丢失事务，缺点是由于主库需要等待备库先提交事务，吞吐量很低。

半同步复制

MySQL 从 5.5 版本开始引入了半同步复制，半同步复制介于异步复制和同步复制之间。主库在提交事务时先等待，必须确认至少一个从库收到了事件（从库将事件写入 relaylog，不需要重放和提交，并向主库发送一个确认信息 ACK），主库收到确认信息后才会正式 commit。

与同步复制相比，半同步复制速度快很多，因为它只需要至少1个从库确认写入 relaylog，并不需要完成在从库上的事务提交，同时又比异步复制更安全，因为主库在提交时，事务至少已经存在2个地方（主库的 binlog 和从库的 relaylog）。

由于半同步复制在提交事务前，需要从库返还确认信息，所以这里涉及到网络的往返通信开销，因此半同步复制只适合在网络条件较好且地理上距离不远的环境部署，否则可能会因为网络延迟大幅降低主库性能。

6 MySQL主从复制技术之GTID

GTID是什么

GTID（Global Transaction Identifiers）是已提交事务的一个编号，是事务的全局唯一编号。GTID 和事务会记录到 binlog 中，用来标识事务。GTID 用于替代以前传统复制方法(binglog+position)，在MySQL 5.6.2 开始支持 GTID。

MySQL 支持 GTID 后，一个事务在集群中就不再孤单，在每一个节点中，如果存在具有相同标识符的情况，可以避免同一个事务，在同一节点中出现多次的情况。

GTID 的出现，最直接的效果就是，每一个事务在集群中具有了唯一性的意义，相对于行复制来讲，数据安全性更高，故障切换更简单。

GTID由什么组成

GTID 由 server_uuid：sequence_Number 组成，其中：

server_uuid：是 MySQL 实例的全局唯一标识；存放在$datadir/auto.cnf。
sequence_Number：是 MySQL 内部的事务编号，在一个 MySQL 实例中不会重复。序列号（保证服务器内唯一)，也表示该实例上已经提交事务的数量，并且随着事务提交而递增。

举个例子：server_uuid 存放于“ cat /mysl/data/3306/data/auto.cnf ”，GTID样例如下：

[auto]server_uuid:1599dcea-5a7b-11e8-94bd-000c292834bo

根据 GTID 可以知道事务最初是在哪个实例上提交的，方便故障排查和切换。

小知识：GTID 只能基于 binlog_ROW 格式。

bin_log 二进制日志格式有以下3种：

1)基于语句记录，对应的参数值为 statement。二进制日志文件中保存的是执行过的 SQL 语句，存储日志量最小

2)基于行格式记录（Row-Based Logging，RBL）,对应的参数值为 row。二进制日志文件中记录的是变更的行的信息，存储日志量大，但是无法直接地进行读取。

3)混合记录模式，对应的参数值为 mixed。在记录事件到二进制日志时，MySQL 服务根据需要，动态修改日志记录的格式。对于不确定的操作使用 row 记录，如果每天数据操作量很大，产生的日志比较多，可以考虑选择使用 mixed 格式。

GTID优缺点

优点：可以很方便地进行故障转移，从库不会丢失主库上的任何修改。

缺点：故障处理比较复杂，对执行的SQL有一定限制。以下三类语句不被执行：

a) create table ... select statements

b) create temporary table or drop temporary table statements inside transactions

c) 同时更新事务引擎和非事务引擎

7 主从复制搭建

主从复制搭建过程可分为四大步骤，包括准备服务器、配置主库、配置从库和测试主从复制，小编这就送上详细教程~

Step1：准备服务器

服务器示例

准备好两台服务器之后，在上述两台服务器中分别安装好 MySQL，并完成基础的初始化准备工作。

MySQL 8下载地址：

https://downloads.mysql.com/archives/community/

一、选择安装的 MySQL 版本

1. 首先判断是否要和公司其他已经安装好的 MySQL 保持版本一致。

2. 如果不是实验新功能性质，则不选择 development release，而是安装 General Availability（GA）release（代表稳定版本，可在生产系统使用）。

二、选择安装 MySQL 的方式

安装方式有2种：二进制安装包的方式(RPM/ZIP/Tar等)、源码方式(source code)。一般会选择二进制安装方式；如果有特殊需求，比如修改一部分源码或修改 MySQL 深层次的配置，则会选择源码方式。

三、二进制 Tar 包安装 MySQL

MySQL 安装步骤如下：

步骤一：解压到 /usr/local 目录，相关脚本如下：

[root@localhost opt]# tar -xvf mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz -C  /usr/local/

步骤二：查看解压后的目录结构，相关脚本如下：

[root@localhost local]# du -h --max-depth=1

解压后的目录结构

步骤三：创建运行 MySQL 的用户和组，相关脚本如下：

[root@localhost local]# groupadd mysql[root@localhost local]# useradd  mysql -g mysql

步骤四：创建 MySQL 数据目录，相关脚本如下：

[root@localhost local]# mkdir -pv /mydata/data[root@localhost local]# mkdir /mydata/socket[root@localhost local]# mkdir /mydata/temp[root@localhost local]# mkdir /mydata/log[root@localhost local]# mkdir /mydata/log/{bin_log,relay_log,slow_log}[root@localhost local]#chown -R  mysql:mysql /mydata/socket /mydata/temp  /mydata/log /mydata/log/bin_log /mydata/log/relay_log /mydata/log/slow_log

步骤五：修改 MySQL 安装权限，具体如下：

a) 创建 MySQL 软连接

[root@localhost local]# ln -sv mysql-8.0.31-linux-glibc2.12-x86_64/ mysql

b) 修改 MySQL 安装目录权限

[root@localhost local]# chown -R  mysql:mysql mysql [root@localhost local]# chown -R  mysql:mysql mysql-8.0.31-linux-glibc2.12-x86_64

步骤六：安装 MySQL，具体如下：

1) 配置 MySQL 环境变量

[root@localhost local]# cat /etc/profile.d/mysql.sh

export MYSQL_HOME=/usr/local/mysql/export PATH=$MYSQL_HOME/bin:$PATH

[root@localhost local]# source /etc/profile.d/mysql.sh

[root@localhost local]# mysql -Vmysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

2) 初始化数据目录

[root@localhostlocal]#mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/mydata/data --lower-case-table-names=1 --innodb_data_file_path=ibdata1:1G:autoextend --initialize-insecure

3) 查看初始化目录结构

4）将 MySQL 服务文件上传到指定目录：/usr/lib/systemd/system

[root@localhost system]# ll mysq*   -rw-r--r--. 1 root root 388 Mar 26 13:15 mysqld.service

5) 设置开机自启动

[root@localhost system]#systemctl daemon-reload[root@localhost system]#systemctl enable mysqld[root@localhost system]# systemctl list-unit-files|grep mysql

6）启动 MySQL 服务

mysqld_safe --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql & 或 systemctl start  mysqld

7) 检查 MySQL 服务状态

netstat -tnulp |grep mysql

8) 登录连接 MySQL，改密码建用户

首次进入使用空密码

mysql -uroot -p  -hlocalhost  -S /mydata/socket/mysql_8307.sock

修改 root 密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Cosmic@2020';  flush privileges;

9）创建苍穹用户并赋权

create user 'cosmic'@'%' IDENTIFIED WITH mysql_native_password BY 'Cosmic@2020';grant all privileges on *.* to 'cosmic'@'%';flush privileges;

Step2：配置主库

1. 修改配置文件/etc/my.cnf

# mysql服务ID，保证整个集群环境中唯一，取值范围1~2^32-1，默认为1。

server-id=1

# 是否只读，1代表只读，0代表读写

read-only=0

# 忽略的数据，指不需要同步的数据库

binlog-ignore-db=mysql_001

# 指定同步的数据库

binlog-do-db=db01

2. 登陆 MySQL，创建远程登陆链接的账号，并授予主从复制的权限

# 创建kingdee_test用户，并设置密码，该用户可在任意主机链接该mysql服务。

create user 'kingdee_test'@'%' identified with mysql_native_password by 'Root@123456';

# 为'kingdee_test'@'%' 用户分配主从复制权限。

grant replication slave on *.* to 'kingdee_test'@'%';

3. 通过指令，查看二进制日志坐标

show master status

二进制日志

Step3：配置从库

1. 修改配置文件/etc/my.cnf

# mysql服务ID，保证整个集群环境中唯一，取值范围1~2^32-1，和主库不一样即可。

server-id=2

# 是否只读，1代表只读，0代表读写

read-only=1

2. 登陆 MySQL，设置主库配置

change replication source to source_host='10.0.38.72', source_port=8307,source_user='kingdee_test',source_password='Root@123456',source_log_file='mysql_bin_1.000004',source_log_pos=1804;

参数说明如下：

参数名	含义
source_host	主库IP地址
source_port	连接主库的端口号
source_user	连接主库的用户名
source_password	连接主库的密码
source_log_file	binlog日志文件名
source_log_pos	binlog日志文件位置

3. 开启同步操作

start replica;

4. 查看主从同步状态

show replica status;

查看主从同步状态结果

Step4：测试主从复制

1. 查看 GTID 状态

show  variables like '%gtid%';

查看gtid状态结果

2. 在主库上创建数据库、表，并插入数据

create database kingdee_cosmic ,use kingdee_cosmic;CREATE TABLE `t_sec_user` (  `id` INT NOT NULL,  `namebase` VARCHAR(15) NOT NULL,  `sex` VARCHAR(5) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t_sec_user(id,namebase,sex) VALUES(1,'Tom', '1'),(2,'Trigger', '0'),(3,'Dawn','7');

3. 在从库中查询数据，验证主从是否同步