In multi-primary mode
no member has a special role. Any member that is compatible with
the other group members is set to read-write mode when joining
the group, and can process write transactions, even if they are
If a member stops accepting write transactions, for example, in the event of an unexpected server exit, clients connected to it can be redirected, or failed over, to any other member that is in read-write mode. Group Replication does not handle client-side failover itself, so you need to arrange this using a middleware framework such as MySQL Router 8.2, a proxy, a connector, or the application itself. Figure 20.5, “Client Failover” shows how clients can reconnect to an alternative group member if a member leaves the group.
Group Replication is an eventual consistency system. This means that as soon as the incoming traffic slows down or stops, all group members have the same data content. While traffic is flowing, transactions can be externalized on some members before the others, especially if some members have less write throughput than others, creating the possibility of stale reads. In multi-primary mode, slower members can also build up an excessive backlog of transactions to certify and apply, leading to a greater risk of conflicts and certification failure. To limit these issues, you can activate and tune Group Replication's flow control mechanism to minimize the difference between fast and slow members. For more information on flow control, see Section 20.7.2, “Flow Control”.
If you want transaction consistency guaranteed for every
transaction in the group, you can do this using the
system variable. You can choose a setting that suits the
workload of your group and your priorities for data reads and
writes, taking into account the performance impact of the
synchronization required to increase consistency. You can also
set the system variable for individual sessions to protect
particularly concurrency-sensitive transactions. For more
information on transaction consistency, see
Section 20.5.3, “Transaction Consistency Guarantees”.
When a group is deployed in multi-primary mode, transactions are checked to ensure they are compatible with the mode. The following strict consistency checks are made when Group Replication is deployed in multi-primary mode:
If a transaction is executed under the SERIALIZABLE isolation level, then its commit fails when synchronizing itself with the group.
If a transaction executes against a table that has foreign keys with cascading constraints, then its commit fails when synchronizing itself with the group.
The checks are controlled by the
system variable. In multi-primary mode, the system variable
should normally be set to
ON, but the
checks can optionally be deactivated by setting the system
OFF. When deploying in
single-primary mode, the system variable must be set to
In a Group Replication topology in multi-primary mode, care needs to be taken when executing data definition statements, also commonly known as data definition language (DDL).
MySQL 8.3 supports atomic Data Definition
Language (DDL) statements, where the complete DDL statement is
either committed or rolled back as a single atomic
transaction. DDL statements, atomic or otherwise, implicitly
end any transaction that is active in the current session, as
if you had done a
executing the statement. This means that DDL statements cannot
be performed within another transaction, within transaction
control statements such as
START TRANSACTION ...
COMMIT, or combined with other statements within the
Group Replication is based on an optimistic replication paradigm, where statements are optimistically executed and rolled back later if necessary. Each server executes without securing group agreement first. Therefore, more care needs to be taken when replicating DDL statements in multi-primary mode. If you make schema changes (using DDL) and changes to the data that an object contains (using DML) for the same object, the changes need to be handled through the same server while the schema operation has not yet completed and replicated everywhere. Failure to do so can result in data inconsistency when operations are interrupted or only partially completed. If the group is deployed in single-primary mode this issue does not occur, because all changes are performed through the same server, the primary.
For more information about atomic DDL support, see Section 15.1.1, “Atomic Data Definition Statement Support”.
For optimal compatibility and performance, all members of a group should run the same version of MySQL Server and therefore of Group Replication. In multi-primary mode, this is more significant because all members would normally join the group in read-write mode. If a group includes members running more than one MySQL Server version, there is a potential for some members to be incompatible with others, because they support functions others do not, or lack functions others have. To guard against this, when a new member joins (including a former member that has been upgraded and restarted), the member carries out compatibility checks against the rest of the group.
One result of these compatibility checks is particularly important in multi-primary mode. If a joining member is running a higher MySQL Server version than the lowest version that the existing group members are running, it joins the group but remains in read-only mode. (In a group that is running in single-primary mode, newly added members default to being read-only in any case.) Members running MySQL 8.0.17 or higher take into account the major.minor.release version of the MySQL software (and thus, of the Group Replication plugin) when checking their compatibility. Members running earlier versions take into account only the major.minor version.
In a group running in multi-primary mode with members that use
different MySQL Server versions, Group Replication
automatically manages the read-write and read-only status of
members running MySQL 8.0.17 or newer. If a member leaves the
group, the members running the version that is now the lowest
are automatically set to read-write mode. When you change a
group that was running in single-primary mode to run in
multi-primary mode, using the
function, Group Replication automatically sets members to the
correct mode. Members are automatically placed in read-only
mode if they are running a higher MySQL server version than
the lowest version present in the group, and members running
the lowest version are placed in read-write mode.
For full information on version compatibility in a group and how this influences the behavior of a group during an upgrade process, see Section 20.8.1, “Combining Different Member Versions in a Group” .