MySQL 主从复制

在本文我们会介绍MySQL的二进制文件、GTID、主从复制方案。

提示

使用Docker启动多个MySQL实例，MySQL版本使用的是8.4。

1. 二进制日志

二进制日志 (Binary Log)，通常简称为 Binlog，它以二进制格式记录了所有对 MySQL 数据库进行数据修改的操作。

Binlog 记录了所有修改数据的 SQL 语句（包括 DDL 和 DML，但不包括 SELECT、SHOW 等查询语句）以及这些语句执行所花费的时间，它以“事件（Event）”的形式记录这些操作。

为什么有了Redo Log还要有Binlog呢？

简单来说，binlog 是 MySQL 服务器层面的日志，与具体的存储引擎无关。无论使用 InnoDB、MyISAM 还是其他存储引擎，只要开启了 binlog配置，都会记录操作到日志中。而Redo Log是InnoDB引擎实现的，属于引擎层，更为底层。

Binlog 通常由两类文件组成：

二进制日志索引文件 (.index)： 这是一个文本文件，记录了所有二进制日志文件的列表和顺序。
二进制日志文件 (.00000*)： 实际的二进制日志文件，后缀通常是类似 .000001、.000002 这样的数字序列，表示不同的日志文件。

为了适应不同的需求，Binlog 提供了几种记录格式：

Statement 格式：记录执行的 SQL 语句。这种格式下，Binlog 文件通常较小，但对于一些非确定性函数（如 UUID() 或 NOW()）和存储过程，可能会导致主从数据不一致的问题。
Row 格式：记录每一行数据的实际变更。这种格式下，Binlog 文件可能较大，但可以确保主从数据的一致性，解决了 Statement 模式的问题。
Mixed 格式：这是 Statement 和 Row 格式的混合。MySQL 会根据具体的操作智能选择使用 Statement 格式或 Row 格式。例如，对于简单 DDL 和 DML 语句，会使用 Statement 格式；对于可能导致不一致性的操作，会切换到 Row 格式。

我们可以使用以下命令查看Binlog相关配置：

sql

show variables like '%log_bin%' ;

可以看到，Binlog默认开启。

可以使用以下命令查看Binlog记录格式：

sql

show variables like '%binlog_format%';

可以看到，默认的记录格式是ROW。

由于 Binlog 是二进制文件，无法直接用文本编辑器打开。MySQL 提供了 mysqlbinlog 工具来解析和查看 Binlog 文件内容。例如：

bash

../bin/mysqlbinlog -v ./binlog.000015

-v：基于行记录格式，重建SQL语句；

下图是mysqlbinlog解析部分结果：

2. 主从复制

主从复制，就是将一个 MySQL 数据库服务器（通常称为 主服务器 Master/Source）上的数据变更实时地同步到一个或多个其他 MySQL 数据库服务器（通常称为 从服务器 Slave/Replica）。

主从复制常用作以下需求：

高可用性 (High Availability) 和灾难恢复： 当主服务器发生故障时，可以快速将从服务器提升为新的主服务器，从而最大限度地减少服务中断时间。
读写分离 (Read/Write Splitting)： 可以将所有的写操作（INSERT, UPDATE, DELETE）发送到主服务器，而将大量的读操作分发到从服务器上。这可以显著减轻主服务器的负载，提高数据库的整体性能。
数据备份： 可以在不影响主服务器性能的情况下，在从服务器上进行数据备份，因为备份操作可能对数据库性能造成影响。
报表和数据分析： 可以在从服务器上运行耗时长的报表查询和数据分析任务，而不会影响主服务器的在线业务。

3. 基于二进制文件的主从复制

3.1 原理

基于二进制文件的主从辅助需要在主数据库（source）上启用Binlog配置（默认开启），原理图如下：

首先，在主数据库（source）上执行数据变更语句（例如insert语句），然后记录在binlog中；
从数据库（replica）的IO线程（称为receiver）连接到主数据库，请求获取binlog内容；
从数据库的IO线程将获取到的binlog写入到本机的Relay log中；
从数据库的SQL线程（称为applier）从Relay log种读取内容，并执行SQL语句，达到复制的作用；

3.2 步骤

可以按照如下步骤配置基于二进制文件的主从复制：

