Rechercher dans le manuel MySQL
18.1.3.2 Multi-Primary Mode
In multi-primary mode
(group_replication_single_primary_mode=OFF)
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
issued concurrently.
If a member stops accepting write transactions, for example in the event of a server crash, 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.0, a proxy, a connector, or the application itself. Figure 18.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 18.6.2, “Flow Control”.
From MySQL 8.0.14, if you want to have a transaction consistency
guarantee for every transaction in the group, you can do this
using the
group_replication_consistency
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 18.4.2, “Transaction Consistency Guarantees”.
18.1.3.2.1 Transaction Checks
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
group_replication_enforce_update_everywhere_checks
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
variable to OFF. When deploying in
single-primary mode, the system variable must be set to
OFF.
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.0 introduces support for atomic Data Definition
Language (DDL) statements, where the complete DDL statement is
either committed or rolled back as a single atomic
transaction. However, DDL statements, atomic or otherwise,
implicitly end any transaction that is active in the current
session, as if you had done a
COMMIT before 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
same transaction.
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 details on atomic DDL support in MySQL 8.0, and the resulting changes in behavior for the replication of certain statements, see Section 13.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 patch version of the release when checking their compatibility. Members running MySQL 8.0.16 or lower, or MySQL 5.7, only take into account the major 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 higher. 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
group_replication_switch_to_multi_primary_mode()
UDF, 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 18.7.1, “Combining Different Member Versions in a Group” .
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-group-replication-multi-primary-mode.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.
