MySQL 9.0 Reference Manual Including MySQL NDB Cluster 9.0
In single-primary mode
(group_replication_single_primary_mode=ON
)
the group has a single primary server that is set to read-write
mode. All the other members in the group are set to read-only
mode (with super_read_only=ON
).
The primary is typically the first server to bootstrap the
group. All other servers that join the group learn about the
primary server and are automatically set to read-only mode.
In single-primary mode, Group Replication enforces that only a
single server writes to the group, so compared to multi-primary
mode, consistency checking can be less strict and DDL statements
do not need to be handled with any extra care. The option
group_replication_enforce_update_everywhere_checks
enables or disables strict consistency checks for a group. When
deploying in single-primary mode, or changing the group to
single-primary mode, this system variable must be set to
OFF
.
The member that is designated as the primary server can change in the following ways:
If the existing primary leaves the group, whether voluntarily or unexpectedly, a new primary is elected automatically.
You can appoint a specific member as the new primary using
the
group_replication_set_as_primary()
function.
If you use the
group_replication_switch_to_single_primary_mode()
function to change a group that was running in
multi-primary mode to run in single-primary mode, a new
primary is elected automatically, or you can appoint the
new primary by specifying it with the function.
When a new primary server is elected automatically or appointed manually, it is automatically set to read-write, and the other group members remain as secondaries, and as such, read-only. Figure 20.4, “New Primary Election” shows this process.
When a new primary is elected or appointed, it might have a
backlog of changes that had been applied on the old primary but
have not yet been applied on this server. In this situation,
until the new primary catches up with the old primary,
read-write transactions might result in conflicts and be rolled
back, and read-only transactions might result in stale reads.
Group Replication's flow control mechanism, which minimizes the
difference between fast and slow members, reduces the chances of
this happening if it is activated and properly tuned. For more
information on flow control, see
Section 20.7.2, “Flow Control”. You can also
use the
group_replication_consistency
system variable to configure the group's level of
transaction consistency to prevent this issue. Setting it to
BEFORE_ON_PRIMARY_FAILOVER
or any higher
consistency level holds new transactions on a newly elected
primary until the backlog has been applied. (This is the default
value.)
For more information on transaction consistency, see Section 20.5.3, “Transaction Consistency Guarantees”. If flow control and transaction consistency guarantees are not used for a group, it is a good practice to wait for the new primary to apply its replication-related relay log before re-routing client applications to it.
The automatic primary member election process involves each member looking at the new view of the group, ordering the potential new primary members, and choosing the member that qualifies as the most suitable. Each member makes its own decision locally, following the primary election algorithm in its MySQL Server release. Because all members must reach the same decision, members adapt their primary election algorithm if other group members are running lower MySQL Server versions, so that they have the same behavior as the member with the lowest MySQL Server version in the group.
The factors considered by members when electing a primary, in order, are as follows:
The first factor considered is which member or members are running the lowest MySQL Server version. If all group members are running MySQL 8.0.17 or higher, members are first ordered by the patch version of their release. (If any members are running MySQL 8.0.16 or earlier—not recommended with members running MySQL 9.0—these are first ordered by the major version of their release, and the patch version is ignored.)
If more than one member is running the lowest MySQL
Server version, the second factor considered is the
member weight of each of those members, as specified by
the
group_replication_member_weight
system variable on the member.
The
group_replication_member_weight
system variable specifies a number in the range 0-100.
All members default to a weight of 50, so set a weight
below this to lower their ordering, and a weight above
it to increase their ordering. You can use this
weighting function to prioritize the use of better
hardware or to ensure failover to a specific member
during scheduled maintenance of the primary.
If more than one member is running the lowest MySQL
Server version, and more than one of those members has
the highest member weight (or member weighting is being
ignored), the third factor considered is the
lexicographical order of the generated server UUIDs of
each member, as specified by the
server_uuid
system
variable. The member with the lowest server UUID is
chosen as the primary. This factor acts as a guaranteed
and predictable tie-breaker so that all group members
reach the same decision if it cannot be determined by
any important factors.
To find out which server is currently the primary when
deployed in single-primary mode, use the
MEMBER_ROLE
column in the
performance_schema.replication_group_members
table. For example:
mysql> SELECT MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------------------+-------------+
| MEMBER_HOST | MEMBER_ROLE |
+-------------------------+-------------+
| remote1.example.com | PRIMARY |
| remote2.example.com | SECONDARY |
| remote3.example.com | SECONDARY |
+-------------------------+-------------+