利用MySQL Shell安裝部署MGR叢集的詳細過程

2022-02-16 19:01:11

MySQL Shell是一個使用者端工具，可用於方便管理和操作MySQL，支援SQL、JavaScript、Python等多種語言，也包括完善的API。MySQL Shell支援檔案型和關係型資料庫模式，通過X DevAPI可以管理檔案型資料，通過AdminAPI可以管理InnoDB Cluster、InnoDB ClusterSet及InnoDB ReplicaSet等。

1. 安裝準備

準備好下面三臺伺服器：

IP	埠	角色
172.16.16.10	3306	mgr1
172.16.16.11	3306	mgr2
172.16.16.12	3306	mgr3

確保三個節點間的網路是可以互通的，並且沒有針對3306和33061埠的防火牆攔截規則。

利用yum安裝MySQL Shell，版本選擇和GreatSQL相同的8.0.25：

$ yum install mysql-shell-8.0.25

假定已經參考前文 3. 安裝部署MGR叢集做好MySQL Server的初始化並啟動三個範例。

接下來直接利用MySQL Shell部署MGR。

2. 利用MySQL Shell構建MGR叢集

利用MySQL Shell構建MGR叢集比較簡單，主要有幾個步驟：

檢查範例是否滿足條件。
建立並初始化一個叢集。
逐個新增範例。

首先，用管理員賬號 root 連線到第一個節點：

#在本地通過socket方式登入
$ mysqlsh -Spath/mysql.sock root@localhost
Please provide the password for 'root@.%2Fmysql.sock': ********
Save password for 'root@.%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.25
...

執行命令 status 檢視當前節點的狀態，確認連線正常可用。

執行 dba.configureInstance() 命令開始檢查當前範例是否滿足安裝MGR叢集的條件，如果不滿足可以直接設定成為MGR叢集的一個節點：

MySQL localhost JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 172.16.16.10:3306
#提示當前的使用者是管理員，不能直接用於MGR叢集，需要新建一個賬號
ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 2 <-- 這裡我們選擇方案2，即建立一個最小許可權賬號
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: GreatSQL
Password for new account: ********
Confirm password: ********
applierWorkerThreads will be set to the default value of 4.
The instance '172.16.16.10:3306' is valid to be used in an InnoDB cluster.
Cluster admin user 'GreatSQL'@'%' created.
The instance '172.16.16.10:3306' is already ready to be used in an InnoDB cluster.
Successfully enabled parallel appliers.

完成檢查並建立完新使用者後，退出當前的管理員賬戶，並用新建立的MGR專用賬戶登入，準備初始化建立一個新叢集：

$ mysqlsh --uri GreatSQL@172.16.16.10:3306
Please provide the password for 'GreatSQL@172.16.16.10:3306': ********
Save password for 'GreatSQL@172.16.16.10:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.25

...
#定義一個變數名c，方便下面參照
 MySQL  172.16.16.10:3306 ssl  JS > var c = dba.createCluster('MGR1');
A new InnoDB cluster will be created on instance '172.16.16.10:3306'.
Validating instance configuration at 172.16.16.10:3306...
This instance reports its own address as 172.16.16.10:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '172.16.16.10:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'MGR1' on '172.16.16.10:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
 MySQL  172.16.16.10:3306 ssl  JS >

這就完成了MGR叢集的初始化並加入第一個節點（引導節點）。

接下來，用同樣方法先用 root 賬號分別登入到另外兩個節點，完成節點的檢查並建立最小許可權級別使用者（此過程略過。。。注意各節點上建立的使用者名稱、密碼都要一致），之後回到第一個節點，執行 addInstance() 新增另外兩個節點。

MySQL 172.16.16.10:3306 ssl JS > c.addInstance('GreatSQL@172.16.16.11:3306'); <-- 這裡要指定MGR專用賬號

WARNING: A GTID set check of the MySQL instance at '172.16.16.11:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
172.16.16.11:3306 has the following errant GTIDs that do not exist in the cluster:
b05c0838-6850-11ec-a06b-00155d064000:1
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of 172.16.16.11:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): Clone <-- 選擇用Clone方式從第一個節點全量複製資料
Validating instance configuration at 172.16.16.11:3306...
This instance reports its own address as 172.16.16.11:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '172.16.16.11:33061'. 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.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: 172.16.16.11:3306 is being cloned from 172.16.16.10:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: 172.16.16.11:3306 is shutting down... <-- 資料Clone完成，準備重啟範例。如果該範例無法完成自動重啟，則需要手動啟動
* Waiting for server restart... ready
* 172.16.16.11: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)
State recovery already finished for '172.16.16.11:3306'
The instance '172.16.16.11:3306' was successfully added to the cluster. <-- 新範例加入成功
MySQL 172.16.16.10:3306 ssl JS >

用同樣的方法，將 172.16.16.12:3306 範例也加入到叢集中。

現在，一個有這三節點的MGR叢集已經部署完畢，來確認下：

 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",
                "address": "172.16.16.12:3306",
                "label": "172.16.16.12:3306",
            }
        ],
        "topologyMode": "Single-Primary"
    }
}

或者執行 c.status() 可以列印出叢集更多的資訊。

至此，利用MySQL Shell構建一個三節點的MGR叢集做好了，可以嘗試向 Primary 節點寫入資料觀察測試。

3. MySQL Shell接管現存的MGR叢集

對於已經在執行中的MGR叢集，也是可以用MySQL Shell接管的。只需要在呼叫 createCluster() 函數時，加上 adoptFromGR:true 選項即可。實際上不加這個選項的話，MySQL Shell也會自動檢測到該MGR叢集已存在，並詢問是否要接管。

在這裡簡單演示下：

#不加上 adoptFromGr:true 選項
MySQL 172.16.16.10:3306 ssl JS > var c=dba.createCluster('MGR1');
A new InnoDB cluster will be created on instance '172.16.16.10:3306'.

You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB cluster based on this replication group? [Y/n]:

可以看到，會有提示資訊詢問是否要接管。

如果加上 adoptFromGr:true 選項，則會直接建立叢集，不再詢問：

var c=dba.createCluster('MGR1', {adoptFromGr:true});
A new InnoDB cluster will be created based on the existing replication group on instance '172.16.16.10:3306'.

Creating InnoDB cluster 'MGR1' on '172.16.16.10:3306'...
Adding Seed Instance...
Adding Instance '172.16.16.10:3306'...
Adding Instance '172.16.16.11:3306'...
Adding Instance '172.16.16.12:3306'...
...

如果是MGR叢集的metadata發生變化，這時候無論呼叫 dba.getCluster() 還是 dba.createCluster 都可能會報告類似下面的錯誤：

Dba.getCluster: Unable to get an InnoDB cluster handle. The instance '192.168.6.27:3306' may belong to a different cluster from the one registered in the Metadata since the value of 'group_replication_group_name' does not match the one registered in the Metadata: possible split-brain scenario. Please retry while connected to another member of the cluster. (RuntimeError)

這種情況下，可以呼叫 dba.dropMetadataSchema() 函數刪除後設資料，再呼叫 dba.createCluster() 接管叢集：

#確保不影響正常業務的話，刪除無用MGR後設資料
 MySQL  172.16.16.10:3306 ssl  JS > dba.dropMetadataSchema()
Are you sure you want to remove the Metadata? [y/N]: y
Metadata Schema successfully removed.

#接管現有叢集
 MySQL  172.16.16.10:3306 ssl  JS > var c=dba.createCluster('MGR1', {adoptFromGr:true})
...

這樣就可以了接管了。