17.3.3.1 Privileges For The Replication PRIVILEGE_CHECKS_USER Account

The user account that is specified as the PRIVILEGE_CHECKS_USER account for a replication channel must have the REPLICATION_APPLIER privilege, otherwise the replication applier thread does not start. As explained in Section 17.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 a PRIVILEGE_CHECKS_USER account. With statement-based binary logging, some administrator-level privileges are required to execute transactions successfully. From MySQL 8.0.19, the REQUIRE_ROW_FORMAT setting can be applied to secured channels, which restricts the channel from executing events that would require these privileges.

The REPLICATION_APPLIER privilege explicitly or implicitly allows the PRIVILEGE_CHECKS_USER account to carry out the following operations that a replication thread needs to perform:

The PRIVILEGE_CHECKS_USER account needs the SESSION_VARIABLES_ADMIN privilege in order to change the value of the sql_require_primary_key system variable for the duration of a session to carry out replication operations. This privilege also allows the account to apply mysqlbinlog output that was created using the --disable-log-bin option.

If table encryption is in use, the table_encryption_privilege_check system variable is set to ON, and the encryption setting for the tablespace involved in any event differs from the applying server's default encryption setting (specified by the default_table_encryption system variable), the PRIVILEGE_CHECKS_USER account needs the TABLE_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 replication slave 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 from mysqlbinlog output as required, the PRIVILEGE_CHECKS_USER account must have the following privileges:

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

If LOAD DATA operations need to be carried out on the replication channel, use row-based binary logging (binlog_format=ROW). With this logging format, the FILE privilege is not needed to execute the event, so do not give the PRIVILEGE_CHECKS_USER account this privilege. The use of row-based binary logging is strongly recommended with replication channels that are secured using a PRIVILEGE_CHECKS_USER account. If REQUIRE_ROW_FORMAT is set for the channel, row-based binary logging is required. The Format_description_log_event, which deletes any temporary files created by LOAD DATA events, is processed without privilege checks. For more information, see Section 17.5.1.19, “Replication and LOAD DATA”.

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

It is recommended that you never give any ACL privileges to the PRIVILEGE_CHECKS_USER account, including CREATE USER, CREATE ROLE, DROP ROLE, and GRANT OPTION, and do not permit the account to update the mysql.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 master being replicated to the secured channel for execution (where they will fail in the absence of these privileges), you can issue SET sql_log_bin = 0 before all ACL statements and SET sql_log_bin = 1 after them, to omit the statements from the master'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 slave.