首先在主数据库的配置文件中设置server-id，server-id的取值应在 $[1, 2^{32} - 1]$ 范围之内，并且在主从复制拓扑结构中唯一；
txt
```
[mysqld]
server-id=1
```
1
2
然后在主数据库中创建账号并分配REPLICATION SLAVE权限给该账号，从数据库可以使用该账号连接到主数据上：
sql
```
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
```
1
2
然后在主数据库中执行下面语句开启全局锁，这一步的命令是为了保持主数据库的数据一致性：
sql
```
mysql> FLUSH TABLES WITH READ LOCK;
```
1
然后在主数据中执行下面的语句，获取二进制日志文件下一个写入的位置：
sql
```
SHOW BINARY LOG STATUS;
```
1
记住File和Position列的值，后续需要使用；
【可选的】开始导出主数据库的数据，如果主数据库中有历史数据，需要使用mysqldump导出数据；
解锁主数据库：
sql
```
mysql> UNLOCK TABLES;
```
1
设置从数据库的server-id，注意，需要和主数据库的server-id不一样；
【可选的】如果主数据库导出了历史数据，需要在从数据库中导入；
在从数据库中配置主数据库信息：
sql
```
mysql> CHANGE REPLICATION SOURCE TO
    ->     SOURCE_HOST='source_host_name',
    ->     SOURCE_USER='replication_user_name',
    ->     SOURCE_PASSWORD='replication_password',
    ->     SOURCE_LOG_FILE='recorded_log_file_name',
    ->     SOURCE_LOG_POS=recorded_log_position;
```
1
2
3
4
5
6
- SOURCE_HOST：主数据库主机名；
- SOURCE_USER：用户名，即第2步配置的；
- SOURCE_PASSWORD：用户密码，即第2步配置的；
- SOURCE_LOG_FILE：主数据库二进制日志文件名，即第4步中的File列值；
- SOURCE_LOG_POS：主数据库二进制日志文件地址，即第4步中的Position列值；
在从数据库中执行以下命令，开始复制：
sql
```
mysql> start replica;
```
1

3.3 实践

我们使用Docker开启两个MySQL数据库，模拟一主一从。

首先在本机上准备两个目录：

/projects/mysql/data/source：作为主数据库的数据目录；
/projects/mysql/config/source：作为主数据库的配置目录，并且在该目录下存在名为my.cnf的配置文件，其中设置主数据库的server-id为11。

然后运行如下命令启动主数据库：

bash

docker run  
--env=MYSQL_ROOT_PASSWORD=abc123@ 
--volume=/projects/mysql/data/source:/var/lib/mysql 
--volume=/projects/mysql/config/source:/etc/mysql/conf.d 
-p 3316:3306 -d 
mysql:8.4

当启动完成后，使用navicat连接到该数据库，查询server-id，确实是11，表示配置文件生效：

然后执行下面的SQL语句，模拟数据操作：

sql

create database db01;
use db01;

create table stu(
id int auto_increment primary key,
name varchar(255) not null,
age int);
insert into stu(name, age) values('张三', 18),('李四',20),('王五',null);

select * from stu;

然后在主数据库中创建账号并赋予权限：

sql

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl1234';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

然后在主数据库中执行以下命令，上全局锁：

sql

FLUSH TABLES WITH READ LOCK;

之后就不能在主数据库上执行数据更改操作了，为数据导出做准备。

首先查看二进制日志状态：

sql

SHOW BINARY LOG STATUS;

之后就可以在主数据库上执行数据导出操作，使用mysqldump：

bash

mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD" > /var/lib/mysql/dump.sql

首先要使用docker exec进入容器内部；
指定的路径是/var/lib/mysql/，这是容器里的路径，但是目录映射到本地主机的/projects/mysql/data/source目录，所以可以在本地主机该位置找到dump.sql；

执行完成后，我们就可以解锁主数据库了：

sql

UNLOCK TABLES;

至此，关于主数据库的准备已完成。我们开始配置从数据库。

同样，先准备两个目录，作为从数据库的数据目录和配置目录：

/projects/mysql/data/replica1：作为从数据库的数据目录；
/projects/mysql/config/replica1：作为从数据库的配置目录，并且在该目录下存在名为my.cnf的配置文件，其中设置从数据库的server-id为12。

然后使用docker再次启动一个MySQL数据库：

bash

docker run  
--env=MYSQL_ROOT_PASSWORD=abc123@ 
--volume=/projects/mysql/data/replica1:/var/lib/mysql 
--volume=/projects/mysql/config/replica1:/etc/mysql/conf.d 
-p 3317:3306 -d 
mysql:8.4

使用navicat连接到从数据库，并开始导入主数据库历史数据：

选择之前导出的SQL文件即可。完成后从数据就有了主数据库历史数据。

然后开始配置主从复制，执行下面的语句：

sql

