MySQL :: MySQL 8.4 参考手册 :: 20.5.6 将 MySQL Enterprise Backup 与组复制结合使用

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 20 组复制 / 20.5 组复制操作 / 20.5.6 将 MySQL Enterprise Backup 与组复制结合使用

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

20.5.6 使用 MySQL Enterprise Backup 与 Group Replication20.5.6 Using MySQL Enterprise Backup with Group Replication

MySQL 企业备份是一款针对 MySQL 服务器的商业授权备份工具，可通过 MySQL 企业版获得。本节说明如何使用 MySQL 企业备份对组复制成员进行备份并随后恢复。相同的技术也可以用于快速向组中添加新成员。MySQL Enterprise Backup is a commercially-licensed backup utility for MySQL Server, available with MySQL Enterprise Edition. This section explains how to back up and subsequently restore a Group Replication member using MySQL Enterprise Backup. The same technique can be used to quickly add a new member to a group.

使用 MySQL 企业备份备份组复制成员Backing up a Group Replication Member Using MySQL Enterprise Backup

备份 Group Replication 成员类似于备份独立的 MySQL 实例。以下说明假设您已经熟悉如何使用 MySQL Enterprise Backup 执行备份；如果不了解，请查看备份数据库服务器。还请注意授予备份管理员 MySQL 权限和在 Group Replication 中使用 MySQL Enterprise Backup 中描述的要求。Backing up a Group Replication member is similar to backing up a stand-alone MySQL instance. The following instructions assume that you are already familiar with how to use MySQL Enterprise Backup to perform a backup; if that is not the case, please review Backing Up a Database Server. Also note the requirements described in Grant MySQL Privileges to Backup Administrator and Using MySQL Enterprise Backup with Group Replication.

考虑以下由三个成员组成的组，s1、s2和s3，它们运行在同名的主机上：Consider the following group with three members, s1, s2, and s3, running on hosts with the same names:

mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| s1          |        3306 | ONLINE       |
| s2          |        3306 | ONLINE       |
| s3          |        3306 | ONLINE       |
+-------------+-------------+--------------+

使用 MySQL 企业备份，通过在其主机上发出命令来创建 s2 的备份，例如，以下命令：Using MySQL Enterprise Backup, create a backup of s2 by issuing on its host, for example, the following command:

s2> mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/backups/my.mbi_`date +%d%m_%H%M` \
		      --backup-dir=/backups/backup_`date +%d%m_%H%M` --user=root -p \
		      --host=127.0.0.1 backup-to-image

恢复故障成员Restoring a Failed Member

假设其中一个成员（以下示例中的 s3）不可修复地损坏。可以使用组成员 s2 的最新备份来恢复 s3。执行恢复的步骤如下：Assume one of the members (s3 in the following example) is irreconcilably corrupted. The most recent backup of group member s2 can be used to restore s3. Here are the steps for performing the restore:

将 s2 的备份复制到 s3 的主机上。 复制备份的具体方法取决于操作系统和可用工具。在此示例中，我们假设主机都是 Linux 服务器，并使用 SCP 在它们之间复制文件：Copy the backup of s2 onto the host for s3. The exact way to copy the backup depends on the operating system and tools available to you. In this example, we assume the hosts are both Linux servers and use SCP to copy the files between them:
```
s2/backups> scp my.mbi_2206_1429 s3:/backups
```
恢复备份。 连接到目标主机（在此例中为 s3 的主机），并使用 MySQL 企业备份恢复备份。步骤如下：Restore the backup. Connect to the target host (the host for s3 in this case), and restore the backup using MySQL Enterprise Backup. Here are the steps:
1. 如果损坏的服务器仍在运行，请先停止它。例如，在使用 systemd 的 Linux 发行版上：Stop the corrupted server, if it is still running. For example, on Linux distributions that use systemd:
```
s3> systemctl stop mysqld
```
2. 将损坏服务器数据目录中的两个配置文件 auto.cnf 和 mysqld-auto.cnf（如果存在）保存起来，通过将它们复制到数据目录外的安全位置。这是为了保留服务器的 UUID 和第 7.1.9.3 节，“持久化系统变量”（如果使用），这些在下面的步骤中需要用到。Preserve the two configuration files in the corrupted server's data directory, auto.cnf and mysqld-auto.cnf (if it exists), by copying them to a safe location outside of the data directory. This is for preserving the server's UUID and Section 7.1.9.3, “Persisted System Variables” (if used), which are needed in the steps below.
3. 删除 s3 数据目录中的所有内容。例如：Delete all contents in the data directory of s3. For example:
```
s3> rm -rf /var/lib/mysql/*
```
  如果系统变量 innodb_data_home_dir、innodb_log_group_home_dir 和 innodb_undo_directory 指向数据目录以外的任何目录，也应将它们清空，否则恢复操作会失败。If the system variables innodb_data_home_dir, innodb_log_group_home_dir, and innodb_undo_directory point to any directories other than the data directory, they should also be made empty; otherwise, the restore operation fails.
