欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL8.0 MGR的維護(hù)管理

 更新時間：2024年08月19日 10:35:13   作者：進(jìn)擊的CJR  
數(shù)據(jù)庫已成為企業(yè)和管理員們最為關(guān)注的核心組件之一,本文主要介紹了MySQL8.0 MGR的維護(hù)管理,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

現(xiàn)在有個三節(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ù)時，或者執(zhí)行滾動升級時，就可以對其進(jìn)行切換，將主節(jié)點(diǎn)切換到其他節(jié)點(diǎn)。

在命令行模式下，可以使用 group_replication_set_as_primary() 這個udf實現(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() 來切換單主/多主模式。

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)

#切換成單主模式時可以指定某個節(jié)點(diǎn)的 server_uuid，如果不指定則會根據(jù)規(guī)則自動選擇一個新的主節(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() 里也可以指定某個節(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)者
#為了降低對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模式，然后開始復(fù)制數(shù)據(jù)
#注意這里要填寫的端口是3382（MySQL正常服務(wù)端口），而不是33821這個MGR服務(wù)專用端口
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)

在命令行模式下，一個節(jié)點(diǎn)想退出MGR集群，直接執(zhí)行 stop group_replication 即可，如果這個節(jié)點(diǎn)只是臨時退出集群，后面還想加回集群，則執(zhí)行 start group_replication 即可自動再加入。而如果是想徹底退出集群，則停止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ù)即可刪除某個節(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)因為網(wǎng)絡(luò)斷開、實例crash等異常情況與MGR集群斷開連接后，這個節(jié)點(diǎn)的狀態(tài)會變成 UNREACHABLE，待到超過 group_replication_member_expel_timeout + 5 秒后，集群會踢掉該節(jié)點(diǎn)。等到這個節(jié)點(diǎn)再次啟動并執(zhí)行 start group_replication，正常情況下，該節(jié)點(diǎn)應(yī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)退出時，剩下的節(jié)點(diǎn)會自動選出新的Primary節(jié)點(diǎn)。當(dāng)最后一個節(jié)點(diǎn)也退出時，相當(dāng)于整個MGR集群都關(guān)閉了。這時候任何一個節(jié)點(diǎn)啟動MGR服務(wù)后，都不會自動成為Primary節(jié)點(diǎn)，需要在啟動MGR服務(wù)前，先設(shè)置 group_replication_bootstrap_group=ON，使其成為引導(dǎo)節(jié)點(diǎn)，再啟動MGR服務(wù)，它才會成為Primary節(jié)點(diǎn)，[參考文章](后續(xù)啟動的其他節(jié)點(diǎn)也才能正常加入集群。

P.S，第一個節(jié)點(diǎn)啟動完畢后，記得重置選項 group_replication_bootstrap_group=OFF，避免在后續(xù)的操作中導(dǎo)致MGR集群分裂。

如果是用mysql shell重啟MGR集群，調(diào)用 rebootClusterFromCompl)eteOutage() 函數(shù)即可，它會自動判斷各節(jié)點(diǎn)的狀態(tài)，選擇其中一個作為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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • Mysql中InnoDB與MyISAM索引差異詳解(最新整理)

    Mysql中InnoDB與MyISAM索引差異詳解(最新整理)

    InnoDB和MyISAM在索引實現(xiàn)和特性上有差異,包括聚集索引、非聚集索引、事務(wù)支持、并發(fā)控制、覆蓋索引、主鍵約束、外鍵支持和物理存儲結(jié)構(gòu)等方面,InnoDB更適合事務(wù)型應(yīng)用,而MyISAM適合只讀或讀多寫少的場景,本文介紹Mysql中InnoDB與MyISAM索引差異,感興趣的朋友一起看看吧
    2025-03-03
  • MySQL表的內(nèi)外連接和視圖使用實戰(zhàn)練習(xí)

    MySQL表的內(nèi)外連接和視圖使用實戰(zhàn)練習(xí)

    這篇文章主要給大家介紹了關(guān)于MySQL表的內(nèi)外連接和視圖使用的相關(guān)資料,MySQL的內(nèi)外連接查詢是用于將兩個或多個表中的數(shù)據(jù)進(jìn)行關(guān)聯(lián)查詢的一種方法,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2024-01-01
  • MySql COALESCE函數(shù)使用方法代碼案例

    MySql COALESCE函數(shù)使用方法代碼案例

    這篇文章主要介紹了MySql COALESCE函數(shù)使用方法代碼案例,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2020-08-08
  • mysql 獲取今天、昨天0點(diǎn)時間戳的實例

    mysql 獲取今天、昨天0點(diǎn)時間戳的實例

    今天小編就為大家分享一篇mysql 獲取今天、昨天0點(diǎn)時間戳的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2018-05-05
  • mysql如何根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表

    mysql如何根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表

    這篇文章主要介紹了mysql根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表的操作方法，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2023-07-07
  • mysql數(shù)據(jù)庫自動添加創(chuàng)建時間及更新時間

    mysql數(shù)據(jù)庫自動添加創(chuàng)建時間及更新時間

    在實際應(yīng)用中我們時常會需要用到創(chuàng)建時間和更新時間這兩個字段,下面這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫自動添加創(chuàng)建時間及更新時間的相關(guān)資料,需要的朋友可以參考下
    2022-05-05
  • MySQL8.0.23安裝超詳細(xì)教程

    MySQL8.0.23安裝超詳細(xì)教程

    這篇文章主要介紹了MySQL8.0.23安裝超詳細(xì)教程,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2021-01-01
  • PHP函數(shù)使用說明(補(bǔ)充)

    PHP函數(shù)使用說明(補(bǔ)充)

    PHP函數(shù)使用說明，應(yīng)用舉例，精簡點(diǎn)評,希望對您學(xué)習(xí)php有所幫助。
    2010-07-07
  • MySQL8.x使用GRANT為用戶賦權(quán)時報錯的解決

    MySQL8.x使用GRANT為用戶賦權(quán)時報錯的解決

    這篇文章主要介紹了MySQL8.x使用GRANT為用戶賦權(quán)時報錯的解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2023-04-04
  • 基于sqlalchemy對mysql實現(xiàn)增刪改查操作

    基于sqlalchemy對mysql實現(xiàn)增刪改查操作

    這篇文章主要介紹了基于sqlalchemy對mysql實現(xiàn)增刪改查操作,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2020-06-06

最新評論