如何在MySQL中设置复制

本教程的先前版本由 Etel Sverdlov 编写。

介绍

使用数据库时，拥有多个数据副本会很有用。这在其中一个数据库服务器发生故障时提供了冗余，并且可以提高数据库的可用性、可伸缩性和整体性能。跨多个独立数据库同步数据的做法称为复制。

MySQL是一个关系型数据库管理系统，是当今世界上最流行的开源关系型数据库。它安装了许多内置的复制功能，允许您维护数据的多个副本。

本教程概述了如何将一台服务器上的 MySQL 实例配置为源数据库，然后将另一台服务器上的 MySQL 实例配置为其副本。它还包括对 MySQL 如何处理复制的概述。

注：历史上，这种类型的数据库复制被称为“主从”复制。在 2020 年 7 月发布的博文中，MySQL 团队承认该术语的负面来源，并宣布他们努力更新数据库程序及其文档以使用更具包容性的语言。

然而，这是一个持续的过程。尽管 MySQL 的文档和程序版本 8 中的许多命令已更新，以将复制拓扑中的服务器称为源及其副本 [X212X ]，有些地方仍然会出现负面术语。本指南将尽可能默认使用更具包容性的 source-replica 术语，但在某些情况下不可避免地会出现旧术语。

先决条件

要完成本指南，您需要：

两台运行 Ubuntu 20.04 的服务器。两者都应该有一个具有 sudo 权限的非 root 管理用户和一个配置了 UFW 的防火墙。按照我们的 Ubuntu 20.04 初始服务器设置指南设置两台服务器。
MySQL 安装在每台服务器上。本指南假定您使用的是默认 Ubuntu 存储库中提供的最新版本的 MySQL，在撰写本文时，该版本是 8.0.25 版本。要在两台服务器上安装它，请按照我们关于如何在 Ubuntu 20.04 上安装 MySQL 的指南进行操作。

请注意，本指南中概述的过程涉及将一台服务器上的 MySQL 安装指定为 源数据库 ，然后将另一台服务器上的 MySQL 安装配置为源的副本。为了清楚起见，必须在源数据库服务器上运行的任何命令都将具有蓝色背景，如下所示：

同样，必须在副本 MySQL 实例的服务器上运行的任何命令都将具有红色背景：

最后，本教程包含有关如何将现有数据库中的数据从源迁移到副本的可选说明。此过程涉及创建源数据库的快照并将生成的文件复制到副本。为此，我们建议您在源服务器服务器上设置 SSH 密钥，然后确保已将源的公钥复制到副本中。

了解 MySQL 中的复制

在 MySQL 中，复制涉及源数据库将对一个或多个数据库中保存的数据所做的每一次更改记录在称为 二进制日志 的特殊文件中。一旦副本实例被初始化，它就会创建两个线程进程。第一个称为 IO 线程 ，连接到源 MySQL 实例并逐行读取二进制日志事件，然后将它们复制到副本服务器上称为 中继日志的本地文件。第二个线程，称为 SQL 线程 ，从中继日志中读取事件，然后尽快将它们应用到副本实例。

MySQL 的最新版本支持两种复制数据的方法。这些复制方法之间的区别与副本如何跟踪它们已处理的源中的哪些数据库事件有关。

MySQL将其传统的复制方法称为二进制日志文件位置复制。当您使用此方法将 MySQL 实例转换为副本时，您必须为其提供一组二进制日志坐标。这些包括副本必须读取的源上的二进制日志文件的名称以及该文件中的特定位置，该位置表示副本应复制到其自己的 MySQL 实例的第一个数据库事件。

这些坐标很重要，因为副本会收到其源的整个二进制日志的副本，如果没有正确的坐标，它们将开始复制其中记录的每个数据库事件。如果您只想在某个时间点之后复制数据或只想复制源数据的子集，这可能会导致问题。

