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. From MySQL 8.0.18, 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 thePRIVILEGE_CHECKS_USERaccount for the replication applier, using aCHANGE REPLICATION SOURCE TOstatement (from MySQL 8.0.23) orCHANGE MASTER TOstatement (before MySQL 8.0.23). 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 frommysqlbinlogoutput, for example to recover from a replication error on the channel.

The use of aPRIVILEGE_CHECKS_USERaccount helps secure a replication channel against the unauthorized or accidental use of privileged or unwanted operations. ThePRIVILEGE_CHECKS_USERaccount provides an additional layer of security in situations such as these:

You can increase the security of a replication channel where privilege checks are applied by adding one or both of these options to theCHANGE REPLICATION SOURCE TO|CHANGE MASTER TOstatement when you specify thePRIVILEGE_CHECKS_USERaccount for the channel:

You grant theREPLICATION_APPLIERprivilege to enable a user account to appear as thePRIVILEGE_CHECKS_USERfor a replication applier thread, and to execute the internal-useBINLOGstatements used by mysqlbinlog. The user name and host name for thePRIVILEGE_CHECKS_USERaccount must follow the syntax described inSection 6.2.4, “Specifying Account Names”, and the user must not be an anonymous user (with a blank user name) or theCURRENT_USER. To create a new account, useCREATE USER. To grant this account theREPLICATION_APPLIERprivilege, use theGRANTstatement. For example, to create a user accountpriv_repl, which can be used manually by an administrator from any host in theexample.comdomain, 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;

TheSET sql_log_binstatements are used so that the account management statements are not added to the binary log and sent to the replication channels (seeSection 13.4.1.3, “SET sql_log_bin Statement”).

After setting up the user account, use theGRANTstatement 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 17.3.3.1, “Privileges For The Replication PRIVILEGE_CHECKS_USER Account”explains what additional privileges the account needs. For example, to grant thepriv_repluser account theINSERTprivilege to add rows to thecusttable indb1, issue the following statement:

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

You assign thePRIVILEGE_CHECKS_USERaccount for a replication channel using aCHANGE REPLICATION SOURCE TOstatement (from MySQL 8.0.23) orCHANGE MASTER TOstatement (before MySQL 8.0.23). The use of row-based binary logging is strongly recommended whenPRIVILEGE_CHECKS_USERis set, and from MySQL 8.0.19 you can use the statement to setREQUIRE_ROW_FORMATto enforce this. If replication is running, issueSTOP REPLICA | SLAVEbefore theCHANGE MASTER TOstatement, andSTART REPLICA | SLAVE在它。例如,开始特权检查on the channelchannel_1on a running replica, issue the following statements:

mysql> STOP SLAVE FOR CHANNEL 'channel_1'; mysql> CHANGE MASTER TO PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com', REQUIRE_ROW_FORMAT = 1 FOR CHANNEL 'channel_1'; mysql> START SLAVE FOR CHANNEL 'channel_1'; Or from MySQL 8.0.22 / 8.0.23: 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> START REPLICA FOR CHANNEL 'channel_1';

When you restart the replication channel, the privilege checks are applied from that point on. 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 thePRIVILEGE_CHECKS_USERaccount for a channel are shown in the Performance Schemareplication_applier_configurationtable, where they are properly escaped so they can be copied directly into SQL statements to execute individual transactions.

WhenREQUIRE_ROW_FORMATis set for a replication channel, the replication applier does not create or drop temporary tables, and so does not set thepseudo_thread_idsession system variable. It does not executelOAD DATA INFILEinstructions, and so does not attempt file operations to access or delete the temporary files associated with data loads (logged as aFormat_description_log_event). It does not executeINTVAR,兰德, andUSER_VARevents, which are used to reproduce the client's connection state for statement-based replication. (An exception isUSER_VARevents 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.

You can setREQUIRE_ROW_FORMATfor a replication channel whether or not you set aPRIVILEGE_CHECKS_USERaccount. 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-formatoption when you usemysqlbinlog, to enforce row-based replication events inmysqlbinlogoutput.

Security Context.By default, when a replication applier thread is started with a user account specified as thePRIVILEGE_CHECKS_USER, the security context is created using default roles, or with all roles ifactivate_all_roles_on_loginis set toON.

You can use roles to supply a general privilege set to accounts that are used asPRIVILEGE_CHECKS_USERaccounts, as in the following example. Here, instead of granting theINSERTprivilege for thedb1.custtable directly to a user account as in the earlier example, this privilege is granted to the rolepriv_repl_rolealong with theREPLICATION_APPLIERprivilege. The role is then used to grant the privilege set to two user accounts, both of which can now be used asPRIVILEGE_CHECKS_USERaccounts:

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;

Be aware that when the replication applier thread creates the security context, it checks the privileges for thePRIVILEGE_CHECKS_USERaccount, 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.

limitation.In MySQL 8.0.18 only, if the replicamysqldis restarted immediately after issuing aRESET REPLICA | SLAVEstatement (due to an unexpected server exit or deliberate restart), thePRIVILEGE_CHECKS_USERaccount setting, which is held in themysql.slave_relay_log_infotable, is lost and must be respecified. When you use privilege checks in that release, always verify that they are in place after a restart, and respecify them if required. From MySQL 8.0.19, thePRIVILEGE_CHECKS_USERaccount setting is preserved in this situation, so it is retrieved from the table and reapplied to the channel.