Author：Arsen
Date：2024/06/26

---

---

前言

不同的 MySQL 复制架构是有不同的意义的，也不是能随便乱用的，比如主从、主备、HA 高可用等复制架构，不同的架构的应用场景是不一样的，接下来，分别谈谈这些架构在实际生产中应用的注意事项。

实际上，复制架构的基础模式有如下几种：

• 主从模式：A => B
• 主主模式：A <=> B
• 链式复制：A => B => C
• 环形复制：A => B => C => A

生产环境中一般建议部署为主从模式，这也是最稳妥的一种复制架构。为了提升高可用性，也可选择主主模式，需要注意的是，主主模式必须确保任何时刻都只有一个数据库是主动（Active）状态，也就是说同一个时刻只能写入一个主（Master）节点，否则可能导致数据异常。链式或环形复制在生产中很少用到，它们的主要缺点在于，随着节点的增加，整个复制系统的稳健性会下降。

一、参数设置

1.1 slave_exec_mode

1、参数解释

slave_exec_mode 参数用于控制MySQL从服务器如何执行复制事件。它决定了从服务器如何应用来自主服务器的binlog事件。具体而言，它可以设置为不同的值来影响事务的执行方式和并行复制的行为。

2、参数默认值

默认为值为 STRICT（严格模式），在这种模式下，从库会严格地执行所有的复制事件，不会忽略任何错误。这意味着如果复制事件执行中出现了任何错误，从库将会停止执行并记录错误，需要手动修复问题后才能继续。

可选参数有 IDEMPOTENT（幂等模式），在这种模式下，从库会忽略复制事件执行过程中可能出现的一些特定错误，例如唯一键冲突或找不到键值。这有助于确保从库可以忽略那些可能是由于之前已经执行过的复制事件而导致的错误。

3、在主从复制中的设置

根据你的复制模式来设置即可。该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
slave_exec_mode = STRICT

IDEMPOTENT 参数值一般在主主配置、环形复制等其他特殊情况下才使用，否则使用默认值即可。

1.2 max_allowed_packet

1、参数解释

max_allowed_packet 参数用于控制单个数据库连接能够处理的最大数据包大小（以字节为单位），如果一个查询生成的结果或操作的数据包大小超过了此限制，MySQL 将会报错并拒绝执行操作。

2、参数默认值

默认的 max_allowed_packet 值因版本和配置而异，通常情况下，默认值为 4MB（4 * 1024 * 1024 字节）。

# 查询当前系统中的默认值
SHOW VARIABLES LIKE 'max_allowed_packet';

3、在主从复制中的设置

该参数在主服务器（Master）、从服务器（Slave）中都要设置。

生产环境中建议配置大于16MB，如果太小了，可能会导致从库不能接收主库发过来的包，主从建议设置成一样的值。

[mysqld]
max_allowed_packet = 16M 

1.3 binlog-do-db

1、参数解释

binlog-do-db 参数用于指定哪些数据库的操作要记录到二进制日志（binary log）中。建议不要使用该复制过滤参数，因为二进制日志中记录了数据库的所有修改操作，包括插入、更新和删除等，它对于主从复制和恢复操作非常重要。

比如，当你指定了 binlog-do-db=account，那只有 account 这个库的所有操作记录会记录到 binlog 日志中（但前提是你先要 USE 到 account 才生效），对其他库的操作记录均不会记录到 binlog 日志中，这其实是不安全的。

也就是说，如果设置了 binlog-do-db=db_name1，那你必须要先 USE db_name1;，然后再执行UPDATE db_name1.sum SET nu=nu+10;等相关增删操作，此时才会将这个操作事务记录到 binlog 日志中。如果你的事务是先 USE db_name2;，然后再执行UPDATE db_name1.sum SET nu=nu+10;等相关增删操作，那这条记录是不会记录到 binlog 日志的，尽管你是对 db_name1 的操作，因为我们相当于已经跨数据库操作了，记住，只要跨库了，参数就失效了。

2、参数默认值

默认情况下，binlog-do-db 参数没有设置，即不限制特定数据库，会记录所有数据库的操作。

