最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

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ù)專用端口
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分區(qū)表的正確使用方法

    MySQL分區(qū)表的正確使用方法

    這篇文章主要給大家介紹了關(guān)于MySQL分區(qū)表的正確使用方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2019-01-01
  • MySQL 隨機(jī)查詢數(shù)據(jù)與隨機(jī)更新數(shù)據(jù)實(shí)現(xiàn)代碼

    MySQL 隨機(jī)查詢數(shù)據(jù)與隨機(jī)更新數(shù)據(jù)實(shí)現(xiàn)代碼

    以下的文章主要講述的是MySQL隨機(jī)查詢數(shù)據(jù)、MySQL隨機(jī)更新數(shù)據(jù)的實(shí)際應(yīng)用以及對(duì)MySQL隨機(jī)查詢數(shù)據(jù)、MySQL隨機(jī)更新數(shù)據(jù)的實(shí)際應(yīng)用代碼的描述，以下就是文章的主要內(nèi)容描述，望你會(huì)有所收獲。
    2010-06-06
  • mysql found_row()使用詳解

    mysql found_row()使用詳解

    在參考手冊(cè)中對(duì)found_rows函數(shù)的描述是： it is desirable to know how many rows the statement would have returned without the LIMIT. 也就是說(shuō)，它返回值是如果SQL語(yǔ)句沒(méi)有加LIMIT的情況下返回的數(shù)值。
    2016-05-05
  • Mysql主從同步的實(shí)現(xiàn)原理

    Mysql主從同步的實(shí)現(xiàn)原理

    這篇文章主要介紹了Mysql主從同步的實(shí)現(xiàn)原理，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧
    2017-03-03
  • mysql-8.0.15-winx64 解壓版安裝教程及退出的三種方式

    mysql-8.0.15-winx64 解壓版安裝教程及退出的三種方式

    本文通過(guò)圖文并茂的形式給大家介紹了mysql-8.0.15-winx64 解壓版安裝,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
    2019-04-04
  • MySQL rand函數(shù)實(shí)現(xiàn)隨機(jī)數(shù)的方法

    MySQL rand函數(shù)實(shí)現(xiàn)隨機(jī)數(shù)的方法

    在mysql中，使用隨機(jī)數(shù)寫(xiě)一個(gè)語(yǔ)句能一下更新幾百條MYSQL數(shù)據(jù)嗎？答案是肯定的，使用MySQL rand函數(shù)，就可以使現(xiàn)在隨機(jī)數(shù)
    2016-09-09
  • CMS不要讓MySQL為你流淚

    CMS不要讓MySQL為你流淚

    MySQL是中小型網(wǎng)站普遍使用的數(shù)據(jù)庫(kù)之一，然而，很多人并不清楚MySQL到底能支持多大的數(shù)據(jù)量，再加上某些國(guó)內(nèi)CMS廠商把數(shù)據(jù)承載量的責(zé)任推給它，導(dǎo)致很多不了解MySQL的站長(zhǎng)對(duì)它產(chǎn)生了很多誤解
    2008-12-12
  • mysql提示got timeout reading communication packets的解決方法

    mysql提示got timeout reading communication packets的解決方法

    今天發(fā)現(xiàn)mysql錯(cuò)誤日志中出現(xiàn)got timeout reading communication packets，一般情況因?yàn)椴糠猪?yè)面生成有問(wèn)題導(dǎo)致，這里簡(jiǎn)單分享一下解決方法
    2019-05-05
  • MySQL用limit方式實(shí)現(xiàn)分頁(yè)的實(shí)例方法

    MySQL用limit方式實(shí)現(xiàn)分頁(yè)的實(shí)例方法

    在本篇文章中小編給大家整理了一篇關(guān)于MySQL用limit方式實(shí)現(xiàn)分頁(yè)的實(shí)例方法，有需要的朋友們可以參考學(xué)習(xí)下。
    2020-01-01
  • 解析MySQL中mysqldump工具的基本用法

    解析MySQL中mysqldump工具的基本用法

    本篇文章是對(duì)MySQL中mysqldump工具的基本用法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
    2013-06-06

最新評(píng)論

新野县| 乾安县| 湟中县| 肇源县| 鹤岗市| 贺州市| 泾阳县| 涞源县| 平顺县| 武强县| 赤城县| 哈巴河县| 来凤县| 水富县| 新疆| 繁峙县| 正镶白旗| 阜新市| 司法| 河北区| 融水| 礼泉县| 沂源县| 义马市| 乌拉特前旗| 霍林郭勒市| 陈巴尔虎旗| 清水河县| 桑日县| 六安市| 晋中市| 东阿县| 凤城市| 平顶山市| 青冈县| 娱乐| 休宁县| 麦盖提县| 南雄市| 同心县| 读书|