The user account that is specified using the
CHANGE REPLICATION SOURCE TO |
CHANGE MASTER TO statement as the
PRIVILEGE_CHECKS_USER account for a
replication channel must have the
otherwise the replication applier thread does not start. As
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 might be required for the
PRIVILEGE_CHECKS_USER account 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.
explicitly or implicitly allows the
PRIVILEGE_CHECKS_USER account to carry out
the following operations that a replication thread needs to
Setting the value of the system variables
pseudo_slave_mode, to apply appropriate metadata and behaviors when executing transactions.
Updating the system tables
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.
CHANGE REPLICATION SOURCE
CHANGE MASTER TO
statement is set to the default of
PRIVILEGE_CHECKS_USER account needs
privileges sufficient to set restricted session variables, so
that it can change the value of the
variable for the duration of a session to match the setting
replicated from the source. The
gives the account this capability. This privilege also allows
the account to apply mysqlbinlog output that
was created using the
--disable-log-bin option. If
replica always uses that value for the
variable in replication operations, and so does not need these
session administration level privileges.
If table encryption is in use, the
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
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 from mysqlbinlog
output as required, the
account must have the following privileges:
For a row insertion logged in row format (which are logged as a
INSERTprivilege on the relevant table.
For a row update logged in row format (which are logged as an
UPDATEprivilege on the relevant table.
For a row deletion logged in row format (which are logged as a
DELETEprivilege on the relevant table.
If statement-based binary logging is in use (which is not
recommended with a
account), for a transaction control statement such as
COMMIT or DML
logged in statement format (which are logged as a
PRIVILEGE_CHECKS_USER account needs
privileges to execute the statement contained in the event.
LOAD DATA operations need to
be carried out on the replication channel, use row-based binary
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
DATA events, is processed without privilege checks.
For more information, see
Section 126.96.36.199, “Replication and LOAD DATA”.
variable is set to specify one or more SQL statements to be
executed when the replication 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
DROP ROLE, and
GRANT OPTION, and do not permit
the account to update the
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 issue
SET sql_log_bin =
0 before all ACL statements and
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.