content_image/746274.jpg
<img src='content_image/746274.jpg'>

## Important 

The caching_sha2_password  authentication plugin is the default for new users created from MySQL 8.0 (for details, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication” ). To connect to a server using a user account that authenticates with this plugin, you must either set up an encrypted connection as described in Section 17.3.1, “Setting Up Replication to Use Encrypted Connections” , or enable the unencrypted connection to support password exchange using an RSA key pair. 

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 17.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’; 

You assign the PRIVILEGE_CHECKS_USER  account for a replication channel using a CHANGE REPLICATION SOURCE TO  statement (from MySQL 8.0.23) or CHANGE MASTER TO  statement (before MySQL 8.0.23). The use of row-based binary logging is strongly recommended when PRIVILEGE_CHECKS_USER  is set, and from MySQL 8.0.19 you can use the statement to set REQUIRE_ROW_FORMAT  to enforce this. If replication is running, issue STOP REPLICA | SLAVE  before the CHANGE MASTER TO  statement, and START REPLICA | SLAVE  after it. For example, to start privilege checks on the channel channel_1  on 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 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. 

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. 