3、在主从复制中的设置

该参数在主服务器（Master）中配置，如果你使用的是主主模式，那从服务器（Slave）也要设置。

[mysqld]
binlog-do-db=db_name1
binlog-do-db=db_name2

该配置表明只对 db_name1 和 db_name1 库的操作才记录到 binlog 日志，但是不建议设置这个参数。

1.4 binlog-ignore-db

1、参数解释

binlog-do-db 参数在 MySQL 5.7 版本之后已被弃用，使用了更为灵活的 binlog-ignore-db 参数来达到类似的效果。

binlog-ignore-db 参数用于指定哪些数据库的操作不记录到二进制日志（binary log）中。通过设置这个参数，可以忽略某些数据库的所有修改操作，使这些操作不出现在二进制日志中。该参数常用于主从复制中，控制哪些数据库的操作需要复制到从服务器。

同理，跨库的操作，参数也是失效的。

2、参数默认值

默认情况下，binlog-ignore-db 参数没有设置，即所有数据库的操作都会记录到二进制日志中。

3、在主从复制中的设置

该参数在主服务器（Master）中配置，如果你使用的是主主模式，那从服务器（Slave）也要设置。

[mysqld]
binlog-ignore-db=db_name1
binlog-ignore-db=db_name2

该配置表明 db_name1、db_name2 数据库的修改操作将不会记录到二进制日志中，当然也就不会复制到从服务器了，但是不建议设置这个参数。

1.5 replicate-ignore-db

1、参数解释

replicate-ignore-db 参数用于指定在主从复制中从服务器（Slave）应忽略的数据库。这意味着在从服务器上，不会执行来自主服务器的与这些数据库相关的任何更改。这是在从服务器端进行的过滤操作，而不是在主服务器端。

例如，我在 slave 从库中的配置文件上配置了：

[mysqld]
replicate-ignore-db=capital

接着，我在 master 主库中执行了以下 sql 语句：

UPDATE account.total SET sum=sum+10;
UPDATE capital.total SET sum=sum+10;

此时，从服务器（Slave）会正常复制 UPDATE account.total SET sum=sum+10;语句，而忽略 UPDATE capital.total SET sum=sum+10; 语句。

但是要注意，replicate-ignore-db 参数只会影响从服务器的行为，主服务器的二进制日志仍然会记录所有数据库的操作。

与 1.3、1.4 参数同理，如果存在跨库更新的情况，参数也是失效的。

2、参数默认值

默认情况下，replicate-ignore-db 参数没有设置，即从服务器会复制主服务器的所有数据库的操作。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
replicate-ignore-db=db_name1
replicate-ignore-db=db_name2

此时，从服务器不会复制与 db_name1、db_name2 库的所有操作记录，因为根据主从复制的原理我们不难理解。

1）IO 线程的工作原理

• 从服务器上的 IO 线程负责连接到主服务器并读取主服务器的二进制日志（binlog）；
• IO 线程将读取到的 binlog 写入从服务器的中继日志（relay log）。

2）SQL 线程的工作原理

• 从服务器上的 SQL 线程负责读取中继日志（relay log）并将其中的更改应用到从服务器的数据库上；
• 当 SQL 线程读取中继日志中的事件时，它会根据从服务器的复制过滤参数（如 replicate-ignore-db）决定是否应用这些事件，但是这里也要看是否是跨库的更新事务，如果是，依然会应用这些事件，否则就正常过滤。

当然了，你从库 replicate-ignore-db 指定的库的操作也是不会写入到从库 binlog 日志中的，因为从库压根就没执行回放到本地数据库的操作，我们说只有开启了 binlog 记录，且没有设置 binlog-ignore-db、binlog-ignore-db binlog 日志写入的过滤参数时，对数据库的操作事务才会记录到 binlog 日志中。

1.6 replicate-ignore-table

1、参数解释

replicate-ignore-table 参数用于在 MySQL 主从复制中，指定从服务器忽略某些表的复制。这个参数允许使用通配符模式来指定要忽略的表，不支持通配符，可跨库更新。

