This section explains how to change which member of a single-primary group is the primary. The function used to change a group's mode can be run on any member.
Use the
group_replication_set_as_primary()
function to change which member is the primary in a
single-primary group. The current primary becomes a read-only
secondary, and the specified group member becomes the
read-write primary. The function can be used on any member of
a replication group running in single-primary mode. It
replaces the usual primary election process, which is
described in
Section 18.1.3.1, “Single-Primary Mode”.
If a standard source to replica replication channel is running
on the existing primary member in addition to the Group
Replication channels, you must stop that replication channel
before you can change the primary member. You can identify the
current primary using the MEMBER_ROLE
column in the Performance Schema table
replication_group_members
, or the
group_replication_primary_member
status variable.
If you invoke the function on a member running a MySQL Server version from 8.0.17, and all members are running MySQL Server version 8.0.17 or higher, you can only specify a new primary member that is running the lowest MySQL Server version in the group, based on the patch version. This safeguard is applied to ensure the group maintains compatibility with new functions. If any member is running a MySQL Server version between MySQL 8.0.13 and MySQL 8.0.16, this safeguard is not enforced for the group and you can specify any new primary member, but it is recommended to select a primary that is running the lowest MySQL Server version in the group.
Any uncommitted transactions that the group is waiting on must be committed, rolled back, or terminated before the operation can complete. Before MySQL 8.0.29, the function waits for all active transactions on the existing primary to end, including incoming transactions that are started after the function is used. From MySQL 8.0.29, you can specify a timeout from 0 seconds (immediately) up to 3600 seconds (60 minutes) for transactions that are running when you use the function. For the timeout to work, all members of the group must be at MySQL 8.0.29 or higher. There is no default setting for the timeout, so if you do not set it, there is no upper limit to the wait time, and new transactions can start during that time.
When the timeout expires, for any transactions that did not
yet reach their commit phase, the client session is
disconnected so that the transaction does not proceed.
Transactions that reached their commit phase are allowed to
complete. When you set a timeout, it also prevents new
transactions starting on the primary from that point on.
Explicitly defined transactions (with a START
TRANSACTION
or BEGIN
statement)
are subject to the timeout, disconnection, and incoming
transaction blocking even if they do not modify any data. To
allow inspection of the primary while the function is
operating, single statements that do not modify data, as
listed in
Permitted Queries Under Consistency Rules,
are permitted to proceed.
Pass in the server_uuid
of
the member which you want to become the new primary of the
group by issuing:
SELECT group_replication_set_as_primary(member_uuid);
From MySQL 8.0.29, you can add a timeout, for example:
SELECT group_replication_set_as_primary(‘00371d66-3c45-11ea-804b-080027337932’, 300)
To check the status of the timeout, use the
PROCESSLIST_INFO
column in the Performance
Schema threads table:
SELECT NAME, PROCESSLIST_INFO FROM performance_schema.threads WHERE NAME=\"thread/group_rpl/THD_transaction_monitor\";
+---------------------------------------------+----------------------------------------------------------------------+
| NAME | PROCESSLIST_INFO |
+--------------------------------------------------------------------------------------------------------------------+
| thread/group_rpl/THD_transaction_monitor | Group replication transaction monitor: Stopped client connections |
+--------------------------------------------------------------------------------------------------------------------+
The status shows when the transaction monitoring thread has been created, when new transactions have been stopped, when the client connections with uncommitted transactions have been disconnected, and finally, when the process is complete and new transactions are allowed again.
While the action runs, you can check its progress by issuing:
SELECT event_name, work_completed, work_estimated FROM performance_schema.events_stages_current WHERE event_name LIKE "%stage/group_rpl%";
+----------------------------------------------------------------------------------+----------------+----------------+
| event_name | work_completed | work_estimated |
+----------------------------------------------------------------------------------+----------------+----------------+
| stage/group_rpl/Primary Election: Waiting for members to turn on super_read_only | 3 | 5 |
+----------------------------------------------------------------------------------+----------------+----------------+