如何在 MySQL 中设置复制

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

介绍

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

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

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

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

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

先决条件

要完成本指南，您需要：

  • 两台运行 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 地址。如果规则添加成功，您将看到以下输出：

Output
Rule added

之后，您无需对副本的防火墙规则进行任何更改，因为副本服务器不会接收任何传入连接，并且 UFW 不会阻止与源 MySQL 服务器的传出连接。您可以继续更新源 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地址上的连接。换句话说，这个 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，但如果您要复制的源上有一个现有数据库，请使用其名称代替db：

/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 USERandGRANT语句而缓存的任何内存：

  • FLUSH PRIVILEGES;

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

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

回想一下在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;

Output
Query OK, 1 row affected (0.01 sec)

之后，关闭 MySQL shell：

  • exit

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

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

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

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

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

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

  • ssh sammy@source_server_ip

然后，从新选项卡或窗口，使用mysqldump. 下面的示例创建一个db.sql从名为的数据库命名的转储文件db，但请确保包含您自己的数据库的名称。另外，请务必在 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 步 – 配置副本数据库

剩下要做的就是像更改源一样更改副本的配置。mysqld.cnf这次在您的副本服务器上打开 MySQL 配置文件：

  • 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 复制设置。运行此命令后，一旦你在这种情况下启用复制，它会尝试连接到IP地址以下SOURCE_HOST使用的用户名和密码之后SOURCE_USER，并SOURCE_PASSWORD分别。它还将查找具有以下名称的二进制日志文件，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)

  • );

Output
Query 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');

Output
Query 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 相关内容库。

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