- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
- 1. Switch the master node
- 2. Switch single master / multi master mode
- 3. Add a new node
- 4. Delete node
- 5. Add back the node that exited abnormally
- 6. Restart MGR cluster
- 7. Summary
- References, documents
- Disclaimers
Today, we will introduce the daily management and maintenance operations of MGR cluster, including master node switching, single master & Multi master mode switching, etc. Manual operation and using MySQL Shell will be introduced respectively.
There is now a three node MGR cluster:
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+ | group_replication_applier | af39db70-6850-11ec-94c9-00155d064000 | 172.16.16.10 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | b05c0838-6850-11ec-a06b-00155d064000 | 172.16.16.11 | 3306 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | b0f86046-6850-11ec-92fe-00155d064000 | 172.16.16.12 | 3306 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
1. Switch the master node
When the master node needs maintenance or rolling upgrade, it can be switched to other nodes.
In command line mode, you can use group_ replication_ set_ as_ The udf of primary () implements switching, for example:
-- take Primary The character switches to the second node
mysql> select group_replication_set_as_primary('b05c0838-6850-11ec-a06b-00155d064000');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('b05c0838-6850-11ec-a06b-00155d064000') |
+--------------------------------------------------------------------------+
| Primary server switched to: b05c0838-6850-11ec-a06b-00155d064000 |
+--------------------------------------------------------------------------+
1 row in set (1.00 sec)
[root@yejr.run:mysql.sock] [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | af39db70-6850-11ec-94c9-00155d064000 | 172.16.16.10 | 3306 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | b05c0838-6850-11ec-a06b-00155d064000 | 172.16.16.11 | 3306 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | b0f86046-6850-11ec-92fe-00155d064000 | 172.16.16.12 | 3306 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+Incidentally, in MySQL version 5.7, the master node can only be switched automatically by restarting, not manually. From this perspective, if you want to use MGR, you'd better choose MySQL version 8.0 instead of version 5.7.
If MySQL Shell is used, you can call setPrimaryInstance() function to switch:
#First get the cluster object
MySQL 172.16.16.10:3306 ssl JS > var c=dba.getCluster()
#View the current node list
MySQL 172.16.16.10:3306 ssl JS > c.status()
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"172.16.16.10:3306": {
"address": "172.16.16.10:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"172.16.16.11:3306": {
"address": "172.16.16.11:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
},
"172.16.16.12:3306": {
"address": "172.16.16.12:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.25"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "172.16.16.10:3306"
}
#Perform switching
MySQL 172.16.16.10:3306 ssl JS > c.setPrimaryInstance('172.16.16.11:3306')
Setting instance '172.16.16.11:3306' as the primary instance of cluster 'MGR1'...
#The changes of the three nodes are listed
Instance '172.16.16.10:3306' was switched from PRIMARY to SECONDARY.
Instance '172.16.16.11:3306' was switched from SECONDARY to PRIMARY.
Instance '172.16.16.12:3306' remains SECONDARY.
WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().
#Switch complete
The instance '172.16.16.11:3306' was successfully elected as primary.2. Switch single master / multi master mode
In command line mode, you can call group_replication_switch_to_single_primary_mode() and group_replication_switch_to_multi_primary_mode() to switch single master / multi master mode.
#Just call the function directly
mysql> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
#View the status of each node
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | af39db70-6850-11ec-94c9-00155d064000 | 172.16.16.10 | 3306 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | b05c0838-6850-11ec-a06b-00155d064000 | 172.16.16.11 | 3306 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | b0f86046-6850-11ec-92fe-00155d064000 | 172.16.16.12 | 3306 | ONLINE | PRIMARY | 8.0.25 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
#When switching to single master mode, you can specify the server of a node_ UUID, if not specified, a new master node will be automatically selected according to the rules
#Here, I choose the specified mgr3 node as the new master node
mysql> select group_replication_switch_to_single_primary_mode('b0f86046-6850-11ec-92fe-00155d064000');
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('b0f86046-6850-11ec-92fe-00155d064000') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully. |
+-----------------------------------------------------------------------------------------+In MySQL Shell, you can call switchToSinglePrimaryMode() and switchToMultiPrimaryMode() functions to switch. Similarly, the function switchToSinglePrimaryMode() can also specify a node as the new master node.
MySQL 172.16.16.10:3306 ssl JS > var c=dba.getCluster()
#Switch to multi master mode
MySQL 172.16.16.10:3306 ssl JS > c.switchToMultiPrimaryMode()
#Switch to single master mode, where I specify mgr2 node as the new master
MySQL 172.16.16.10:3306 ssl JS > c.switchToSinglePrimaryMode("172.16.16.11:3306")Note that when it is already in single master mode, whether it is group_replication_switch_to_single_primary_mode() or switchToSinglePrimaryMode() function, when another node is specified, there will be no switching, but there will be no error, only prompt.
3. Add a new node
Next, we demonstrate how to add a new node to the MGR cluster.
First of all, complete MySQL Server initialization, create MGR special account, set MGR service channel and other pre work. Refer to the above for operations in this part 3. Install and deploy MGR cluster.
Next, execute the command start group directly_ Replication starts the MGR service, and the new node will enter the step of distributed recovery. It will automatically select one of the existing nodes as the donor, and decide whether to directly read the binlog for recovery or use Clone for full recovery.
If the MGR cluster has been running online for a period of time and has a certain stock of data, it may be slow for new nodes to join. It is recommended to manually use clone for a full replication. I still remember that when I created the MGR special account, I added backup to it_ Admin authorization comes in handy at this time. Clone needs this permission.
The following shows how to use Clone for a full data recovery. It is assumed that the node to be added is 172.16.16.13 (named mgr4).
#Set up donors on mgr4 #In order to reduce the impact on the Primary node, it is recommended to select other Secondary nodes mysql> set global clone_valid_donor_list='172.16.16.11:3306'; #Stop mgr service (if any) and close super_read_only mode, and then start copying data #Note that the port to be filled in here is 3306 (MySQL normal service port), not 33061, a special port for MGR service mysql> stop group_replication; set global super_read_only=0; clone INSTANCE FROM GreatSQL@172.16.16.11:3306 IDENTIFIED BY 'GreatSQL';
After the full amount of data is copied, the node will be restarted automatically. After restarting, confirm the group again_ replication_ group_ name,group_replication_local_address,group_ replication_ group_ Whether the values of seeds are correct. If there is no problem, execute start group_ After replication, the node should be able to join the cluster normally.
It is easier to add a new node with MySQL Shell. First perform MySQL Server initialization and execute DBA dba. Configureinstance() creates a special MGR account. Then, connect to the Primary node and directly call addInstance() function:
#Connect to Primary node
$ mysqlsh --uri GreatSQL@172.16.16.10:3306
MySQL 172.16.16.10:3306 ssl JS > var c=dba.getCluster()
MySQL 172.16.16.10:3306 ssl JS > c.addInstance('GreatSQL@172.16.16.13:3306')
WARNING: A GTID set check of the MySQL instance at '172.16.16.13:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
...
NOTE: 172.16.16.13:3306 is being cloned from 172.16.16.10:3306 #< -- automatically select a donor node
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: 172.16.16.13:3306 is shutting down...
* Waiting for server restart... ready
* 172.16.16.13:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.43 MB transferred in about 1 second (~72.43 MB/s)
Incremental state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: '172.16.16.13:3306' is being recovered from '172.16.16.12:3306'
* Distributed recovery has finished
#The new node is successfully joined
The instance '172.16.16.13:3306' was successfully added to the cluster.
#Confirm that the addition is successful and has been listed in the MGR cluster list
MySQL 172.16.16.10:3306 ssl JS > c.describe()
{
"clusterName": "MGR1",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "172.16.16.10:3306",
"label": "172.16.16.10:3306",
"role": "HA"
},
{
"address": "172.16.16.11:3306",
"label": "172.16.16.11:3306",
"role": "HA"
},
{
"address": "172.16.16.12:3306",
"label": "172.16.16.12:3306",
"role": "HA"
},
{
"address": "172.16.16.13:3306", <--Newly added node
"label": "172.16.16.13:3306",
"role": "HA"
}
],
"topologyMode": "Single-Primary"
}
} Confirm that the new node is added successfully.
4. Delete node
In the command line mode, if a node wants to exit the MGR cluster, directly execute stop group_replication is enough. If this node just temporarily exits the cluster and wants to add back the cluster later, execute start group_replication can automatically rejoin. If you want to completely exit the cluster, stop the MGR service and execute reset master; reset slave all; Just reset all replication (including MGR) related information.
In MySQL Shell, just call the removeInstance() function to delete a node, for example:
MySQL 172.16.16.10:3306 ssl JS > c.removeInstance('172.16.16.13:3306');
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.
Instance '172.16.16.13:3306' is attempting to leave the cluster...
The instance '172.16.16.13:3306' was successfully removed from the cluster.This kicks the node out of the cluster and resets the group_replication_group_seeds and group_replication_local_address two option values. If you want to add the node back to the cluster after calling addInstance().
5. Add back the node that exited abnormally
When a node is disconnected from the MGR cluster due to abnormal conditions such as network disconnection and instance crash, the status of the node will become UNREACHABLE until it exceeds the group_ replication_ member_ expel_ After timeout + 5 seconds, the cluster will kick out the node. Wait until the node starts again and execute start group_replication. Under normal circumstances, the node should be able to automatically rejoin the cluster.
In MySQL Shell, you can call rejoinInstance() function to add the abnormal node back to the cluster:
MySQL 172.16.16.10:3306 ssl JS > c.rejoinInstance('172.16.16.13:3306');
Rejoining instance '172.16.16.13:3306' to cluster 'MGR1'...
The instance '172.16.16.13:3306' was successfully rejoined to the cluster.6. Restart MGR cluster
Under normal circumstances, when the Primary node in the MGR cluster exits, the remaining nodes will automatically select a new Primary node. When the last node also exits, the whole MGR cluster is shut down. At this time, after starting the MGR service, any node will not automatically become the Primary node. You need to set the group before starting the MGR service_ replication_ bootstrap_ Group = on, make it the boot node, and then start the MGR service, it will become the Primary node, and other subsequent nodes can join the cluster normally. It can be tested by yourself. There is no demonstration here.
P.S. remember to reset the option group after the first node is started_ replication_ bootstrap_ Group = off to avoid MGR cluster splitting in subsequent operations.
If you restart the MGR cluster with MySQL Shell, call the rebootClusterFromCompleteOutage() function. It will automatically judge the status of each node, select one of them as the Primary node, and then pull up the MGR service on each node to complete the restart of the MGR cluster. You can refer to this article: Wan Da #12, after the MGR cluster hangs up, how can we choose the master automatically without manual intervention
7. Summary
This paper introduces several common management and maintenance methods of MGR cluster, including switching master node, switching single master / multi master mode, adding node, deleting node, rejoining abnormal node, restarting the whole MGR cluster, etc. On the whole, using MySQL Shell to manage MGR clusters will be simpler and more convenient, and it is also conducive to the encapsulation of the management platform. However, the way of manual operation should not be forgotten. Sometimes there may be no supporting MySQL Shell tool, so you have to rely on manual operation.
References, documents
- MySQL 8.0 Reference Manual
- Database Kernel Development - Wen Zhenghu
- Group Replication principle - song libing
Disclaimers
Due to the limited personal level, mistakes and omissions are inevitable in the column. Do not directly copy the commands and methods in the document and apply them directly to the online production environment. Readers must fully understand and verify the test environment before formal implementation, so as to avoid damage or damage to the production environment.
Enjoy GreatSQL :)
MGR column in simple terms:
1. Introduction to MGR | MGR in simple terms https://mp.weixin.qq.com/s/lbU5KaY2hEPq4gnwJ8PgCg
2. Group replication technology architecture | MGR in simple terms https://mp.weixin.qq.com/s/6__H3-HFsBtZk336EaAcrQ
3. Install and deploy MGR cluster | explain MGR in simple terms https://mp.weixin.qq.com/s/Nh37KuBQZ137BpmqG26_OQ
4. Use MySQL Shell to install and deploy MGR cluster | explain MGR in simple terms https://mp.weixin.qq.com/s/51ESDPgeuXqsgib6wb87iQ
5.MGR management and maintenance | Mgr in simple terms https://mp.weixin.qq.com/s/D5obkekTClZEdN2KQ9xiXg
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee: https://gitee.com/GreatSQL/GreatSQL
GitHub: https://github.com/GreatSQL/GreatSQL
Bilibili: https://space.bilibili.com/1363850082/video