基于二进制日志文件位置的复制在许多用例中都是可行的，但这种方法在更复杂的设置中可能会变得笨拙。这导致了 MySQL 更新的本机复制方法的开发，有时称为 基于事务的复制 。此方法涉及为源 MySQL 实例执行的每个事务（或由数据库执行的隔离工作）创建一个全局事务标识符 (GTID)。

基于事务的复制机制类似于基于二进制日志文件的复制：每当源上发生数据库事务时，MySQL 会在二进制日志文件中分配并记录事务的 GTID 以及事务本身。然后将 GTID 和事务传输到源的副本以供它们处理。

MySQL 的基于事务的复制与传统的复制方法相比有很多好处。例如，由于源及其副本都保留 GTID，因此如果源或副本遇到具有其已处理的 GTID 的事务，他们将跳过该事务。这有助于确保源与其副本之间的一致性。此外，使用基于事务的复制副本不需要知道要处理的下一个数据库事件的二进制日志坐标。这意味着在复制链中启动新副本或更改副本顺序要简单得多。

请记住，这只是对 MySQL 如何处理复制的一般解释； MySQL 提供了许多选项，您可以对其进行调整以优化您自己的复制设置。本指南概述了如何设置基于二进制日志文件位置的复制。但是，如果您对配置不同类型的复制环境感兴趣，我们鼓励您查看 MySQL 的官方文档。

第 1 步 — 调整源服务器的防火墙

假设您遵循先决条件 Initial Server Setup Guide，您将使用 UFW 在两台服务器上配置防火墙。这将有助于确保您的两个服务器的安全，但源的防火墙将阻止来自您的副本 MySQL 实例的任何连接尝试。

要更改这一点，您需要包含一个 UFW 规则，该规则允许从您的副本通过源的防火墙进行连接。您可以通过在源服务器 上运行类似以下 的命令来执行此操作。

此特定命令允许任何源自副本服务器 IP 地址的连接（由 replica_server_ip 表示）到 MySQL 的默认端口号 3306：

sudo ufw allow from replica_server_ip to any port 3306

请务必将 replica_server_ip 替换为您的副本服务器的实际 IP 地址。如果规则添加成功，您将看到以下输出：

OutputRule added

之后，您不需要对副本的防火墙规则进行任何更改，因为副本服务器不会接收任何传入连接，并且到源 MySQL 服务器的传出连接不会被 UFW 阻止。您可以继续更新源 MySQL 实例的配置以启用复制。

第 2 步 — 配置源数据库

为了让您的源 MySQL 数据库开始复制数据，您需要对其配置进行一些更改。

在 Ubuntu 20.04 上，默认的 MySQL 服务器配置文件名为 mysqld.cnf，可以在 /etc/mysql/mysql.conf.d/ 目录中找到。使用您喜欢的文本编辑器在源服务器 上打开此文件 。在这里，我们将使用 nano：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在文件中，找到 bind-address 指令。默认情况下它看起来像这样：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
bind-address            = 127.0.0.1
. . .

127.0.0.1 是表示 localhost 的 IPv4 环回地址，并将其设置为 bind-address 指令的值指示 MySQL 仅侦听 localhost[ 上的连接X199X] 地址。换句话说，这个 MySQL 实例将只能接受来自安装它的服务器的连接。

请记住，您正在将另一个 MySQL 实例转换为该实例的副本，因此该副本必须能够读取写入源安装的任何新数据。为此，您必须将源 MySQL 实例配置为侦听副本能够访问的 IP 地址上的连接，例如源服务器的公共 IP 地址。

将 127.0.0.1 替换为 源服务器的 IP 地址。这样做之后，bind-address 指令将如下所示，用您自己的服务器的 IP 地址代替 source_server_ip：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
bind-address            = source_server_ip
. . .

