默认情况下，MySQL 复制（包括组复制）在将已被其他服务器接受的事务应用到副本或组成员时不会进行权限检查。您可以创建一个具有适当权限的用户账户，以应用通常在通道上复制的事务，并将其指定为复制应用程序的 PRIVILEGE_CHECKS_USER 账户，使用 CHANGE REPLICATION SOURCE TO 语句。MySQL 然后会根据用户账户的权限检查每个事务，以验证您是否已授权对该通道的操作。该账户还可以被管理员安全地用于应用或重新应用来自 mysqlbinlog 输出的事务，例如，用于从通道上的复制错误中恢复。By default, MySQL replication (including Group Replication) does not carry out privilege checks when transactions that were already accepted by another server are applied on a replica or group member. You can create a user account with the appropriate privileges to apply the transactions that are normally replicated on a channel, and specify this as the PRIVILEGE_CHECKS_USER account for the replication applier, using a CHANGE REPLICATION SOURCE TO statement. MySQL then checks each transaction against the user account's privileges to verify that you have authorized the operation for that channel. The account can also be safely used by an administrator to apply or reapply transactions from mysqlbinlog output, for example to recover from a replication error on the channel.

使用 PRIVILEGE_CHECKS_USER 账户有助于保护复制通道，防止特权或不需要操作的未经授权或意外使用。PRIVILEGE_CHECKS_USER 账户在以下情况下提供额外的安全层：The use of a PRIVILEGE_CHECKS_USER account helps secure a replication channel against the unauthorized or accidental use of privileged or unwanted operations. The PRIVILEGE_CHECKS_USER account provides an additional layer of security in situations such as these:

当您为通道指定 PRIVILEGE_CHECKS_USER 帐户时，可以通过在 CHANGE REPLICATION SOURCE TO 语句中添加以下一个或两个选项，来提高应用权限检查的复制通道的安全性：You can increase the security of a replication channel where privilege checks are applied by adding one or both of these options to the CHANGE REPLICATION SOURCE TO statement when you specify the PRIVILEGE_CHECKS_USER account for the channel:

您授予REPLICATION_APPLIER权限，以使用户帐户能够作为复制应用程序线程的PRIVILEGE_CHECKS_USER出现，并执行 mysqlbinlog 使用的内部使用BINLOG语句。PRIVILEGE_CHECKS_USER帐户的用户名和主机名必须遵循第 8.2.4 节，“指定帐户名”中描述的语法，并且用户不能是匿名用户（用户名为空）或CURRENT_USER。要创建新帐户，请使用CREATE USER。要授予该帐户REPLICATION_APPLIER权限，请使用GRANT语句。例如，要创建一个用户帐户priv_repl，管理员可以手动从example.com域中的任何主机使用，并且需要加密连接，请执行以下语句：You grant the REPLICATION_APPLIER privilege to enable a user account to appear as the PRIVILEGE_CHECKS_USER for a replication applier thread, and to execute the internal-use BINLOG statements used by mysqlbinlog. The user name and host name for the PRIVILEGE_CHECKS_USER account must follow the syntax described in Section 8.2.4, “Specifying Account Names”, and the user must not be an anonymous user (with a blank user name) or the CURRENT_USER. To create a new account, use CREATE USER. To grant this account the REPLICATION_APPLIER privilege, use the GRANT statement. For example, to create a user account priv_repl, which can be used manually by an administrator from any host in the example.com domain, and requires an encrypted connection, issue the following statements:

mysql> SET sql_log_bin = 0;
mysql> CREATE USER 'priv_repl'@'%.example.com' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT REPLICATION_APPLIER ON *.* TO 'priv_repl'@'%.example.com';
mysql> SET sql_log_bin = 1;

SET sql_log_bin 语句用于确保账户管理语句不会被添加到二进制日志中，也不会发送到复制通道（参见 第 15.4.1.3 节，“SET sql_log_bin 语句”）。The SET sql_log_bin statements are used so that the account management statements are not added to the binary log and sent to the replication channels (see Section 15.4.1.3, “SET sql_log_bin Statement”).