2、参数默认值

默认情况下，replicate-ignore-table 参数未设置，即不忽略任何表。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
# 忽略复制某些具体的表
replicate-wild-ignore-table=exampledb.table1
replicate-wild-ignore-table=exampledb.table2
replicate-wild-ignore-table=exampledb.table3

1.7 replicate-wild-ignore-table

1、参数解释

replicate-wild-ignore-table 参数用于在 MySQL 主从复制中，指定从服务器忽略某些表的复制。这个参数允许使用通配符模式来指定要忽略的表，支持通配符，通配符模式可以使用 % 和 _，分别表示任意字符和单个字符，可跨库更新，因此，如果你要想要忽略对某个库的复制（跨库），该参数就是解决方案。

2、参数默认值

默认情况下，replicate-wild-ignore-table 参数未设置，即不忽略任何表。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
# 忽略复制某个具体的表
replicate-wild-ignore-table=exampledb.excluded_table

# 忽略复制某个数据库中的所有表
replicate-wild-ignore-table=exampledb.%

# 忽略复制多个表或模式
replicate-wild-ignore-table=anotherdb.table_to_ignore
replicate-wild-ignore-table=anotherdb.prefix_%

1.8 slave_compressed_protocol

1、参数解释

slave_compressed_protocol 参数决定在主从复制过程中，从服务器是否使用压缩协议与主服务器进行通信。启用该参数可以在网络带宽有限或网络延迟较高的环境中显著降低网络传输的数据量，从而提高复制性能。

在夸集群复制时，该参数是比较有用的。参数启用后，会在主库进行压缩，然后在从库解压缩，启用压缩协议可能会增加 CPU 使用率，因为压缩和解压缩数据需要额外的计算资源。因此，在启用该参数前，确保你的网络环境确实需要数据压缩，以避免不必要的性能开销。

通过合理配置 slave_compressed_protocol，可以在一定程度上优化主从复制的网络性能，特别是在网络带宽受限的情况下。

2、参数默认值

默认情况下为 OFF，即禁用压缩协议，ON 为可选参数，表示启用压缩协议。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
slave_compressed_protocol=OFF

1.9 read-only

1、参数解释

read-only 参数用于指示 MySQL 服务器是否允许进行写操作。当数据库设置为只读模式时，所有的写操作将被拒绝，只有读操作可以执行，这对于确保数据的安全性和避免意外的写入操作非常有用，而且在做读写分离的时候，我们从库一般也会启用 read-only 只读权限。

2、参数默认值

默认情况下，read-only 参数为 OFF，即数据库允许读和写操作，ON 为可选参数，设置为 ON 后，数据库将进入只读模式，不允许进行写操作。

3、在主从复制中的设置

可以考虑为从库配置 read-only 选项，以保障数据安全，但要注意 SUPER 权限的用户仍然可以写数据库。

如果你使用普通主从复制或链式复制，该参数在从服务器（Slave）中配置：

[mysqld]
read-only=ON

注意：如果你使用的是主主模式或环形模式，那主服务器（Master）、从服务器（Slave）就不能设置了，因为你设置了，如果你的主挂掉，切换到备后，被就为只读了，那应用程序就无法写入数据，造成你的平台无法对外提供服务了。

1.10 slave_net_timeout

1、参数解释

slave_net_timeout 参数用于定义从服务器与主服务器之间网络连接的超时时间。在主从复制中，从服务器通过网络从主服务器获取二进制日志数据。这个参数控制了在没有从主服务器接收到数据时从服务器等待的时间。

设置较小的 slave_net_timeout 值可能导致从服务器在网络连接出现问题时更快地超时并放弃连接尝试，从而加快故障检测和故障转移过程。相反，设置较大的值则可能导致从服务器在网络连接问题时等待更长的时间。

2、参数默认值

默认情况下，slave_net_timeout 参数的值为 3600 秒（1 小时），建议将其设置小于1分钟。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
slave_net_timeout = 60

1.11 slave-skip-errors

1、参数解释

