Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 47.0Mb
PDF (A4) - 47.1Mb
PDF (RPM) - 42.4Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.9Mb
HTML Download (RPM) - 9.4Mb
Man Pages (TGZ) - 226.8Kb
Man Pages (Zip) - 333.5Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Privileges For The Replication PRIVILEGE_CHECKS_USER Account

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.

Important

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, and will be required in a future release. With statement-based binary logging, some administrator-level privileges are required to execute transactions successfully.

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:

  • Setting the value of the system variables gtid_next, original_commit_timestamp, original_server_version, immediate_server_version, and pseudo_slave_mode, to apply appropriate metadata and behaviors when executing transactions.

  • Executing internal-use BINLOG statements to apply mysqlbinlog output, provided that the account also has permission for the tables and operations in those statements.

  • Updating the system tables mysql.gtid_executed, mysql.slave_relay_log_info, mysql.slave_worker_info, and mysql.slave_master_info, to update replication metadata. (If events access these tables explicitly for other purposes, you must grant the appropriate privileges on the tables.)

  • Applying a binary log Table_map_log_event, which provides table metadata but does not make any database changes.

The PRIVILEGE_CHECKS_USER account needs the SESSION_VARIABLES_ADMIN privilege in order to change the value of certain system variables (pseudo_thread_id and sql_require_primary_key) 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:

  • For a row insertion logged in row format (which are logged as a Write_rows_log_event), the INSERT privilege on the relevant table.

  • For a row update logged in row format (which are logged as an Update_rows_log_event), the UPDATE privilege on the relevant table.

  • For a row deletion logged in row format (which are logged as a Delete_rows_log_event), the DELETE privilege on the relevant table.

  • For a transaction control statement such as BEGIN or COMMIT or DML logged in statement format (which are logged as a Query_log_event), privileges to execute the statement contained in the event.

Note

If LOAD DATA INFILE operations need to be carried out on the replication channel, use row-based binary logging (binlog_format=ROW). LOAD DATA is considered unsafe for statement-based format, and the PRIVILEGE_CHECKS_USER account should not be given the required privilege to execute such a statement (the FILE privilege). For more information, see Section 17.5.1.19, “Replication and LOAD DATA”. The Format_description_log_event, which deletes any temporary files created by LOAD DATA events, is processed without privilege checks.

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.