在设置用户账户后，使用GRANT语句授予额外权限，使用户账户能够执行您期望应用线程执行的数据库更改操作，例如更新服务器上的特定表。这些权限同样允许管理员在需要手动在复制通道上执行这些事务时使用该账户。如果尝试执行未授予适当权限的意外操作，则该操作将不被允许，并且复制应用线程将因错误而停止。第 19.3.3.1 节，“为复制 PRIVILEGE_CHECKS_USER 账户的权限”说明了账户所需的其他权限。例如，要授予priv_repl用户账户INSERT权限，以便向db1中的cust表添加行，请执行以下语句：After setting up the user account, use the GRANT statement to grant additional privileges to enable the user account to make the database changes that you expect the applier thread to carry out, such as updating specific tables held on the server. These same privileges enable an administrator to use the account if they need to execute any of those transactions manually on the replication channel. If an unexpected operation is attempted for which you did not grant the appropriate privileges, the operation is disallowed and the replication applier thread stops with an error. Section 19.3.3.1, “Privileges For The Replication PRIVILEGE_CHECKS_USER Account” explains what additional privileges the account needs. For example, to grant the priv_repl user account the INSERT privilege to add rows to the cust table in db1, issue the following statement:

mysql> GRANT INSERT ON db1.cust TO 'priv_repl'@'%.example.com';

您可以使用PRIVILEGE_CHECKS_USER语句为复制通道分配CHANGE REPLICATION SOURCE TO账户。如果复制正在运行，在STOP REPLICA语句之前执行CHANGE REPLICATION SOURCE TO，在其之后执行START REPLICA。当设置PRIVILEGE_CHECKS_USER时，强烈建议使用基于行的二进制日志；您可以使用该语句将REQUIRE_ROW_FORMAT设置为强制执行此操作。You assign the PRIVILEGE_CHECKS_USER account for a replication channel using a CHANGE REPLICATION SOURCE TO statement. If replication is running, issue STOP REPLICA before the CHANGE REPLICATION SOURCE TO statement, and START REPLICA after it. The use of row-based binary logging is strongly recommended when PRIVILEGE_CHECKS_USER is set; you can use the statement to set REQUIRE_ROW_FORMAT to enforce this.

当您重新启动复制通道时，从那一刻起将应用对动态权限的检查。然而，静态全局权限在应用端的上下文中不会生效，直到您重新加载授权表，因为这些权限对于已连接的客户端未作更改。要激活静态权限，请执行刷新权限操作。这可以通过发出 FLUSH PRIVILEGES 语句，或者执行 mysqladmin flush-privileges 或 mysqladmin reload 命令来完成。When you restart the replication channel, checks on dynamic privileges are applied from that point on. However, static global privileges are not active in the applier's context until you reload the grant tables, because these privileges are not changed for a connected client. To activate static privileges, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

例如，要在正在运行的副本上对通道 channel_1 启动权限检查，请发出以下语句：For example, to start privilege checks on the channel channel_1 on a running replica, issue the following statements:

mysql> STOP REPLICA FOR CHANNEL 'channel_1';
mysql> CHANGE REPLICATION SOURCE TO
     >    PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com',
     >    REQUIRE_ROW_FORMAT = 1 FOR CHANNEL 'channel_1';
mysql> FLUSH PRIVILEGES;
mysql> START REPLICA FOR CHANNEL 'channel_1';

如果未指定通道并且不存在其他通道，则该语句将应用于默认通道。通道的 PRIVILEGE_CHECKS_USER 帐户的用户名和主机名显示在性能模式的 replication_applier_configuration 表中，在该表中它们经过适当转义，因此可以直接复制到 SQL 语句中以执行单个事务。If you do not specify a channel and no other channels exist, the statement is applied to the default channel. The user name and host name for the PRIVILEGE_CHECKS_USER account for a channel are shown in the Performance Schema replication_applier_configuration table, where they are properly escaped so they can be copied directly into SQL statements to execute individual transactions.

如果您正在使用Rewriter插件，您应该授予PRIVILEGE_CHECKS_USER用户账户SKIP_QUERY_REWRITE权限。这可以防止该用户发出的语句被重写。有关更多信息，请参见第 7.6.4 节，“重写器查询重写插件”。If you are using the Rewriter plugin, you should grant the PRIVILEGE_CHECKS_USER user account the SKIP_QUERY_REWRITE privilege. This prevents statements issued by this user from being rewritten. See Section 7.6.4, “The Rewriter Query Rewrite Plugin”, for more information.