通常情况下，slave_exec_mode 如果保持默认的话，从库会严格地执行所有的复制事件，不会忽略任何错误。这意味着如果复制事件执行中出现了任何错误，从库将会停止执行并记录错误，需要手动修复问题后才能继续。

slave-skip-errors 参数用于主从复制过程中遇到特定类型的错误时，从服务器是否应该跳过这些错误并继续执行复制。这可以用于处理一些特定的复制错误而不中断整个复制流程。

2、参数默认值

默认情况下，slave-skip-errors 参数未定义，即未设置默认值。

可以设置为一个字符串，包含一个或多个 MySQL 错误代码。多个错误代码之间用逗号分隔。例如，1062（主键冲突）,1053（找不到键） 表示在遇到错误代码为 1062 或 1053 时跳过错误。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
slave-skip-errors = 1062,1053

如果你不知道为什么会发生复制错误，那么请不要使用该选项。如果复制设置和客户程序中没有Bug，并且MySQL自身也没有Bug，那么应该是不会发生停止复制的错误的。滥用该选项会使从服务器与主服务器不能保持同步，将会导致数据不一致。

1.12 skip-slave-start

1、参数解释

skip-slave-start是一个全局参数，用于控制 MySQL 服务器是否启动从服务器线程。如果设置为 skip-slave-start=1，则 MySQL 服务器启动时不会启动从服务器线程，即不会进行主从复制。相反，如果设置为skip-slave-start=0（或者在配置文件中不设置该参数），则从服务器线程会启动，MySQL服务器会尝试连接到主服务器并开始复制。

2、参数默认值

默认情况下，skip-slave-start参数未显式设置时，默认值是0，表示从服务器线程会被启动，MySQL服务器会进行主从复制。

可选参数值 1，表示从服务器线程不会启动，主从复制不会进行。

3、在主从复制中的设置

该参数在从服务器（Slave）中配置，如果你使用的是主主模式，那主服务器（Master）也要设置。

[mysqld]
skip-slave-start=0

小结

• 如果主主(备)复制或环形复制
  • slave_exec_mode 建议设置为 IDEMPOTENT；
  • max_allowed_packet 建议设置为 16M 或更高；
  • slave_compressed_protocol 保持 OFF 默认即可（跨集群复制时可评估服务器性能，然后再考虑打开）；
  • read-only 需设置为 OFF，即保持默认；
  • slave-skip-errors 建议不设置（除非你完全清楚需要过滤的错误）
  • skip-slave-start 建议设置为 0，即保持默认；
  • 强烈不建议设置 binlog-do-db、binlog-ignore-db、replicate-ignore-db、replicate-ignore-table、replicate-wild-ignore-table 参数，如果非要设置，建议通过 replicate-wild-ignore-table 参数来过滤即可，因为该参数既可以支持跨库更新，也支持通配符，较为灵活。
• 如果普通主从复制或链式复制
  • slave_exec_mode 建议设置为 STRICT 默认即可；
  • max_allowed_packet 设置为 16M 或更高；
  • slave_compressed_protocol 保持 OFF 默认即可（跨集群复制时可评估服务器性能，然后再考虑打开）；
  • read-only 建议置为 ON，启用只读模式，保证数据安全；
  • slave-skip-errors 建议不设置（除非你完全清楚需要过滤的错误）
  • skip-slave-start 建议设置为 0，即保持默认；
  • 强烈不建议设置 binlog-do-db、binlog-ignore-db、replicate-ignore-db、replicate-ignore-table、replicate-wild-ignore-table 参数，如果非要设置，建议通过 replicate-wild-ignore-table 参数来过滤即可，因为该参数既可以支持跨库更新，也支持通配符，较为灵活。

二、复制模式

2.1 主从模式

2.1.1 场景

1、主从模式：A => B

• 读写分离

  主从模式最常见的应用场景是实现读写分离。主服务器（A）负责处理写操作和读操作，而从服务器（B）复制主服务器的数据，并且只用于处理读操作。这样可以减轻主服务器的负载，提高系统的整体性能和稳定性。

• 数据备份

  从服务器（B）可以用于备份数据，而不会影响主服务器（A）的性能和可用性。