4. 将 s2 的备份恢复到 s3 所在的主机：Restore backup of s2 onto the host for s3:
```
s3> mysqlbackup --defaults-file=/etc/my.cnf \
  --datadir=/var/lib/mysql \
  --backup-image=/backups/my.mbi_2206_1429  \
  --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log
```
  Note
  上面的命令假设 s2 和 s3 上的二进制日志和中继日志具有相同的基本名称，并且位于两个服务器上的相同位置。如果这些条件不满足，您应该使用 --log-bin 和 --relay-log 选项将二进制日志和中继日志还原到 s3 上的原始文件路径。例如，如果您知道在 s3 上二进制日志的基本名称是 s3-bin，中继日志的基本名称是 s3-relay-bin，那么您的恢复命令应如下所示：The command above assumes that the binary logs and relay logs on s2 and s3 have the same base name and are at the same location on the two servers. If these conditions are not met, you should use the --log-bin and --relay-log options to restore the binary log and relay log to their original file paths on s3. For example, if you know that on s3 the binary log's base name is s3-bin and the relay-log's base name is s3-relay-bin, your restore command should look like:
```
mysqlbackup --defaults-file=/etc/my.cnf \
  --datadir=/var/lib/mysql \
  --backup-image=/backups/my.mbi_2206_1429  \
  --log-bin=s3-bin --relay-log=s3-relay-bin \
  --backup-dir=/tmp/restore_`date +%d%m_%H%M` copy-back-and-apply-log
```
  能够将二进制日志和中继日志恢复到正确的文件路径可以使恢复过程更容易；如果由于某种原因无法实现，请参见重建失败的成员以作为新成员重新加入。Being able to restore the binary log and relay log to the right file paths makes the restore process easier; if that is impossible for some reason, see Rebuild the Failed Member to Rejoin as a New Member.