接下来，找到 server-id 指令，它定义了 MySQL 在内部用于区分复制设置中的服务器的标识符。复制环境中的每个服务器，包括源及其所有副本，都必须有自己唯一的 server-id 值。默认情况下，该指令将被注释掉，如下所示：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
# server-id             = 1
. . .

通过删除井号 (#) 取消注释此行。您可以选择任何数字作为该指令的值，但请记住该数字必须是唯一的，并且不能与复制组中的任何其他 server-id 匹配。为了简单起见，以下示例将此值保留为默认值，1：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
server-id               = 1
. . .

在 server-id 行下方，找到 log_bin 指令。这定义了 MySQL 二进制日志文件的基本名称和位置。

注释掉时，由于默认情况下该指令是禁用的，因此二进制日志记录被禁用。您的副本服务器必须读取源的二进制日志文件，以便知道何时以及如何复制源的数据，因此取消注释此行以在源上启用二进制日志记录。这样做后，它将如下所示：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
log_bin                       = /var/log/mysql/mysql-bin.log
. . .

最后，向下滚动到文件底部以找到注释掉的 binlog_do_db 指令：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
# binlog_do_db          = include_database_name

删除井号以取消注释此行并将 include_database_name 替换为要复制的数据库的名称。此示例显示 binlog_do_db 指令指向名为 db 的数据库，但如果您的源上有一个想要复制的现有数据库，请使用其名称代替 [X192X ]：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
binlog_do_db          = db

注意：如果要复制多个数据库，可以为要添加的每个数据库添加另一个 binlog_do_db 指令。本教程将继续仅复制单个数据库，但如果您想复制更多，它可能如下所示：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
binlog_do_db          = db
binlog_do_db          = db_1
binlog_do_db          = db_2

或者，您可以通过为每个数据库添加 binlog_ignore_db 指令来指定 MySQL 不应复制的数据库：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
binlog_ignore_db          = db_to_ignore

进行这些更改后，保存并关闭文件。如果您使用 nano 编辑文件，请按 CTRL + X、Y，然后按 ENTER。

然后通过运行以下命令重新启动 MySQL 服务：

sudo systemctl restart mysql

这样，这个 MySQL 实例就可以用作其他 MySQL 服务器将复制的源数据库。但是，在配置副本之前，您还需要在源上执行几个步骤，以确保您的复制拓扑能够正常运行。首先是创建一个专用的 MySQL 用户，该用户将执行与复制过程相关的任何操作。

第 3 步 — 创建复制用户

MySQL 复制环境中的每个副本都使用用户名和密码连接到源数据库。副本可以使用源数据库上存在并具有适当权限的任何 MySQL 用户配置文件进行连接，但本教程将概述如何为此目的创建专用用户。

首先打开 MySQL shell：

sudo mysql

注意：如果您配置了使用密码进行身份验证的专用 MySQL 用户，则可以使用如下命令连接到 MySQL：

mysql -u sammy -p

将 sammy 替换为您的专用用户的名称，并在出现提示时输入该用户的密码。

请注意，本指南中的某些操作（包括必须在副本服务器上执行的一些操作）需要高级权限。因此，以管理用户身份连接可能会更方便，就像使用前面的 sudo mysql 命令一样。但是，如果您想在本指南中使用权限较低的 MySQL 用户，至少应该授予他们 CREATE USER、RELOAD、REPLICATION CLIENT、REPLICATION SLAVE , 和 REPLICATION_SLAVE_ADMIN 权限。

根据提示，创建一个新的 MySQL 用户。以下示例将创建一个名为 replica_user 的用户，但您可以随意命名。请务必将 replica_server_ip 更改为您的 副本服务器的公共 IP 地址 ，并将 password 更改为您选择的强密码：

CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

请注意，此命令指定 replica_user 将使用 mysql_native_password 身份验证插件。可以改用 MySQL 的默认身份验证机制 caching_sha2_password，但这需要在源和副本之间建立加密连接。这种设置最适合生产环境，但配置加密连接超出了本教程的范围。 MySQL 文档包含有关如何配置使用加密连接的复制环境的说明，如果您想设置的话。

创建新用户后，授予他们适当的权限。 MySQL 复制用户至少必须具有 REPLICATION SLAVE 权限：

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';

在此之后，最好运行 FLUSH PRIVILEGES 命令。这将释放服务器由于前面的 CREATE USER 和 GRANT 语句而缓存的所有内存：

FLUSH PRIVILEGES;

至此，您已完成在源 MySQL 实例上设置复制用户。但是， 不退出 MySQL shell。暂时保持打开状态，因为您将在下一步中使用它来获取有关源数据库二进制日志文件的一些重要信息。

第 4 步 — 从源中检索二进制日志坐标

回想一下 Understanding Replication in MySQL 部分，MySQL 通过从源的二进制日志文件中逐行复制数据库事件并在副本上实现每个事件来实现复制。当使用 MySQL 的二进制日志文件基于位置的复制时，您必须为副本提供一组坐标，这些坐标详细说明了源二进制日志文件的名称和该文件中的特定位置。然后，副本使用这些坐标来确定日志文件中应该开始复制数据库事件的点，并跟踪它已经处理了哪些事件。

此步骤概述了如何获取源实例的当前二进制日志坐标，以便将您的副本设置为从日志文件中的最新点开始复制数据。为了确保在您检索坐标时没有用户更改任何数据，这可能会导致问题，您需要锁定数据库以防止任何客户端在您获取坐标时读取或写入数据。您将很快解锁所有内容，但此过程将导致您的数据库经历一些停机时间。

从上一步结束时，您仍然应该打开源服务器的 MySQL shell。在提示符下，运行以下命令，该命令将关闭源实例上每个数据库中所有打开的表并锁定它们：

FLUSH TABLES WITH READ LOCK;

然后运行以下操作，它将返回源二进制日志文件的当前状态信息：

SHOW MASTER STATUS;

您将在输出中看到与此示例类似的表格：

Output+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这是副本将开始复制数据库事件的位置。记录 File 名称和 Position 值，因为稍后启动复制时将需要这些。

获取此信息后立即执行的操作取决于您的源数据库是否有任何想要迁移到副本的现有数据。跳至以下两个小节中最适合您的情况的小节。

如果您的源没有任何要迁移的现有数据

如果您的源 MySQL 实例是新安装的或没有任何要迁移到副本的现有数据，您可以此时解锁表：

UNLOCK TABLES;

如果您还没有这样做，您可以在 MySQL shell 仍然打开的情况下创建您选择复制的数据库。按照步骤 2 中给出的示例，以下操作将创建一个名为 db 的数据库：

CREATE DATABASE db;

OutputQuery OK, 1 row affected (0.01 sec)

之后，关闭 MySQL shell：

exit

之后，您可以继续下一步。

如果您的源具有要迁移的现有数据

如果您希望将源 MySQL 实例上的数据迁移到副本，您可以通过使用 mysqldump 实用程序创建数据库快照来实现。但是，您的数据库当前仍应处于锁定状态。如果您在同一窗口中进行任何新的更改，数据库将自动解锁。同样，如果您退出客户端，表格将自动解锁。

解锁表可能会导致问题，因为这意味着客户端可以再次更改数据库中的数据。这可能会导致您的数据快照与您刚刚检索到的二进制日志坐标不匹配。

为此，您必须在本地机器上打开一个新的终端窗口或选项卡，这样您就可以在不解锁 MySQL 的情况下创建数据库快照。

从新的终端窗口或选项卡，打开另一个SSH会话到托管您的源MySQL实例的服务器：

ssh sammy@source_server_ip

然后，从新选项卡或窗口中，使用 mysqldump 导出数据库。以下示例从名为 db 的数据库创建名为 db.sql 的转储文件，但请确保包含您自己的数据库的名称。此外，请务必在 bash shell 中运行此命令，而不是在 MySQL shell 中：

sudo mysqldump -u root db > db.sql

之后，您可以关闭此终端窗口或选项卡并返回到您的第一个窗口或选项卡，它应该仍然打开 MySQL shell。在 MySQL 提示符下，解锁数据库以使其再次可写：

UNLOCK TABLES;

然后你可以退出 MySQL shell：

exit

您现在可以将您的快照文件发送到您的副本服务器。假设您在源服务器上配置了 SSH 密钥并将源的公钥添加到副本的 authorized_keys 文件中，您可以使用 scp 命令安全地执行此操作，例如这：

scp db.sql sammy@replica_server_ip:/tmp/

请务必将 sammy 替换为您在副本服务器上创建的管理 Ubuntu 用户配置文件的名称，并将 replica_server_ip 替换为副本服务器的 IP 地址。另外，请注意，此命令将快照放置在副本服务器的 /tmp/ 目录中。

将快照发送到副本服务器后，通过 SSH 连接：

ssh sammy@replica_server_ip

然后打开 MySQL shell：

sudo mysql

在提示符下，创建您将从源复制的新数据库：

CREATE DATABASE db;

您无需创建任何表或使用任何示例数据加载此数据库。当您使用刚刚创建的快照导入数据库时，这一切都会得到解决。相反，退出 MySQL shell：

exit

然后导入数据库快照：

sudo mysql db < /tmp/db.sql

您的副本现在具有源数据库中的所有现有数据。您可以完成本指南的最后一步来配置您的副本服务器以开始复制对源数据库所做的新更改。

第 5 步 — 配置副本数据库

剩下要做的就是更改副本的配置，类似于更改源的配置。打开 MySQL 配置文件，mysqld.cnf，这次是在你的副本服务器上的：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

如前所述，复制设置中的每个 MySQL 实例都必须具有唯一的 server-id 值。找到副本的 server-id 指令，取消注释，并将其值更改为任何正整数，只要它与源的值不同即可：

/etc/mysql/mysql.conf.d/mysqld.cnf

server-id               = 2

之后，更新 log_bin 和 binlog_do_db 值，使它们与您在源计算机的配置文件中设置的值一致：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
log_bin                 = /var/log/mysql/mysql-bin.log
. . .
binlog_do_db            = db
. . .

最后，添加一个 relay-log 指令来定义副本的中继日志文件的位置。在配置文件末尾包含以下行：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
relay-log               = /var/log/mysql/mysql-relay-bin.log

进行这些更改后，保存并关闭文件。然后在副本上重新启动 MySQL 以实现新配置：

sudo systemctl restart mysql

重新启动 mysql 服务后，您终于可以开始从源数据库复制数据了。

第 6 步 — 启动和测试复制

此时，您的两个 MySQL 实例都已完全配置为允许复制。要开始从您的源复制数据，请在您的副本服务器 上打开 MySQL shell ：

sudo mysql

在提示符下，运行以下操作，同时配置多个 MySQL 复制设置。运行此命令后，一旦您在此实例上启用复制，它将尝试分别使用 SOURCE_USER 和 SOURCE_PASSWORD 后面的用户名和密码连接到 SOURCE_HOST 后面的 IP 地址。它还将查找名称在 SOURCE_LOG_FILE 之后的二进制日志文件，并从 SOURCE_LOG_POS 之后的位置开始读取它。

请务必将 source_server_ip 替换为源服务器的 IP 地址。同样，replica_user 和 password 应该与您在步骤 2 中创建的复制用户对齐； mysql-bin.000001 和 899 应该反映您在步骤 3 中获得的二进制日志坐标。

您可能希望在副本服务器上运行之前在文本编辑器中键入此命令，以便您可以更轻松地替换所有相关信息：

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source_server_ip',
SOURCE_USER='replica_user',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=899;

之后，激活副本服务器：

START REPLICA;

如果您正确输入了所有详细信息，此实例将开始复制对源上的 db 数据库所做的任何更改。

您可以通过运行以下操作查看有关副本当前状态的详细信息。此命令中的 \G 修饰符重新排列文本以使其更具可读性：

SHOW REPLICA STATUS\G;

此命令返回大量信息，这些信息在故障排除时会有所帮助：

Output*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 138.197.3.190
                  Source_User: replica_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 1273
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 729
        Relay_Source_Log_File: mysql-bin.000001
. . .

注意：如果您的副本在连接时出现问题或复制意外停止，则可能是源的二进制日志文件中的事件阻止了复制。在这种情况下，您可以运行 SET GLOBAL SQL_SLAVE_SKIP_COUNTER 命令以跳过您在上一个命令中定义的二进制日志文件位置之后的一定数量的事件。此示例仅跳过第一个事件：

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

之后，您需要再次启动副本：

START REPLICA;

此外，如果您需要停止复制，请注意您可以通过在副本实例上运行以下操作来完成：

STOP REPLICA;

您的副本现在正在从源复制数据。您对源数据库所做的任何更改都将反映在副本 MySQL 实例上。您可以通过在源数据库上创建示例表并检查它是否成功复制来测试这一点。

首先在源机器上打开 MySQL shell：

sudo mysql

选择您选择复制的数据库：

USE db;

然后在该数据库中创建一个表。以下 SQL 操作创建一个名为 example_table 的表，其中有一列名为 example_column：

CREATE TABLE example_table (
example_column varchar(30)
);

OutputQuery OK, 0 rows affected (0.03 sec)

如果您愿意，您还可以在此表中添加一些示例数据：

INSERT INTO example_table VALUES
('This is the first row'),
('This is the second row'),
('This is the third row');

OutputQuery OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

创建表并选择性地向其中添加一些示例数据后，返回到副本服务器的 MySQL shell 并选择复制的数据库：

USE db;

然后运行 SHOW TABLES 语句列出所选数据库中的所有表：

SHOW TABLES;

如果复制工作正常，您将看到刚刚添加到此命令输出中列出的源的表：

Output+---------------+
| Tables_in_db  |
+---------------+
| example_table |
+---------------+
1 row in set (0.00 sec)

此外，如果您在源上的表中添加了一些示例数据，您可以使用如下查询检查该数据是否也被复制：

SELECT * FROM example_table;

在 SQL 中，星号 (*) 是“所有列”的简写。所以这个查询本质上是告诉 MySQL 从 example_table 返回每一列。如果复制按预期工作，此操作将在其输出中返回该数据：

Output+------------------------+
| example_column         |
+------------------------+
| This is the first row  |
| This is the second row |
| This is the third row  |
+------------------------+
3 rows in set (0.00 sec)

如果这些操作中的任何一个都无法返回您添加到源的示例表或数据，则可能是您的复制配置中的某处有错误。在这种情况下，您可以运行 SHOW REPLICA STATUS\G 操作来尝试查找问题的原因。此外，您可以参考 MySQL 的关于排除复制问题的文档以获取有关如何解决复制问题的建议。

结论

通过完成本教程，您将设置一个 MySQL 复制环境，该环境使用 MySQL 的基于二进制日志文件位置的复制方法，具有一个源和一个副本。但请记住，本指南中概述的过程仅代表在 MySQL 中配置复制的一种方法。 MySQL 提供了许多不同的复制选项，您可以使用它们来生成针对您的需求优化的复制环境。还有一些第三方工具，例如 Galera Cluster，您可以使用它们来扩展 MySQL 的内置复制功能。

如果您对 MySQL 中复制的具体功能还有任何疑问，我们鼓励您查看 MySQL 关于该主题的官方文档。如果您想全面了解 MySQL，您还可以查看我们的整个 MySQL 相关内容库。