CHANGE REPLICATION SOURCE TO
     SOURCE_HOST='172.17.0.2',
		 SOURCE_PORT=3306,
     SOURCE_USER='repl',
     SOURCE_PASSWORD='repl1234',
     SOURCE_LOG_FILE='binlog.000003',
     SOURCE_LOG_POS=1456,
		 GET_SOURCE_PUBLIC_KEY =1;

有两个注意点：

SOURCE_HOST：表示主数据库的主机地址，由于是在Docker环境下配置的，所以需要在主数据库容器查看IP地址，然后写在配置中，可以使用docker inspect命令查看；
GET_SOURCE_PUBLIC_KEY：该配置项默认禁用，会造成如下问题：主服务器的 repl 用户（用于复制的用户）的认证插件是 caching_sha2_password，并且连接不是通过 SSL/TLS 加密的，MySQL 客户端就可能无法成功认证，导致连不上主数据库库。为了在这种非 SSL/TLS 连接下也能安全地使用 caching_sha2_password，MySQL 提供了RSA 公钥交换机制，即客户端（从服务器）可以在连接开始时请求主服务器的 RSA 公钥。
txt
```
Error connecting to source 'repl@172.17.0.2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
```
1

然后执行下面命令，开始复制：

sql

start replica;

之后，我们在从数据库查询stu表（之前在主数据库做了一些改动），结果如下：

可以看到这些改动正确同步到了从数据库。

我们也可以执行下面语句，查看复制情况：

sql

show replica status;

结果如下：

可以发现在从数据库中保存了主数据库二进制日志文件名和位置，所以我们可以随时启停从数据库。

4. GTID

4.1 GTID概述

GTID，全称global transaction identifier，即全局事务标识符，当一个事务提交（commit）时，GTID伴随着事务创建并保存在Binlog中。

GTID的格式如下：

properties

GTID = source_id:transaction_id

source_id：标识数据库，通常情况下，取值数据库的server_uuid：https://dev.mysql.com/doc/refman/8.4/en/replication-options.html#sysvar_server_uuid
transaction_id：标识在该数据库中执行的事务序列号，取值范围： $[1, 2^{63} - 1]$

例如，在某个数据库上第23个提交的事务，其GTID如下：

properties

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

GTID范围：如果在同一个数据库上，连续执行的事务，它们的GTID可以用一个范围表示，例如：

properties

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

GTID集合：GTID集合包含多个单独的GTID和GTID范围，例如：

properties

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

在MySQL中，GTID默认禁止，我们可以使用以下配置启用GTID：

properties

gtid_mode=ON
enforce-gtid-consistency=ON

4.2 GTID生命周期

本小节介绍GTID的生命周期以及涉及到的系统表和环境变量：

首先，客户端提交事务（commit），数据库为事务分配一个GTID，然后将GTID和该事务一起保存进Binlog中，并且将该GTID保存进系统变量@@GLOBAL.gtid_executed中，该环境变量表示已执行事务的GTID，是GTID集合。
问题：GTID如何分配？在MySQL中，系统变量@@gtid_next决定了GTID如何分配，默认值为AUTOMATIC，表示新的GTID为server_uuid:transaction_id，其中transaction_id为自增长的序列号。
当Binlog切换（例如，从Binlog.000001切换到Binlog000002）或数据库关闭时，MySQL会将前一个Binlog中的GTID保存进mysql.gtid_executed表中。表结构如下：
sql
```
CREATE TABLE gtid_executed (
  source_uuid CHAR(36) NOT NULL,
  interval_start BIGINT NOT NULL,
  interval_end BIGINT NOT NULL,
  gtid_tag CHAR(32) NOT NULL,
  PRIMARY KEY (source_uuid, gtid_tag, interval_start)
);
```
1
2
3
4
5
6
7
mysql.gtid_executed表不是全部已执行事务的GTID，因为有部分GTID还在当前使用的Binlog中，没有刷新到该表中。
上面的情况是在开启了Binlog的前提下执行的，如果在开启了GTID时关闭了Binlog，那么在事务提交时，会为事务分配GTID，然后将GTID保存进系统变量@@GLOBAL.gtid_executed中，并且写进mysql.gtid_executed表中。所以，此时mysql.gtid_executed表中就是全部已执行事务的GTID。

5. 基于GTID的主从复制

5.1 原理

基于GTID的主从复制，与基于二进制文件的主从复制原理图类似：

相同点：

主数据库必须开启Binlog配置；

不同点：