为 s3 恢复 auto.cnf 文件。 要重新加入复制组，恢复的成员必须拥有它之前加入该组时使用的相同 server_uuid。通过将上述步骤 2 中保存的 auto.cnf 文件复制到恢复成员的数据目录中来提供旧的服务器 UUID。Restore the auto.cnf file for s3. To rejoin the replication group, the restored member must have the same server_uuid it used to join the group before. Supply the old server UUID by copying the auto.cnf file preserved in step 2 above into the data directory of the restored member.
Note
如果无法通过恢复故障成员的旧 auto.cnf 文件向已恢复的成员提供故障成员的原始 server_uuid，则必须让已恢复的成员作为新成员加入组；请参阅下面的重建故障成员以作为新成员重新加入的说明，了解如何操作。If you cannot supply the failed member's original server_uuid to the restored member by restoring its old auto.cnf file, you must let the restored member join the group as a new member; see instructions in Rebuild the Failed Member to Rejoin as a New Member below on how to do that.
为 s3 恢复 mysqld-auto.cnf 文件（仅在 s3 使用了持久化系统变量时需要）。 必须将用于配置失败成员的第 7.1.9.3 节，“持久化系统变量” 的设置提供给恢复的成员。这些设置可以在失败服务器的 mysqld-auto.cnf 文件中找到，你应在上面步骤 2 中已保存该文件。将文件恢复到恢复服务器的数据目录中。如未备份该文件，请参阅恢复持久化系统变量，了解应如何操作。Restore the mysqld-auto.cnf file for s3 (only required if s3 used persistent system variables). The settings for the Section 7.1.9.3, “Persisted System Variables” that were used to configure the failed member must be provided to the restored member. These settings are to be found in the mysqld-auto.cnf file of the failed server, which you should have preserved in step 2 above. Restore the file to the data directory of the restored server. See Restoring Persisted System Variables on what to do if you do not have a copy of the file.
启动已恢复的服务器。 例如，在使用 systemd 的 Linux 发行版上：Start the restored server. For example, on Linux distributions that use systemd:
```
systemctl start mysqld
```
Note
如果您正在恢复的服务器是一个主节点，请在恢复主节点中描述的步骤 在启动已恢复的服务器之前 执行。If the server you are restoring is a primary member, perform the steps described in Restoring a Primary Member before starting the restored server.
重启组复制。 例如，使用 mysql 客户端连接到已重启的 s3，并执行以下语句：Restart Group Replication. Connect to the restarted s3 using, for example, a mysql client, and issue the following statement:
```
mysql> START GROUP_REPLICATION;
```
在恢复的实例可以成为群组的在线成员之前，它需要应用在备份之后发生的群组事务；这是通过 Group Replication 的分布式恢复机制来实现的，并且该过程在执行 START GROUP_REPLICATION 语句后开始。要检查恢复实例的成员状态，请执行：Before the restored instance can become an online member of the group, it needs to apply any transactions that have happened to the group after the backup was taken; this is achieved using Group Replication's distributed recovery mechanism, and the process starts after the START GROUP_REPLICATION statement has been issued. To check the member status of the restored instance, issue:
```
mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| s1          |        3306 | ONLINE       |
| s2          |        3306 | ONLINE       |
| s3          |        3306 | RECOVERING   |
+-------------+-------------+--------------+
```
这显示 s3 正在应用事务以赶上群组。一旦它赶上群组的其他成员，其 member_state 会变为 ONLINE：This shows that s3 is applying transactions to catch up with the group. Once it has caught up with the rest of the group, its member_state changes to ONLINE:
```
mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| s1          |        3306 | ONLINE       |
| s2          |        3306 | ONLINE       |
| s3          |        3306 | ONLINE       |
+-------------+-------------+--------------+
```
Note
如果您正在恢复的服务器是主成员，一旦它与组同步并变为ONLINE，请执行恢复主成员结尾处描述的步骤，以恢复在启动服务器之前对其进行的配置更改。If the server you are restoring is a primary member, once it has gained synchrony with the group and become ONLINE, perform the steps described at the end of Restoring a Primary Member to revert the configuration changes you had made to the server before you started it.

该成员现在已从备份中完全恢复，并作为组的常规成员正常运行。The member has now been fully restored from the backup and functions as a regular member of the group.

重建失败的成员以作为新成员重新加入Rebuild the Failed Member to Rejoin as a New Member

有时，上面在恢复失败的成员中概述的步骤无法执行，例如，因为二进制日志或中继日志损坏，或者备份中根本没有这些日志。在这种情况下，请使用备份重建该成员，然后将其作为新成员添加到组中。在下面的步骤中，我们假设重建的成员名为s3，就像失败的成员一样，并且它运行在与s3相同的主机上：Sometimes, the steps outlined above in Restoring a Failed Member cannot be carried out because, for example, the binary log or relay log is corrupted, or it is just missing from the backup. In such a situation, use the backup to rebuild the member, and then add it to the group as a new member. In the steps below, we assume the rebuilt member is named s3, like the failed member, and that it runs on the same host as s3:

将 s2 的备份复制到 s3 的主机上。 复制备份的具体方法取决于操作系统和可用工具。在此示例中，我们假设主机都是 Linux 服务器，并使用 SCP 在它们之间复制文件：Copy the backup of s2 onto the host for s3 . The exact way to copy the backup depends on the operating system and tools available to you. In this example we assume the hosts are both Linux servers and use SCP to copy the files between them:
```
s2/backups> scp my.mbi_2206_1429 s3:/backups
```
恢复备份。 连接到目标主机（在此例中为 s3 的主机），并使用 MySQL 企业备份恢复备份。步骤如下：Restore the backup. Connect to the target host (the host for s3 in this case), and restore the backup using MySQL Enterprise Backup. Here are the steps:
1. 如果损坏的服务器仍在运行，请先停止它。例如，在使用 systemd 的 Linux 发行版上：Stop the corrupted server, if it is still running. For example, on Linux distributions that use systemd:
```
s3> systemctl stop mysqld
```
2. 如果在损坏的服务器数据目录中找到配置文件 mysqld-auto.cnf，请将其复制到数据目录之外的安全位置以保存它。这是为了保留服务器的 7.1.9.3 节，“持久化系统变量”，这些变量在后续操作中是需要的。Preserve the configuration file mysqld-auto.cnf, if it is found in the corrupted server's data directory, by copying it to a safe location outside of the data directory. This is for preserving the server's Section 7.1.9.3, “Persisted System Variables”, which are needed later.
3. 删除 s3 数据目录中的所有内容。例如：Delete all contents in the data directory of s3. For example:
```
s3> rm -rf /var/lib/mysql/*
```
  如果系统变量 innodb_data_home_dir、innodb_log_group_home_dir 和 innodb_undo_directory 指向数据目录以外的任何目录，也应将它们清空，否则恢复操作会失败。If the system variables innodb_data_home_dir, innodb_log_group_home_dir, and innodb_undo_directory point to any directories other than the data directory, they should also be made empty; otherwise, the restore operation fails.
4. 将 s2 的备份恢复到 s3 的主机上。通过这种方法，我们是在将 s3 重新构建为一个新成员，对于它，我们不需要也不想使用备份中的旧二进制日志和中继日志；因此，如果这些日志已包含在您的备份中，请使用 --skip-binlog 和 --skip-relaylog 选项将它们排除：Restore the backup of s2 onto the host of s3. With this approach, we are rebuilding s3 as a new member, for which we do not need or do not want to use the old binary and relay logs in the backup; therefore, if these logs have been included in your backup, exclude them using the --skip-binlog and --skip-relaylog options:
```
s3> mysqlbackup --defaults-file=/etc/my.cnf \
  --datadir=/var/lib/mysql \
  --backup-image=/backups/my.mbi_2206_1429  \
  --backup-dir=/tmp/restore_`date +%d%m_%H%M` \
  --skip-binlog --skip-relaylog \
  copy-back-and-apply-log
```
  Note
  如果您在备份中有健康的二进制日志和中继日志，并且可以无问题地将其转移到目标主机，建议按照上面恢复失败成员中描述的更简单的程序进行操作。If you have healthy binary log and relay logs in the backup that you can transfer onto the target host with no issues, you are recommended to follow the easier procedure as described in Restoring a Failed Member above.