• 高可用性

  如 MHA 实现 1 主多从（至少 2 从）的高可用架构，具体看《基于 MHA 的 MySQL 高可用主从架构》。

2、链式复制：A => B => C

• 数据分布和容灾

  链式复制适用于需要将数据分布到多个地理位置或数据中心的场景。例如，主服务器（A）位于一个数据中心，从服务器（B）位于另一个数据中心，从服务器（B）又作为另一个从服务器（C）的主服务器，这样可以实现数据在多个地点的复制和容灾。

• 跨地域部署

  在全球化应用中，链式复制可以帮助减少数据传输的延迟，并提供地理位置的容灾备份。

• 数据分析

  链式复制也可以用于数据分析场景，其中从服务器（B）可以用于生产环境的读写操作，而从服务器（C）则专门用于数据分析和报表生成，避免对主服务器（A）和从服务器（B）的影响。

2.1.1 搭建

2.1.1.1 基于主库数据制作从库

A => B

流程：部署新 MySQL 实例（B），将现有的主库（A）的数据 mysqldump 下来，并将 mysqldump 下来的数据导入新部署的 MySQL 实例（B）。

1、从实例部署

2、主库执行 mysqldump 命令，导出 SQL 文件

mysqldump -u root -p -P 3306 --flush-logs --master-data=2 --single-transaction --hex-blob -R -f --all-databases > /data/back/databases.sql

参数说明：

• -u：指定 MySQL 备份用户。

• -p：指定 MySQL 备份用户密码。

• -P：指定 MySQL 服务端口。

• --flush-logs：这个选项告诉MySQL在备份之前刷新（轮换）二进制日志。

• --master-data=2：这个选项会在备份文件中加入 CHANGE MASTER TO 语句，包含二进制日志文件名和位置信息，以便在恢复备份后重新配置主从复制，前提是启用了 binlog 日志功能，否则是没有 CHANGE MASTER TO 语句的。

• --single-transaction：这个选项确保在备份过程中使用一致性视图，而不会锁定整个数据库。这样可以避免备份期间数据库写操作的阻塞，保证备份的一致性。

• --hex-blob：这个选项指示 mysqldump 将 BLOB 字段的内容以十六进制形式输出到备份文件中，这样可以确保备份文件中二进制数据的正确性和可读性。

• -R(--routines)：这个选项用于备份存储过程和函数，如果数据库中有存储过程或函数，使用此选项可以确保备份文件包含这些存储过程和函数的定义和代码。

• -f(--force)：这个选项强制 mysqldump 继续生成备份，即使出现错误。通常用于确保即使某些表备份失败，也能生成尽可能完整的备份文件。

• --all-databases：这个选项指示 mysqldump 备份所有数据库，而不仅仅是一个指定的数据库。备份文件将包含系统中所有数据库的结构和数据。

• > /data/back/databases.sql：这部分是将备份输出重定向到指定路径的语法。

3、在从库上导入此 SQL 文件

# 如果之前启动了SLAVE，则关闭SLAVE
STOP SLAVE;

# 导入数据
mysql -uroot -p < /data/back/databases.sql

4、配置主从

根据导出来的 sql 文件，其中就记录了连接主库的语句（上图红框部分）

# 配置连接主库信息
CHANGE MASTER TO
MASTER_HOST='xxx.xxx.xxx.xxx',               # master的IP
MASTER_USER='test',                          # master创建的复制用户
MASTER_PASSWORD='Z****06',                   # master创建的复制用户密码
MASTER_PORT=3306,                            # master数据库监听端口
MASTER_LOG_FILE='mysql-binlog.000002',       # master_binlog日志
MASTER_LOG_POS=154;                          # master_binlog日志位置点

# 启动从服务器的复制进程
START SLAVE;

5、检查复制状态

# 执行检查命令，确保 IO 线程和 SQL 线程均为 Yes
SHOW SLAVE STATUS;

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

2.1.1.2 基于从库制作从库

A => B => C

流程：部署新 MySQL 实例（C），将现有的从库（B）的数据 mysqldump 下来，并将 mysqldump 下来的数据导入新部署的 MySQL 实例（C）。