当为复制通道设置 REQUIRE_ROW_FORMAT 时，复制应用程序不会创建或删除临时表，因此也不会设置 pseudo_thread_id 会话系统变量。它不会执行 LOAD DATA INFILE 指令，因此不会尝试进行访问或删除与数据加载相关的临时文件的文件操作（记录为 Format_description_log_event）。它不会执行 INTVAR、RAND 和 USER_VAR 事件，这些事件用于重现用于基于语句复制的客户端连接状态。（例外情况是与 DDL 查询相关的 USER_VAR 事件，这些事件将被执行。）它不会执行记录在 DML 事务中的任何语句。如果复制应用程序在尝试排队或应用事务时检测到任何这些类型的事件，事件将不会被应用，并且复制会在错误中停止。When REQUIRE_ROW_FORMAT is set for a replication channel, the replication applier does not create or drop temporary tables, and so does not set the pseudo_thread_id session system variable. It does not execute LOAD DATA INFILE instructions, and so does not attempt file operations to access or delete the temporary files associated with data loads (logged as a Format_description_log_event). It does not execute INTVAR, RAND, and USER_VAR events, which are used to reproduce the client's connection state for statement-based replication. (An exception is USER_VAR events that are associated with DDL queries, which are executed.) It does not execute any statements that are logged within DML transactions. If the replication applier detects any of these types of event while attempting to queue or apply a transaction, the event is not applied, and replication stops with an error.

无论是否设置 PRIVILEGE_CHECKS_USER 帐户，您都可以为复制通道设置 REQUIRE_ROW_FORMAT。当您设置此选项时实施的限制，即使没有权限检查，也能提高复制通道的安全性。您还可以在使用 mysqlbinlog 时指定 --require-row-format 选项，以在 mysqlbinlog 输出中强制使用行格式复制事件。You can set REQUIRE_ROW_FORMAT for a replication channel whether or not you set a PRIVILEGE_CHECKS_USER account. The restrictions implemented when you set this option increase the security of the replication channel even without privilege checks. You can also specify the --require-row-format option when you use mysqlbinlog, to enforce row-based replication events in mysqlbinlog output.

安全上下文。 默认情况下，当使用指定为 PRIVILEGE_CHECKS_USER 的用户帐户启动复制应用线程时，安全上下文会使用默认角色创建，或者如果 activate_all_roles_on_login 设置为 ON，则会使用所有角色创建。Security Context.  By default, when a replication applier thread is started with a user account specified as the PRIVILEGE_CHECKS_USER, the security context is created using default roles, or with all roles if activate_all_roles_on_login is set to ON.

您可以使用角色向用作 PRIVILEGE_CHECKS_USER 账户的帐户提供一般权限集，如以下示例所示。在这里，不是像前面的示例那样直接将 INSERT 权限授予 db1.cust 表的用户账户，而是将此权限连同 REPLICATION_APPLIER 权限一起授予角色 priv_repl_role。然后使用该角色将权限集授予两个用户账户，这两个账户现在都可以用作 PRIVILEGE_CHECKS_USER 账户：You can use roles to supply a general privilege set to accounts that are used as PRIVILEGE_CHECKS_USER accounts, as in the following example. Here, instead of granting the INSERT privilege for the db1.cust table directly to a user account as in the earlier example, this privilege is granted to the role priv_repl_role along with the REPLICATION_APPLIER privilege. The role is then used to grant the privilege set to two user accounts, both of which can now be used as PRIVILEGE_CHECKS_USER accounts:

mysql> SET sql_log_bin = 0;
mysql> CREATE USER 'priv_repa'@'%.example.com'
                  IDENTIFIED BY 'password'
                  REQUIRE SSL;
mysql> CREATE USER 'priv_repb'@'%.example.com'
                  IDENTIFIED BY 'password'
                  REQUIRE SSL;
mysql> CREATE ROLE 'priv_repl_role';
mysql> GRANT REPLICATION_APPLIER TO 'priv_repl_role';
mysql> GRANT INSERT ON db1.cust TO 'priv_repl_role';
mysql> GRANT 'priv_repl_role' TO
                  'priv_repa'@'%.example.com',
                  'priv_repb'@'%.example.com';
mysql> SET DEFAULT ROLE 'priv_repl_role' TO
                  'priv_repa'@'%.example.com',
                  'priv_repb'@'%.example.com';
mysql> SET sql_log_bin = 1;

请注意，当复制应用程序线程创建安全上下文时，它会检查 PRIVILEGE_CHECKS_USER 账户的权限，但不会进行密码验证，也不会执行与账户管理相关的检查，例如检查账户是否被锁定。创建的安全上下文在复制应用程序线程的整个生命周期内保持不变。Be aware that when the replication applier thread creates the security context, it checks the privileges for the PRIVILEGE_CHECKS_USER account, but does not carry out password validation, and does not carry out checks relating to account management, such as checking whether the account is locked. The security context that is created remains unchanged for the lifetime of the replication applier thread.