为 s3 恢复 mysqld-auto.cnf 文件（仅在 s3 使用了持久化系统变量时需要）。 必须将用于配置失败成员的第 7.1.9.3 节，“持久化系统变量” 的设置提供给恢复的服务器。这些设置可以在失败服务器的 mysqld-auto.cnf 文件中找到，你应在上面步骤 2 中已保存该文件。将文件恢复到恢复服务器的数据目录中。如未备份该文件，请参阅恢复持久化系统变量，了解应采取的措施。Restore the mysqld-auto.cnf file for s3 (only required if s3 used persistent system variables). The settings for the Section 7.1.9.3, “Persisted System Variables” that were used to configure the failed member must be provided to the restored server. These settings are to be found in the mysqld-auto.cnf file of the failed server, which you should have preserved in step 2 above. Restore the file to the data directory of the restored server. See Restoring Persisted System Variables on what to do if you do not have a copy of the file.
Note
不要将损坏的服务器的auto.cnf文件恢复到新成员的数据目录——当重建的s3作为新成员加入组时，它将被分配一个新的服务器 UUID。Do NOT restore the corrupted server's auto.cnf file to the data directory of the new member—when the rebuilt s3 joins the group as a new member, it is going to be assigned a new server UUID.
启动已恢复的服务器。 例如，在使用 systemd 的 Linux 发行版上：Start the restored server. For example, on Linux distributions that use systemd:
```
systemctl start mysqld
```
Note
如果您正在恢复的服务器是一个主节点，请在恢复主节点中描述的步骤 在启动已恢复的服务器之前 执行。If the server you are restoring is a primary member, perform the steps described in Restoring a Primary Member before starting the restored server.
重新配置恢复的成员以加入组复制。 使用mysql客户端连接到恢复的服务器，并使用以下语句重置源和副本信息：Reconfigure the restored member to join Group Replication. Connect to the restored server with a mysql client and reset the source and replica information with the following statements:
```
mysql> RESET BINARY LOGS AND GTIDS;

mysql> RESET REPLICA ALL;
```
为了使恢复的服务器能够使用组复制的内置机制进行分布式恢复自动恢复，请配置服务器的gtid_executed变量。为此，请使用包含在s2备份中的backup_gtid_executed.sql文件，该文件通常恢复到恢复成员的数据目录下。禁用二进制日志，使用backup_gtid_executed.sql文件配置gtid_executed，然后通过使用mysql客户端执行以下语句重新启用二进制日志：For the restored server to be able to recover automatically using Group Replication's built-in mechanism for distributed recovery, configure the server's gtid_executed variable. To do this, use the backup_gtid_executed.sql file included in the backup of s2, which is usually restored under the restored member's data directory. Disable binary logging, use the backup_gtid_executed.sql file to configure gtid_executed, and then re-enable binary logging by issuing the following statements with your mysql client:
```
mysql> SET SQL_LOG_BIN=OFF;
mysql> SOURCE datadir/backup_gtid_executed.sql
mysql> SET SQL_LOG_BIN=ON;
```
然后，使用此处显示的 SQL 语句在成员上配置组复制用户凭据：Then, configure the Group Replication user credentials on the member using the SQL statements shown here:
```
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', 
    ->   SOURCE_PASSWORD='password'
    ->   FOR CHANNEL 'group_replication_recovery';
```
重启组复制。 使用您的 mysql 客户端向已恢复的服务器发出以下语句：Restart Group Replication. Issue the following statement to the restored server with your mysql client:
```
mysql> START GROUP_REPLICATION;
```
在恢复的实例可以成为群组的在线成员之前，它需要应用在备份之后发生的群组事务；这是通过 Group Replication 的分布式恢复机制来实现的，并且该过程在执行 START GROUP_REPLICATION 语句后开始。要检查恢复实例的成员状态，请执行：Before the restored instance can become an online member of the group, it needs to apply any transactions that have happened to the group after the backup was taken; this is achieved using Group Replication's distributed recovery mechanism, and the process starts after the START GROUP_REPLICATION statement has been issued. To check the member status of the restored instance, issue:
```
mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| s3          |        3306 | RECOVERING   |
| s2          |        3306 | ONLINE       |
| s1          |        3306 | ONLINE       |
+-------------+-------------+--------------+
```
这显示 s3 正在应用事务以赶上群组。一旦它赶上群组的其他成员，其 member_state 会变为 ONLINE：This shows that s3 is applying transactions to catch up with the group. Once it has caught up with the rest of the group, its member_state changes to ONLINE:
```
mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+
| member_host | member_port | member_state |
+-------------+-------------+--------------+
| s3          |        3306 | ONLINE       |
| s2          |        3306 | ONLINE       |
| s1          |        3306 | ONLINE       |
+-------------+-------------+--------------+
```
Note
如果您正在恢复的服务器是主成员，一旦它与组同步并变为ONLINE，请执行恢复主成员结尾处描述的步骤，以恢复在启动服务器之前对其进行的配置更改。If the server you are restoring is a primary member, once it has gained synchrony with the group and become ONLINE, perform the steps described at the end of Restoring a Primary Member to revert the configuration changes you had made to the server before you started it.

