MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
Server instances that you want to use for Group Replication must satisfy the following requirements.
InnoDB Storage Engine.
Data must be stored in the
InnoDB
transactional storage
engine. Transactions are executed optimistically and then,
at commit time, are checked for conflicts. If there are
conflicts, in order to maintain consistency across the
group, some transactions are rolled back. This means that
a transactional storage engine is required. Moreover,
InnoDB
provides some
additional functionality that enables better management
and handling of conflicts when operating together with
Group Replication. The use of other storage engines,
including the temporary
MEMORY
storage engine, might
cause errors in Group Replication. You can prevent the use
of other storage engines by setting the
disabled_storage_engines
system variable on group members, for example:
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
Primary Keys.
Every table that is to be replicated by the group must
have a defined primary key, or primary key equivalent
where the equivalent is a non-null unique key. Such keys
are required as a unique identifier for every row within a
table, enabling the system to determine which transactions
conflict by identifying exactly which rows each
transaction has modified. Group Replication has its own
built-in set of checks for primary keys or primary key
equivalents, and does not use the checks carried out by
the
sql_require_primary_key
system variable. You may set
sql_require_primary_key=ON
for a server
instance where Group Replication is running, and you may
set the REQUIRE_TABLE_PRIMARY_KEY_CHECK
option of the CHANGE REPLICATION
SOURCE TO
|
CHANGE MASTER
TO
statement to ON
for a
Group Replication channel. However, be aware that you
might find some transactions that are permitted under
Group Replication's built-in checks are not permitted
under the checks carried out when you set
sql_require_primary_key=ON
or
REQUIRE_TABLE_PRIMARY_KEY_CHECK=ON
.
Network Performance. MySQL Group Replication is designed to be deployed in a cluster environment where server instances are very close to each other. The performance and stabiity of a group can be impacted by both network latency and network bandwidth. Bi-directional communication must be maintained at all times between all group members. If either inbound or outbound communication is blocked for a server instance (for example, by a firewall, or by connectivity issues), the member cannot function in the group, and the group members (including the member with issues) might not be able to report the correct member status for the affected server instance.
From MySQL 8.0.14, you can use an IPv4 or IPv6 network infrastructure, or a mix of the two, for TCP communication between remote Group Replication servers. There is also nothing preventing Group Replication from operating over a virtual private network (VPN).
Also from MySQL 8.0.14, where Group Replication server instances are co-located and share a local group communication engine (XCom) instance, a dedicated input channel with lower overhead is used for communication where possible instead of the TCP socket. For certain Group Replication tasks that require communication between remote XCom instances, such as joining a group, the TCP network is still used, so network performance influences the group's performance.
The following options must be configured as shown on server instances that are members of a group.
Unique Server Identifier.
Use the server_id
system
variable to configure the server with a unique server ID,
as required for all servers in replication topologies. The
server ID must be a positive integer between 1 and
(232)−1, and it must be
different from every other server ID in use by any other
server in the replication topology.
Binary Log Active.
Set
--log-bin[=log_file_name]
.
MySQL Group Replication replicates binary log contents,
therefore the binary log needs to be on for it to operate.
This option is enabled by default. See
Section 5.4.4, “The Binary Log”.
Replica Updates Logged.
Set --log-slave-updates
.
This option is enabled by default. Group members need to
log transactions that are received from their donors at
joining time and applied through the replication applier,
and to log all transactions that they receive and apply
from the group. This enables Group Replication to carry
out distributed recovery by state transfer from an
existing group member's binary log.
Binary Log Row Format.
Set --binlog-format=row
.
Group Replication relies on row-based replication format
to propagate changes consistently among the servers in the
group. It relies on row-based infrastructure to be able to
extract the necessary information to detect conflicts
among transactions that execute concurrently in different
servers in the group. From MySQL 8.0.19, the
REQUIRE_ROW_FORMAT
setting is
automatically added to Group Replication's channels to
enforce the use of row-based replication when the
transactions are applied. See
Section 17.2.1, “Replication Formats” and
Section 17.3.3, “Replication Privilege Checks”.
Binary Log Checksums Off (to MySQL 8.0.20).
Up to and including MySQL 8.0.20, set
--binlog-checksum=NONE
.
In these releases, Group Replication cannot make use of
checksums and does not support their presence in the
binary log. From MySQL 8.0.21, Group Replication supports
checksums, so group members may use the default setting.
Global Transaction Identifiers On.
Set gtid_mode=ON
and
enforce_gtid_consistency=ON
.
Group Replication uses global transaction identifiers to
track exactly which transactions have been committed on
every server instance and thus be able to infer which
servers have executed transactions that could conflict
with already committed transactions elsewhere. In other
words, explicit transaction identifiers are a fundamental
part of the framework to be able to determine which
transactions may conflict. See
Section 17.1.3, “Replication with Global Transaction Identifiers”.
Replication Information Repositories.
Set
master_info_repository=TABLE
and
relay_log_info_repository=TABLE
.
In MySQL 8.0, this setting is the default, and the
FILE
setting is deprecated. From MySQL
8.0.23, the use of these system variables is deprecated,
so omit the system variables and just allow the default.
The replication applier needs to have the replication
metadata written to the
mysql.slave_master_info
and
mysql.slave_relay_log_info
system
tables to ensure the Group Replication plugin has
consistent recoverability and transactional management of
the replication metadata. See
Section 17.2.4.2, “Replication Metadata Repositories”.
Transaction Write Set Extraction.
Set
--transaction-write-set-extraction=XXHASH64
so that while collecting rows to log them to the binary
log, the server collects the write set as well. The write
set is based on the primary keys of each row and is a
simplified and compact view of a tag that uniquely
identifies the row that was changed. This tag is then used
for detecting conflicts. This option is enabled by
default.
Binary Log Dependency Tracking.
Setting
binlog_transaction_dependency_tracking=WRITESET_SESSION
can improve performance for a group member, depending on
the group's workload. Group Replication carries out its
own parallelization after certification when applying
transactions from the relay log, independently of the
value set for
binlog_transaction_dependency_tracking
.
However, the value of
binlog_transaction_dependency_tracking
does affect how transactions are written to the binary
logs on Group Replication members. The dependency
information in those logs is used to assist the process of
state transfer from a donor's binary log for distributed
recovery, which takes place whenever a member joins or
rejoins the group.
Default Table Encryption.
Set
--default-table-encryption
to the same value on all group members. Default schema and
tablespace encryption can be either enabled
(ON
) or disabled
(OFF
, the default) as long as the
setting is the same on all members.
Lower Case Table Names.
Set
--lower-case-table-names
to the same value on all group members. A setting of 1 is
correct for the use of the
InnoDB
storage engine, which
is required for Group Replication. Note that this setting
is not the default on all platforms.
Multithreaded Appliers.
Group Replication members can be configured as
multithreaded replicas, enabling transactions to be
applied in parallel. A nonzero value for
slave_parallel_workers
enables the multithreaded applier on the member, and up to
1024 parallel applier threads can be specified. Setting
slave_preserve_commit_order=1
ensures that the final commit of parallel transactions is
in the same order as the original transactions, as
required for Group Replication, which relies on
consistency mechanisms built around the guarantee that all
participating members receive and apply committed
transactions in the same order. Finally, the setting
slave_parallel_type=LOGICAL_CLOCK
,
which specifies the policy used to decide which
transactions are allowed to execute in parallel on the
replica, is required with
slave_preserve_commit_order=1
.
Setting
slave_parallel_workers=0
disables parallel execution and gives the replica a single
applier thread and no coordinator thread. With that
setting, the
slave_parallel_type
and
slave_preserve_commit_order
options have no effect and are ignored.