MySQL8 主从同步详解

置顶 geXingW

已于 2023-04-17 22:16:22 修改

阅读量1.5k

点赞数

分类专栏： MySQL 文章标签： mysql 服务器数据库 mysql 8

于 2022-05-22 14:41:13 首次发布

本文链接：https://blog.youkuaiyun.com/for_happy123/article/details/124910342

版权

MySQL 专栏收录该内容

6 篇文章

订阅专栏

什么是MySQL复制

MySQL复制是将一个MySQL服务器（称为主服务器）上的数据复制到一个或多个MySQL服务器（称为从服务器）的方式，是构建基于MySQL的高性能、高可用、可扩展、数据备份与灾难恢复等工作的基础。

MySQL复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。

MySQL复制的优点

横向扩展解决方案，将负载分散到多个副本以提高性能。
异步复制，无需永久连接即可从主服务器接收更新。
数据安全，副本可以暂停复制过程，因此可以在副本上运行备份服务，而不会损坏相应的数据。
实时分析，可以使用从服务器实时复制主服务器的数据进行数据分析，而不影响主服务器的性能。
远距离数据分发，可以使用复制创建数据库的远程副本，以供远程站点使用，而无需永久访问主服务器。

MySQL复制的常见用途

水平扩展与负载均衡。通过为MySQL源服务器配置一个或多个MySQL副本服务器，分担主库查询流量，MySQL源服务器仅用于更新。可提高MySQL源服务器写入性能和MySQL服务器整体的查询性能。
数据备份。为MySQL服务器配置副本服务器，作为源数据库的备份服务器。
高可用和故障切换。为MySQL源服务器配置副本服务器，可利用虚拟IP代理功能，避免MySQL的单点故障，或显著缩短宕机恢复时间。
系统升级测试。可使用复制源服务器的副本服务器作为应用上线前的测试数据库，进行功能测试或压力测试。

MySQL支持的复制方式

基于二进制日志文件位置的复制。
基于全局事务ID（GTID）的复制。

MySQL支持的二进制日志格式

STATEMENT基于语句的复制。主库将所有造成数据更改的查询记录到二进制日志，从库只需要读取并在本地重放这些更改即可。优点：
- 无论造成更改的SQL，更改了多少数据。二进制日志文件仅记录该条SQL，存储更加紧凑。当更改的数据较多时，极大的节省了存储空间。同样从库在进行日志同步和重放时，带宽占用更少，也更快。
- 二进制中记录的SQL为原始SQL，发生问题时更方便定位。
缺点：
```
- 当主从数据库存在差异时，同一条SQL语句在主库和从库上会产生不同的结果。尤其是调用函数和存储过程时，例如：`USER()`、`UUID()`等，包括使用当前时间戳作为默认值的列，由于SQL语句在主、从库上执行时间的不同，时间戳的取值也不同。对于`AUTO_INCREMENT`这类字段可能会存在不一致。
- 不能使用索引的`UPDATE`语句，将会扫描全表，比基于行的复制要锁定更多的行。
```
ROW基于行的复制。主服务器每次有增、删、改，将每个更改的行写入二进制日志。

优点：
- 该类型的日志比较安全性高
- 相比于语句复制，在执行任何INSERT、UPDATE、DELETE操作时，不会进行全表扫描，相比于基于语句的复制，产生的锁要少很多。同时也无需额外产生建立查询计划的消耗，占用更少的CPU资源。
缺点：
- 没有记录造成更改的SQL语句，也就无法快速获得执行了哪些SQL。
- 基于行的复制，如果找不到要修改的行的，会导致抛出错误，导致复制中断。
- 基于行的复制，在对单条SQL需要修改大量行时，会产生大量的二进制日志。从库在进行复制和重放时，需要消耗更大的带宽。
MIXED混合复制。MySQL自行决定使用STATEMENT或ROW，并自动切换。默认使用的是：STATEMENT，遇到以下情况使用ROW模式：
- 当语句中包含UUID()、FOUND_ROWS、ROW_COUNT、USER()、CURRENT_USER()、LOAD_FILE()
- 当调用触发器或函数更新一个或多个自增字段
- 当语句中使用了系统变量
对于DML语句无论如何配置，都采用STATEMENT进行记录，即使设置binlog_format=row。如：CREATE TABLE和ALTER TABLE等。

MySQL复制的工作方式