该成员现在已作为新成员恢复到组中。The member has now been restored to the group as a new member.

恢复持久化系统变量。 mysqlbackup 不支持备份或保留第 7.1.9.3 节，“持久化系统变量”——mysqld-auto.cnf 文件不会包含在备份中。要使用其持久化变量设置启动已恢复的成员，您需要执行以下操作之一：Restoring Persisted System Variables. mysqlbackup does not provide support for backing up or preserving Section 7.1.9.3, “Persisted System Variables”—the file mysqld-auto.cnf is not included in a backup. To start the restored member with its persisted variable settings, you need to do one of the following:

保留损坏服务器上的 mysqld-auto.cnf 文件副本，并将其复制到已恢复服务器的数据目录中。Preserve a copy of the mysqld-auto.cnf file from the corrupted server, and copy it to the restored server's data directory.
如果组中其他成员拥有与损坏成员相同的持久化系统变量设置，则可将该成员的 mysqld-auto.cnf 文件复制到已恢复服务器的数据目录中。Copy the mysqld-auto.cnf file from another member of the group into the restored server's data directory, if that member has the same persisted system variable settings as the corrupted member.
在恢复的服务器启动后并且在重启组复制之前，通过mysql客户端手动将所有系统变量设置为其持久化的值。After the restored server is started and before you restart Group Replication, set all the system variables manually to their persisted values through a mysql client.

恢复主成员。 如果恢复的成员是组中的主成员，则必须在组复制分布式恢复过程中小心，防止对恢复的数据库进行写操作。根据客户端访问组的方式，一旦恢复的成员在网络上可访问，在该成员完成对其离开组期间错过的活动的跟进之前，可能会执行 DML 语句。为避免这种情况，在启动恢复的服务器之前，在服务器选项文件中配置以下系统变量：Restoring a Primary Member. If the restored member is a primary in the group, care must be taken to prevent writes to the restored database during the Group Replication distributed recovery process. Depending on how the group is accessed by clients, there is a possibility of DML statements being executed on the restored member once it becomes accessible on the network, prior to the member finishing its catch-up on the activities it has missed while off the group. To avoid this, before starting the restored server, configure the following system variables in the server option file:

group_replication_start_on_boot=OFF
super_read_only=ON
event_scheduler=OFF

这些设置确保成员在启动时变为只读，并且在成员在分布式恢复过程中追赶组时，事件调度器被关闭。还必须为客户端提供足够的错误处理，因为在此期间它们无法对正在恢复的成员执行 DML 操作。These settings ensure that the member becomes read-only at startup, and that the event scheduler is turned off while the member catches up with the group during the distributed recovery process. Adequate error handling must also be provided for on the clients, since they are unable to perform DML operations during this period on the member being restored.

一旦恢复过程完全完成，并且恢复的成员与组的其余部分同步，你可以恢复这些更改。首先，使用此处显示的语句重新启动事件调度器：Once the restoration process is fully completed and the restored member is synchronized with the rest of the group, you can revert these changes. First, restart the event scheduler using the statement shown here:

mysql> SET global event_scheduler=ON;

在此之后，你应该在成员的选项文件中设置以下系统变量，以便它们在下一次启动成员时具有必要的值：After this, you should set the following system variables in the member's option file, so that they have the necessary values for the next time that the member is started:

group_replication_start_on_boot=ON
super_read_only=OFF
event_scheduler=ON

首页上一级下一节