主数据库和从数据库都要开启GTID配置；
从数据库的SQL线程从Relay log中读取并执行事务时，由于该事务包含GTID，所以SQL线程先将系统变量@@gtid_next设置为读取到的GTID，然后执行事务，提交事务时，从系统变量@@gtid_next获取GTID（此前设置过，所以不是AUTOMATIC），为该事务设置GTID，从而保证从数据和主数据库，相同事务具有相同GTID值。
从数据库的SQL线程可能有多个，那么有可能多个线程并发执行同一个事务，MySQL保证每个事务只会被执行一次。在执行某个事务之前，会先获取该事务GTID的所有权，获取了所有权后会先检查该事务的GTID是不是已经在系统变量@@GLOBAL.gtid_executed中了，如果在，说明该事务已经被执行了，则跳过执行。
自动定位：在基于GTID的主从复制中，CHANGE REPLICATION SOURCE TO不再需要SOURCE_LOG_FILE和SOURCE_LOG_POS配置项，只需要启用SOURCE_AUTO_POSITION。在从数据库中，只要开启了GTID配置（gtid_mode=ON），并且启用了SOURCE_AUTO_POSITION，那么自动定位就自动激活了。
自动定位的机制如下：从数据库与主数据库建立连接后，从数据库向主数据库发送从数据库已执行（@@GLOBAL.gtid_executed）和已接收到（SELECT RECEIVED_TRANSACTION_SET FROM PERFORMANCE_SCHEMA.replication_connection_status）的GTIDs。主数据库对比自己已执行的GTID（@@GLOBAL.gtid_executed），找出从数据库缺失的GTID，然后再定位到Binlog，将缺失GTID对应的事务传输给从数据库，从而实现主从一致。

5.2 步骤

下面介绍如何设置基于GTID的主从复制：

【可选的】如果已经配置了基于二进制文件位置的主从复制，那么可以将数据库设置为只读状态，使数据库之间保持一致：
sql
```
mysql> SET @@GLOBAL.read_only = ON;
```
1
【可选的】如果数据库在运行中，关闭数据库；如果此时只有主数据库，还没有从数据库，那么可以将主数据库的数据导出，留待后续导入从数据库；
在主从数据库中开启GTID配置：
properties
```
gtid_mode=ON
enforce-gtid-consistency=ON
```
1
2
在从数据的配置中，还需要多加一个配置项：
properties
```
skip-replica-start=ON
```
1
表示在数据库启动时，不要开启复制，需要时手动开启复制。
启动主数据库；
启动从数据库，此时如果有第2步导出的数据，可以导入从数据库；

配置从数据库使用基于GTID的主从复制：

sql

mysql> CHANGE REPLICATION SOURCE TO
     >     SOURCE_HOST = host,
     >     SOURCE_PORT = port,
     >     SOURCE_USER = user,
     >     SOURCE_PASSWORD = password,
     >     SOURCE_AUTO_POSITION = 1;

在从数据中启动主从复制：
sql
```
mysql> START REPLICA;
```
1

5.3 实践

配置过程较为简单，此处不再演示。

展示从数据库复制状态：

可以看到从数据库接收到的和已执行的GTID集合。

关于使用GTID主从复制的好处：

最大的好处就是无需手动指定 Binlog 文件和位置，简化配置，只要主数据库开启了GTID，那么就可以在从数据库中开启主从复制。
保持数据一致性：GTID 保证了每个已提交的事务都有一个唯一的标识符。只要从服务器已经执行了主服务器上所有具有相同 GTID 的事务，那么数据就是一致的。

使用GTID的注意事项：由于GTID依赖于Binlog，如果Binlog由于过期被自动删除，有可能导致找不到GTID对应的事务，从而导致复制失败。所以可以适当设置长一点的Binlog过期时间。

6. 读写分离

这是应用层面的工作，参考ShardingSphere。

参考资料

[1] GET_SOURCE_PUBLIC_KEY：https://stackoverflow.com/questions/69936021/error-002061-authentication-plugin-caching-sha2-password-reported-error-aut

[2] GTID：https://dev.mysql.com/doc/refman/8.4/en/replication-gtids.html

MySQL 主从复制 ​

1. 二进制日志 ​

2. 主从复制 ​

3. 基于二进制文件的主从复制 ​

3.1 原理 ​

3.2 步骤 ​

3.3 实践 ​

4. GTID ​

4.1 GTID概述 ​

4.2 GTID生命周期 ​

5. 基于GTID的主从复制 ​

5.1 原理 ​

5.2 步骤 ​

5.3 实践 ​

6. 读写分离 ​

参考资料 ​

MySQL 主从复制

1. 二进制日志

2. 主从复制

3. 基于二进制文件的主从复制

3.1 原理

3.2 步骤

3.3 实践

4. GTID

4.1 GTID概述

4.2 GTID生命周期

5. 基于GTID的主从复制

5.1 原理

5.2 步骤

5.3 实践

6. 读写分离

参考资料