主服务器将数据更改记录到二进制日志（Bin Log）中；
从服务器根据设定的主服务器配置，创建I/O线程与主服务器建立客户端连接；
当从服务器I/O线程与主服务器建立连接后，主服务器创建Binlog Dump线程（二进制转储线程），当主库的二进制日志中有新的事件产生，Binlog Dump线程会对要复制的事件加锁，并发送给从服务器的I/O线程。Binlog dump线程会在二进制日志被读取后释放掉二进制事件的锁，无需等待从服务器是否确认收到该事件。
从服务器的I/O线程将主服务器Bin log，复制到自己的中继日志（Relay Log）中；
从服务器通过自身创建的SQL线程重放中继日志中的二进制事件。
如果有多个从服务器，主服务器会启动多个Binlog Dump线程。从服务器的I/O线程和SQL线程是相互独立的，因此I/O线程即便比SQL线程快很多，导致SQL线程在服务器停机前没有执行完所有中继日志，再服务器重启后，依然可以继续执行中继日志中的事件。
在整个复制过程中，一共有两种日志：Bin log二进制日志和Relay log中继日志；一共有三种线程：I/O线程、SQL线程和Binlog Dump线程。
在一主一从且从库为单线程复制的架构中，三种线程的数量为：1个I/O线程、1个SQL线程和1个Binlog Dump线程；当架构为一主多从且从库为单线程复制的架构时，假设从库数量为n，三种线程的数量为：n个I/O线程、n个SQL线程和n个Binlog Dump线程；在从库为多线程复制的架构中，假设从库SQL线程数量设置为m (m > 1)，且有n个从库，三种线程的数量为：n个I/O线程、n个SQL线程和m+1个Binlog Dump线程，从库需要为多个SQL线程另外启动一个协调线程（coordinator thread）.

配置复制

为复制创建账户。最好可以专门创建一个账号用于复制，赋予REPLICATION SLAVE和REPLICATION CLIENT权限，并限制其只能在指定IP或域名范围内访问。

-- 创建复制账号，并设定仅在指定IP段可以访问
CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'password';

# 为账号添加访问所有数据库中所有表的REPLICATION SLAVE权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

对于仅作为复制的从服务器来说REPLICATION SLAVE权限已经够了，为什么要加REPLICATION CLIENT权限？

先看一下两个权限的功能：

REPLICATION SLAVE：允许用户执行SHOW REPLICAS、SHOW RELYLOG EVENTS和SHOW BINLOG EVENTS，拥有该权限的用户可以从主服务器获取产生更新的二进制事件。

REPLICATION CLIENT: 允许用户执行SHOW MASTER STATUS、SHOW REPLICA_STATUS和SHOW BINARY LOGS。

从两个权限的区别，可以看到REPLICATION CLIENT提供了许多常用于查看复制状态的命令。

为主服务器和从服务器设置全局唯一的服务器ID，新部署服务器的默认server_id为1；
```
# 执行如下命令全局设置，服务器重启后失效。   
SET GLOBAL server_id = 1;
```

为什么要保证server_id的全局唯一性？

在主 <-> 主架构或其他类似的架构中，两个主服务器互为主从，主服务器B从主服务器A获取的二进制事件，重放后需要重新写入主服务器B的Binlog中，那么主服务器A在获取主服务器B的Binlog时将会再次执行相同的二进制事件（该事件本来就是主服务器A产生的，且已执行过的），就会导致无线循环。

于是，当主服务器A在同步主服务器B的二进制事件时，发现该事件对应的server_id与自身相同，将会忽略该事件，避免重复执行。

如何选择server_id？

server_id应尽量选择不易重复且易于区分的值，如：在同一个D类地址的网段中，可以选择D段地址为server_id。

配置主从服务器
1. 主服务器配置
```
[mysqld]
server-id=2

# 指定bin log文件位置和名称
log_bin=/apps/mysql/bin_log/my-binlog
```
2. 从服务器配置
```
[mysqld]
server-id=2

# 指定bin log文件位置和名称
log_bin=/apps/mysql/bin_log/my-binlog

# 指定relay log文件位置和名称
relay_log=/apps/mysql/relay_log/my-relaylog

# 设定从服务器只读
read_only=1

# 禁止服务器启动时，自动开始复制
skip-replica-start=1
```
为什么要指定bin log和relay log的位置和名称？

在早些的版本中，如果没有专门指定，MySQL服务器会使用机器名作为默认值，例如：host_name-bin。如果后续机器名改变，bin log文件的名字也将改变。当在配置文件指定了bin log文件名后，后续将不再受机器名更改的影响。