如果不能关闭从库，那么我们一般采取关闭 Slave（B）的 SQL 线程，然后导出数据的方式制作从库。关闭从库的复制SQL线程后，从库将不再被更新，这个时候，可以认为我们获得了一个一致性的快照。

1、关闭 Slave（B）的 SQL 线程，并获取 SHOW SLAVE STATUS 的信息

STOP SLAVE SQL_THREAD;
SHOW SLAVE STATUS;

2、对于 SHOW SLAVE STATUS 命令的输出，我们关注的是如下两项

Relay_Master_Log_File
Exec_Master_Log_Pos

新的从库应该从主库的如上位置开始重新同步。

3、Slave（B）导出数据

mysqldump -u root -p -P 3306 --flush-logs --master-data=2 --single-transaction --hex-blob -R -f --all-databases > /data/back/databases.sql

4、重新启动 Slave（B）- 因为我们第 1 步就关闭 SQL 线程了

START SLAVE;

5、向新的 Slave（C）机器导入数据

mysql -uroot -p < /data/back/databases.sql

6、配置主从

# 配置连接主库信息
CHANGE MASTER TO
MASTER_HOST='xxx.xxx.xxx.xxx',               # Slave（C）的IP
MASTER_USER='test',                          # Slave（C）创建的复制用户
MASTER_PASSWORD='Z****06',                   # Slave（C）创建的复制用户密码
MASTER_PORT=3306,                            # Slave（C）数据库监听端口
MASTER_LOG_FILE='mysql-binlog.000002',       # Slave（C）的binlog日志（在第2步获取）
MASTER_LOG_POS=154;                          # Slave（C）的日志位置点（在第2步获取）

2.2 主主模式

2.2.1 场景

1、主主模式：A <=> B

• 高可用性

  主主复制模式可以提供高可用性，如一台服务器发生故障，会故障转移至另一台，从而保证服务的连续性，可通过 keepalived 实现。

• 地理分布和容灾备份

  当需要在不同地理位置或数据中心之间同步数据时，主主复制是一种常见的解决方案。

• 读写分离

  在一些场景下，主主复制也可以用于读写分离，使得不同服务器可以同时处理读和写操作。

• 数据同步和备份

  主主复制还可以用于数据同步和备份。每台服务器上的数据变更会自动同步到其他服务器上，从而确保数据的一致性和可用性。

2、环形复制：A => B => C => A

• 高可用性和容错性

  环形复制提供了较高的容错性和可用性。如果环中的任何一个服务器发生故障，系统仍然可以继续运行，因为数据变更可以通过环中的其他服务器进行传播。这种架构可以有效地减少单点故障的影响。

• 地理分布和异地容灾备份

  当需要在不同地理位置或数据中心之间保持数据同步时，环形复制可以作为一种异地容灾备份的解决方案。每个服务器都可以作为其他服务器的备份，任何地点的数据变更都可以在环中传播，从而保证数据的一致性和可用性。

• 增强的读写分离和负载均衡

  形复制结构可以支持更灵活的读写分离策略和负载均衡。通过适当配置和路由，可以将写操作路由到环中的某个节点，同时将读操作分布到其他节点，以提高整体数据库系统的性能和响应能力。

• 实时数据分析和报告

  每个节点在环形复制中都可以独立地进行数据分析和报告生成，而不会影响到其他节点的正常运行。这使得环形复制特别适合于需要实时数据处理和分析的应用场景。

2.2.2 搭建

与主从模式的搭建流程一致，对于主主模式，注意保持 MySQL 配置文件的一致性。

总结

这里总结一下，主要是自己的心得体会总结，以前对 MySQL 的主从其实都是一知半解，为了能够真正了解复制，今天特意做了如上总结，其实复制并没有我们想象那么简单，但其实也并不复杂，关键在于你是否真正去了解过复制过程中涉及到的配置参数，及这些配置参数的作用，如果你认真去了解这些参数的功能，你会发现，其实 MySQL 复制是很有趣的。加油！继续学习中！

–END