Movatterモバイル変換

The user account that is specified using theCHANGE REPLICATION SOURCE TO statement as thePRIVILEGE_CHECKS_USER account for a replication channel must have theREPLICATION_APPLIER privilege, otherwise the replication applier thread does not start. As explained inSection 19.3.3, “Replication Privilege Checks”, the account requires further privileges that are sufficient to apply all the expected transactions expected on the replication channel. These privileges are checked only when relevant transactions are executed.

The use of row-based binary logging (binlog_format=ROW) is strongly recommended for replication channels that are secured using aPRIVILEGE_CHECKS_USER account. With statement-based binary logging, some administrator-level privileges might be required for thePRIVILEGE_CHECKS_USER account to execute transactions successfully. TheREQUIRE_ROW_FORMAT setting can be applied to secured channels, which restricts the channel from executing events that would require these privileges.

TheREPLICATION_APPLIER privilege explicitly or implicitly allows thePRIVILEGE_CHECKS_USER account to carry out the following operations that a replication thread needs to perform:

If theREQUIRE_TABLE_PRIMARY_KEY_CHECK option of theCHANGE REPLICATION SOURCE TO statement is set to the default valueSTREAM, thePRIVILEGE_CHECKS_USER account needs privileges sufficient to set restricted session variables, so that it can change the value of thesql_require_primary_key system variable for the duration of a session to match the setting replicated from the source. TheSESSION_VARIABLES_ADMIN privilege gives the account this capability. This privilege also allows the account to applymysqlbinlog output that was created using the--disable-log-bin option. If you setREQUIRE_TABLE_PRIMARY_KEY_CHECK to eitherON orOFF, the replica always uses that value for thesql_require_primary_key system variable in replication operations, and so does not need these session administration level privileges.

If table encryption is in use, thetable_encryption_privilege_check system variable is set toON, and the encryption setting for the tablespace involved in any event differs from the applying server's default encryption setting (specified by thedefault_table_encryption system variable), thePRIVILEGE_CHECKS_USER account needs theTABLE_ENCRYPTION_ADMIN privilege in order to override the default encryption setting. It is strongly recommended that you do not grant this privilege. Instead, ensure that the default encryption setting on a replica matches the encryption status of the tablespaces that it replicates, and that replication group members have the same default encryption setting, so that the privilege is not needed.

In order to execute specific replicated transactions from the relay log, or transactions frommysqlbinlog output as required, thePRIVILEGE_CHECKS_USER account must have the following privileges:

If statement-based binary logging is in use (which is not recommended with aPRIVILEGE_CHECKS_USER account), for a transaction control statement such asBEGIN orCOMMIT or DML logged in statement format (which are logged as aQuery_log_event), thePRIVILEGE_CHECKS_USER account needs privileges to execute the statement contained in the event.

IfLOAD DATA operations need to be carried out on the replication channel, use row-based binary logging (binlog_format=ROW). With this logging format, theFILE privilege is not needed to execute the event, so do not give thePRIVILEGE_CHECKS_USER account this privilege. The use of row-based binary logging is strongly recommended with replication channels that are secured using aPRIVILEGE_CHECKS_USER account. IfREQUIRE_ROW_FORMAT is set for the channel, row-based binary logging is required. TheFormat_description_log_event, which deletes any temporary files created byLOAD DATA events, is processed without privilege checks. For more information, seeSection 19.5.1.19, “Replication and LOAD DATA”.

If theinit_replica system variable is set to specify one or more SQL statements to be executed when the replication SQL thread starts, thePRIVILEGE_CHECKS_USER account must have the privileges needed to execute these statements.

It is recommended that you never give any ACL privileges to thePRIVILEGE_CHECKS_USER account, includingCREATE USER,CREATE ROLE,DROP ROLE, andGRANT OPTION, and do not permit the account to update themysql.user table. With these privileges, the account could be used to create or modify user accounts on the server. To avoid ACL statements issued on the source server being replicated to the secured channel for execution (where they fail in the absence of these privileges), you can issueSET sql_log_bin = 0 before all ACL statements andSET sql_log_bin = 1 after them, to omit the statements from the source's binary log. Alternatively, you can set a dedicated current database before executing all ACL statements, and use a replication filter (--binlog-ignore-db) to filter out this database on the replica.