MySQL8.0 MGR的維護(hù)管理
更新時(shí)間：2024年08月19日 10:35:13 作者：進(jìn)擊的CJR
數(shù)據(jù)庫(kù)已成為企業(yè)和管理員們最為關(guān)注的核心組件之一,本文主要介紹了MySQL8.0 MGR的維護(hù)管理,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
現(xiàn)在有個(gè)三節(jié)點(diǎn)的MGR集群：
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 | b4a92e9f-4416-11ef-ab6d-5254009ccf5d | VM-20-8-centos | 3381 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | ba8d6325-4416-11ef-94a6-5254009ccf5d | VM-20-8-centos | 3382 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | c0bbb3f3-4416-11ef-b304-5254009ccf5d | VM-20-8-centos | 3383 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
切換主節(jié)點(diǎn)
當(dāng)主節(jié)點(diǎn)需要進(jìn)行維護(hù)時(shí)，或者執(zhí)行滾動(dòng)升級(jí)時(shí)，就可以對(duì)其進(jìn)行切換，將主節(jié)點(diǎn)切換到其他節(jié)點(diǎn)。
在命令行模式下，可以使用 group_replication_set_as_primary() 這個(gè)udf實(shí)現(xiàn)切換，例如：
mysql> select group_replication_set_as_primary('ba8d6325-4416-11ef-94a6-5254009ccf5d');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('ba8d6325-4416-11ef-94a6-5254009ccf5d') |
+--------------------------------------------------------------------------+
| Primary server switched to: ba8d6325-4416-11ef-94a6-5254009ccf5d |
+--------------------------------------------------------------------------+
1 row in set (1.01 sec)
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 | b4a92e9f-4416-11ef-ab6d-5254009ccf5d | VM-20-8-centos | 3381 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | ba8d6325-4416-11ef-94a6-5254009ccf5d | VM-20-8-centos | 3382 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | c0bbb3f3-4416-11ef-b304-5254009ccf5d | VM-20-8-centos | 3383 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
也可以使用mysql shell進(jìn)行切換
MySQL 159.75.158.18:3381 ssl JS > var cluster = dba.getCluster()
MySQL 159.75.158.18:3381 ssl JS > cluster.setPrimaryInstance('admin@159.75.158.18:3383')
Setting instance '159.75.158.18:3383' as the primary instance of cluster 'MGR1'...
Instance 'VM-20-8-centos:3381' remains SECONDARY.
Instance 'VM-20-8-centos:3382' was switched from PRIMARY to SECONDARY.
Instance 'VM-20-8-centos:3383' was switched from SECONDARY to PRIMARY.
WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().
The instance '159.75.158.18:3383' was successfully elected as primary.
切換單主/多主模式
在命令行模式下，可以調(diào)用 group_replication_switch_to_single_primary_mode() 和 group_replication_switch_to_multi_primary_mode() 來(lái)切換單主/多主模式。
mysql> select group_replication_switch_to_multi_primary_mode(); +--------------------------------------------------+ | group_replication_switch_to_multi_primary_mode() | +--------------------------------------------------+ | Mode switched to multi-primary successfully. | +--------------------------------------------------+ 1 row in set (1.01 sec) 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 | b4a92e9f-4416-11ef-ab6d-5254009ccf5d | VM-20-8-centos | 3381 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | ba8d6325-4416-11ef-94a6-5254009ccf5d | VM-20-8-centos | 3382 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | c0bbb3f3-4416-11ef-b304-5254009ccf5d | VM-20-8-centos | 3383 | ONLINE | PRIMARY | 8.0.25 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
#切換成單主模式時(shí)可以指定某個(gè)節(jié)點(diǎn)的 server_uuid，如果不指定則會(huì)根據(jù)規(guī)則自動(dòng)選擇一個(gè)新的主節(jié)點(diǎn)
mysql> select group_replication_switch_to_single_primary_mode('b4a92e9f-4416-11ef-ab6d-5254009ccf5d');
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('b4a92e9f-4416-11ef-ab6d-5254009ccf5d') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully. |
+-----------------------------------------------------------------------------------------+
1 row in set (1.01 sec)
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 | b4a92e9f-4416-11ef-ab6d-5254009ccf5d | VM-20-8-centos | 3381 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | ba8d6325-4416-11ef-94a6-5254009ccf5d | VM-20-8-centos | 3382 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | c0bbb3f3-4416-11ef-b304-5254009ccf5d | VM-20-8-centos | 3383 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)同樣，也可以使用mysql shell進(jìn)行調(diào)用 switchToSinglePrimaryMode() 以及 switchToMultiPrimaryMode() 函數(shù)進(jìn)行切換。同樣地，函數(shù) switchToSinglePrimaryMode() 里也可以指定某個(gè)節(jié)點(diǎn)作為新的主節(jié)點(diǎn)。
MySQL 159.75.158.18:3381 ssl JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'MGR1' to Multi-Primary mode...
Instance 'VM-20-8-centos:3381' remains PRIMARY.
Instance 'VM-20-8-centos:3382' was switched from SECONDARY to PRIMARY.
Instance 'VM-20-8-centos:3383' was switched from SECONDARY to PRIMARY.
The cluster successfully switched to Multi-Primary mode.
MySQL 159.75.158.18:3381 ssl JS > cluster.switchToSinglePrimaryMode('159.75.158.18:3381')
Switching cluster 'MGR1' to Single-Primary mode...
Instance 'VM-20-8-centos:3381' remains PRIMARY.
Instance 'VM-20-8-centos:3382' was switched from PRIMARY to SECONDARY.
Instance 'VM-20-8-centos:3383' was switched from PRIMARY to 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().
WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.
The cluster successfully switched to Single-Primary mode.
添加新節(jié)點(diǎn)
#在待接入節(jié)點(diǎn)上設(shè)置捐獻(xiàn)者 #為了降低對(duì)Primary節(jié)點(diǎn)的影響，建議選擇其他Secondary節(jié)點(diǎn) mysql> set global clone_valid_donor_list='159.75.158.18:3382'; #停掉mgr服務(wù)（如果有的話），關(guān)閉super_read_only模式，然后開(kāi)始復(fù)制數(shù)據(jù) #注意這里要填寫(xiě)的端口是3382（MySQL正常服務(wù)端口），而不是33821這個(gè)MGR服務(wù)專(zhuān)用端口 mysql> stop group_replication; set global super_read_only=0; clone INSTANCE FROM admin@159.75.158.18:3382 IDENTIFIED BY '****';
使用mysql shell添加更容易些
cluster.addInstance('admin@159.75.158.18:3383')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'VM-20-8-centos:3383' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Incremental state recovery was selected because it seems to be safely usable.
Validating instance configuration at 159.75.158.18:3383...
This instance reports its own address as VM-20-8-centos:3383
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'VM-20-8-centos:33831'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
State recovery already finished for 'VM-20-8-centos:3383'
The instance 'VM-20-8-centos:3383' was successfully added to the cluster.
刪除節(jié)點(diǎn)
在命令行模式下，一個(gè)節(jié)點(diǎn)想退出MGR集群，直接執(zhí)行 stop group_replication 即可，如果這個(gè)節(jié)點(diǎn)只是臨時(shí)退出集群，后面還想加回集群，則執(zhí)行 start group_replication 即可自動(dòng)再加入。而如果是想徹底退出集群，則停止MGR服務(wù)后，執(zhí)行 reset master; reset slave all; 重置所有復(fù)制（包含MGR）相關(guān)的信息就可以了。
登錄3383端口的節(jié)點(diǎn) mysql> stop group_replication; Query OK, 0 rows affected (4.55 sec) 再次查看集群 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 | b4a92e9f-4416-11ef-ab6d-5254009ccf5d | VM-20-8-centos | 3381 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | ba8d6325-4416-11ef-94a6-5254009ccf5d | VM-20-8-centos | 3382 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec)
使用mysql shell里，只需調(diào)用 removeInstance() 函數(shù)即可刪除某個(gè)節(jié)點(diǎn)，例如：
c.removeInstance(‘159.75.158.18:3383’);
c.removeInstance('159.75.158.18:3383')
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 '159.75.158.18:3383' is attempting to leave the cluster...
The instance '159.75.158.18:3383' was successfully removed from the cluster.
異常退出的節(jié)點(diǎn)重新加回
當(dāng)節(jié)點(diǎn)因?yàn)榫W(wǎng)絡(luò)斷開(kāi)、實(shí)例crash等異常情況與MGR集群斷開(kāi)連接后，這個(gè)節(jié)點(diǎn)的狀態(tài)會(huì)變成 UNREACHABLE，待到超過(guò) group_replication_member_expel_timeout + 5 秒后，集群會(huì)踢掉該節(jié)點(diǎn)。等到這個(gè)節(jié)點(diǎn)再次啟動(dòng)并執(zhí)行 start group_replication，正常情況下，該節(jié)點(diǎn)應(yīng)能自動(dòng)重新加回集群。
在mysql shell里，可以調(diào)用 rejoinInstance() 函數(shù)將異常的節(jié)點(diǎn)重新加回集群：
cluster.rejoinInstance(‘159.75.158.18:3383’)
cluster.rejoinInstance('159.75.158.18:3383')
Rejoining instance 'VM-20-8-centos:3383' to cluster 'MGR1'...
The instance 'VM-20-8-centos:3383' was successfully rejoined to the cluster.重啟MGR集群
正常情況下，MGR集群中的Primary節(jié)點(diǎn)退出時(shí)，剩下的節(jié)點(diǎn)會(huì)自動(dòng)選出新的Primary節(jié)點(diǎn)。當(dāng)最后一個(gè)節(jié)點(diǎn)也退出時(shí)，相當(dāng)于整個(gè)MGR集群都關(guān)閉了。這時(shí)候任何一個(gè)節(jié)點(diǎn)啟動(dòng)MGR服務(wù)后，都不會(huì)自動(dòng)成為Primary節(jié)點(diǎn)，需要在啟動(dòng)MGR服務(wù)前，先設(shè)置 group_replication_bootstrap_group=ON，使其成為引導(dǎo)節(jié)點(diǎn)，再啟動(dòng)MGR服務(wù)，它才會(huì)成為Primary節(jié)點(diǎn)，[參考文章](后續(xù)啟動(dòng)的其他節(jié)點(diǎn)也才能正常加入集群。
P.S，第一個(gè)節(jié)點(diǎn)啟動(dòng)完畢后，記得重置選項(xiàng) group_replication_bootstrap_group=OFF，避免在后續(xù)的操作中導(dǎo)致MGR集群分裂。
如果是用mysql shell重啟MGR集群，調(diào)用 rebootClusterFromCompl)eteOutage() 函數(shù)即可，它會(huì)自動(dòng)判斷各節(jié)點(diǎn)的狀態(tài)，選擇其中一個(gè)作為Primary節(jié)點(diǎn)，然后拉起各節(jié)點(diǎn)上的MGR服務(wù)，完成MGR集群重啟。
參考文章
https://gitee.com/GreatSQL/GreatSQL-Doc/blob/master/deep-dive-mgr/deep-dive-mgr-05.md
到此這篇關(guān)于MySQL8.0 MGR的維護(hù)管理的文章就介紹到這了,更多相關(guān)MySQL8.0 MGR內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql中InnoDB與MyISAM索引差異詳解(最新整理)
InnoDB和MyISAM在索引實(shí)現(xiàn)和特性上有差異,包括聚集索引、非聚集索引、事務(wù)支持、并發(fā)控制、覆蓋索引、主鍵約束、外鍵支持和物理存儲(chǔ)結(jié)構(gòu)等方面,InnoDB更適合事務(wù)型應(yīng)用,而MyISAM適合只讀或讀多寫(xiě)少的場(chǎng)景,本文介紹Mysql中InnoDB與MyISAM索引差異,感興趣的朋友一起看看吧2025-03-03
MySQL表的內(nèi)外連接和視圖使用實(shí)戰(zhàn)練習(xí)
這篇文章主要給大家介紹了關(guān)于MySQL表的內(nèi)外連接和視圖使用的相關(guān)資料,MySQL的內(nèi)外連接查詢是用于將兩個(gè)或多個(gè)表中的數(shù)據(jù)進(jìn)行關(guān)聯(lián)查詢的一種方法,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01
這篇文章主要介紹了MySql COALESCE函數(shù)使用方法代碼案例,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-08-08
mysql 獲取今天、昨天0點(diǎn)時(shí)間戳的實(shí)例
今天小編就為大家分享一篇mysql 獲取今天、昨天0點(diǎn)時(shí)間戳的實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-05-05
mysql如何根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表
這篇文章主要介紹了mysql根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表的操作方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-07-07
mysql數(shù)據(jù)庫(kù)自動(dòng)添加創(chuàng)建時(shí)間及更新時(shí)間
在實(shí)際應(yīng)用中我們時(shí)常會(huì)需要用到創(chuàng)建時(shí)間和更新時(shí)間這兩個(gè)字段,下面這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫(kù)自動(dòng)添加創(chuàng)建時(shí)間及更新時(shí)間的相關(guān)資料,需要的朋友可以參考下2022-05-05
這篇文章主要介紹了MySQL8.0.23安裝超詳細(xì)教程,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
PHP函數(shù)使用說(shuō)明，應(yīng)用舉例，精簡(jiǎn)點(diǎn)評(píng),希望對(duì)您學(xué)習(xí)php有所幫助。2010-07-07
MySQL8.x使用GRANT為用戶賦權(quán)時(shí)報(bào)錯(cuò)的解決
這篇文章主要介紹了MySQL8.x使用GRANT為用戶賦權(quán)時(shí)報(bào)錯(cuò)的解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
基于sqlalchemy對(duì)mysql實(shí)現(xiàn)增刪改查操作
這篇文章主要介紹了基于sqlalchemy對(duì)mysql實(shí)現(xiàn)增刪改查操作,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-06-06