另外，比较早版本的MySQL中，bin log功能并不是默认开启，当在配置文件配置过bin log文件名后会进行开启。

为什么要禁止从服务器重启自动开始复制？

有时在计划之外的重启，会导致数据出错，特别是非InnoDB引擎的数据库。禁止服务器自动启动复制，可在服务器重启后，手动校验、修复数据后，再使用START SLAVE开始复制。

基于二进制日志文件位置的复制。

查看主服务器的二进制日志位置。如果在开启开启复制前，需要将已存在的数据同步到从库，可以通过创建主服务器数据快照，导入到从服务器，再进行同步。

# 查看当前的二进制日志位置
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      736 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

上述显示当前主服务器的二进制日志文件为binlog.000002，位置为736。

配置从服务器复制信息。以下命令均在从服务器上执行。

-- MySQL 8.0.23之前版本
CHANGE MASTER TO
MASTER_HOST='host', -- 主服务器地址
MASTER_PORT = port, -- 主服务器端口
MASTER_USER='repl', -- 前边创建的`repl`用户名
MASTER_PASSWORD='password', -- 前边创建的`repl`用户秘密
MASTER_LOG_FILE='binlog.000002', -- 通过SHOW MASTER STATUS命令查看到的主库二进制日志文件名
MASTER_LOG_POS=736; -- 通过SHOW MASTER STATUS命令查看到的主库二进制日志位置

-- MySQL 8.0.23及之后版本。
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='host', -- 主服务器地址
SOURCE_PORT = port, -- 主服务器端口
SOURCE_USER='repl', -- 前边创建的`repl`用户名
SOURCE_PASSWORD='password', -- 前边创建的`repl`用户秘密
SOURCE_LOG_FILE='binlog.000002', -- 通过SHOW MASTER STATUS命令查看到的主库二进制日志文件名
SOURCE_LOG_POS=736; -- 通过SHOW MASTER STATUS命令查看到的主库二进制日志位置

开始复制，并查看复制状态。在从服务器执行

-- 开始复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G;

-- 停止复制
STOP SLAVE;

基于全局事务ID（GTID）的复制。

GTID生命周期
- 当事务提交时，MySQL会为每个事务分配一个由当前机器UUID + 序列号的组成的尚未使用的GTID，并写入二进制日志文件。在二进制文件中，GTID排在事务之前写入。
- 当MySQLBinlog轮换或者服务器重启时，MySQL会将当前所有写入Binlog的GTID存入mysql.gtid_executed表。

配置主、从服务器，开启GTID，重启服务器

# 编辑my.cnf，添加如下内容
gtid_mode=ON  # 开启GTID

enforce-gtid-consistency=ON # 在GTID模式下，强制只能将保证一致性的语句记录到Binlog，部分语句将不能被写入Binlog

重启服务器，查看GTID状态

-- 查看配置
SHOW VARIABLES LIKE '%gtid%';

-- 查看主库状态
SHOW MASTER STATU;

更改从服务器的复制选项

-- MySQL 8.0.23之前版本
CHANGE MASTER TO
MASTER_HOST = host, -- 主服务器地址
MASTER_PORT = port, -- 主服务器端口
MASTER_USER = user, -- 前边创建的`repl`用户名
MASTER_PASSWORD = password, -- 前边创建的`repl`用户秘密
MASTER_AUTO_POSITION = 1; -- 需要从主服务器哪个GTID开始复制

-- MySQL 8.0.23及之后版本。
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = host, -- 主服务器地址
SOURCE_PORT = port, -- 主服务器端口
SOURCE_USER = user, -- 前边创建的`repl`用户名
SOURCE_PASSWORD = password, -- 前边创建的`repl`用户秘密
SOURCE_AUTO_POSITION = 1; -- 需要从主服务器哪个GTID开始复制

延迟复制

-- 停止复制
STOP SLAVE;

-- 设置复制延迟
-- MySQL 8.0.23之前版本
CHANGE MASTER TO
MASTER_HOST = host, -- 主服务器地址
MASTER_PORT = port, -- 主服务器端口
MASTER_USER = user, -- 前边创建的`repl`用户名
MASTER_PASSWORD = password, -- 前边创建的`repl`用户秘密
MASTER_AUTO_POSITION = 1, -- 需要从主服务器哪个GTID开始复制
MASTER_DELAY= delay_seconds; -- 延迟复制的时间（单位：秒）,默认0

-- MySQL 8.0.23及之后版本。
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = host, -- 主服务器地址
SOURCE_PORT = port, -- 主服务器端口
SOURCE_USER = user, -- 前边创建的`repl`用户名
SOURCE_PASSWORD = password, -- 前边创建的`repl`用户秘密
SOURCE_AUTO_POSITION = 1, -- 需要从主服务器哪个GTID开始复制
SOURCE_DELAY= delay_seconds; -- 延迟复制的时间（单位：秒）,默认0

-- 开始复制
STATR SLAVE;

复制延迟导致哪些线程被延迟了？

复制延迟只针对于从服务器，主服务器的事务提交和日志写入都是即时的。也就是说主服务器的I/O线程不受延迟影响，从服务器的I/O线程也不受延迟影响，只有SQL线程会被延迟。

为什么要使用复制延迟？

假设把从库设定为延迟主库1小时，当在主库上进行误操作后。在1小时内，可以通过将从库提升为主库，并重放部分中继日志，跳过误操作的语句进行恢复。

复制过滤

使用binlog-do-db和binglog-ignore-db选项，配置主服务器``Binlog写入规则。

# 复制db1
binlog_do_db=db1

# 复制db1和db2
binlog_do_db=db1
binlog_do_db=db2

#忽略db1
binlog_ignore_db=db1

#忽略db1和db2
binlog_ignore_db=db1
binlog_ignore_db=db2

binlog_do_db和binlog_ignore_db配置多个值（数据库），为什么要每个库一个配置？

MySQL当配置多个值时，使用，拼接，与MySQL数据库命名允许使用,相冲突，会导致MySQL将binlog_do_db和binlog_ignore_db的多个值当成一个值，进行处理。

binlog_do_db和binlog_ignore_db为什么不能像期望那样工作，比如：配置了binlog_do_db=db1，但是db1的更新没有被记录进日志？

binlog_do_db和binlog_ignore_db的生效方式与Binlog使用语句复制和行复制密切相关。

** 当Binlog使用语句复制时 **。
设置binlog_do_db=db1。MySQL只会记录默认数据库为db1的语句到Binlog。如果默认数据库不是db1，一概不写入二进制日志。
# 写入日志
USE db1;
UPDATE db1.january SET amount=amount+1000;

# 写入日志
USE db1;
UPDATE db2.january SET amount=amount+1000;

# 写入日志
USE db1;
UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;

# 不写入日志
USE db2;
UPDATE db1.january SET amount=amount+1000;

# 不写入日志
USE db2;
UPDATE db2.january SET amount=amount+1000;

# 不写入日志
USE db2;
UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;
设置binlog_ignore_db=db1。如果没有选择默认数据库（使用USE语句选择），该选项不生效（不过滤任何语句）；如果已选择默认数据库，过滤掉所有默认数据库是db1的语句。
# 不写入日志
USE db1;
UPDATE db1.january SET amount=amount+1000;

# 不写入日志
USE db1;
UPDATE db2.january SET amount=amount+1000;

# 写入日志
USE db2;
UPDATE db1.january SET amount=amount+1000;

# 写入日志
USE db2;
UPDATE db2.january SET amount=amount+1000;
** 当使用行复制时 **。
设置binlog_do_db=db1。MySQL只会记录对db1造成修改的行变更记录到Binlog。
# 写入日志
USE db1;
UPDATE db1.february SET amount=amount+100;

# 写入日志
USE db2;
UPDATE db1.february SET amount=amount+100;

# 将db1更改写入日志，其他不写
USE db1;
UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;

# 不写入日志
USE db2;
UPDATE db2.january SET amount=amount+1000;

# 将db1更改写入日志，其他不写
USE db2;
UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;
设置binlog_ignore_db=db1。过滤掉所有对db1进行更改的Binlog，不受默认数据库的影响（使用USE语句选择）。
# 不写入日志
USE db1;
UPDATE db1.january SET amount=amount+1000;

# 写入日志
USE db1;
UPDATE db2.january SET amount=amount+1000;

# 不写入日志
USE db2;
UPDATE db1.january SET amount=amount+1000;

# 写入日志
USE db2;
UPDATE db2.january SET amount=amount+1000;
replicate_do_db和binlog_do_db规则一致；replicate_ignore_db和replicate_ignore_db规则一致。

使用binlog-do-db和binglog-ignore-db选项，配置从服务器``Binlog重放规则。

# 复制db1
replicate_do_db=db1

# 复制db1和db2
replicate_do_db=db1
replicate_do_db=db2

#忽略db1
replicate_ignore_db=db1

#忽略db1和db2
replicate_ignore_db=db1
replicate_ignore_db=db2