Oracle MySQL Opreator for Kubernetes & Self-healing(Failover) 

前回「Oracle MySQL Operator for Kubernetes & WordPress」では、InnoDBクラスタ環境を構築して、InnoDBクラスタのScaleIN/OUTやWordPressのPodスケールさせてみました。

今回は、InnoDBクラスタのプライマリに障害が発生した場合にどのような挙動になるかを見ていきたいと思います。

前回の環境に、「mysql-cluster」というNamespaceを作成して、プライマリ1、セカンダリー2のMySQL InnoDBクラスタを構築します。
そして、プライマリを疑似障害として削除した場合に、残りのセカンダリの1つがプライマリに昇格して、停止したプライマリがセカンダリとして自動復旧して、グループレプリケーションでデータがレプリカされることを確認してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61606/29f76817-e741-b7d6-dd26-e57a309b6023.png)

1. mysql-0でデータベースとテーブルを作成、1～3の値をインサート
2. グループレプリケーションでmysql-1とmysql-2にデータレプリカ
3. mysql-0のPodを削除
4. mysql-1がプライマリ昇格、テーブルに4～6の値をインサート
5. mysql-2にグループレプリケーションでデータレプリカ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61606/0851c8e6-c7c2-30f3-84c9-d43d98b34a28.png)

6. mysql-0が復旧して、データレプリケーション（復旧完了）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61606/a51cf02b-97c5-d7fb-e9ae-4bc5ce88c4cc.png)

## Self-healing(Failover) 

### 1.Create InnoDB Cluster

「mysql-cluster」というNamespaceを作成します。

```
kubectl create ns mysql-cluster
```
コマンド結果

```
namespace/mysql-cluster created
```
コンテキストを「mysql-cluster」にセットします。

```
kubectl config set-context --current --namespace=mysql-cluster
```
コマンド結果

```
Context "context-cbsxdpxsfma" modified.
```

Secretを作成します。

```
vim secret.yaml
```
```
apiVersion: v1
kind: Secret
metadata:
  name: mypwds
  namespace: mysql-cluster
stringData:
  rootUser: root
  rootHost: '%'
  rootPassword: root
```

```
kubectl apply -f secret.yaml
```
コマンド結果

```
secret/mypwds created
```

OCIで必要となるクレデンシャル情報をスクリプトにして実行します。
※事前にOCIコンソールで、APIキーを作成してfingerprintとプライベートキーを取得する必要があります。

```
vim oci-sample-secret.sh
```
```
kubectl create secret generic oci-credentials -n mysql-cluster \
        --from-literal=user=<user> \
        --from-literal=fingerprint=<your-fingerprint> \
        --from-literal=tenancy=<your-tenancy> \
        --from-literal=region=<your-resion> \
        --from-literal=passphrase=<your-passphrase> \
        --from-file=privatekey=<your-path>/oci_api_key.pem
```
```
chmod +x oci-sample-secret.sh
```
```
sh oci-sample-secret.sh
```
コマンド結果

```
secret/oci-credentials created
```

「mypwds」と「oci-credentials」Secretができていることを確認します。

```
kubectl get secrets
```
コマンド結果

```
NAME                  TYPE                                  DATA   AGE
default-token-dkmk4   kubernetes.io/service-account-token   3      79s
mypwds                Opaque                                3      28s
oci-credentials       Opaque                                6      12s
```
InnoDBクラスタを作成します。

```
vim mysql-cluster.yaml
```
```
apiVersion: mysql.oracle.com/v2alpha1
kind: InnoDBCluster
metadata:
  name: mysql
  namespace: mysql-cluster
spec:
  secretName: mypwds
  instances: 3
  router:
    instances: 1
```

```
kubectl apply -f mysql-cluster.yaml
```
コマンド結果

```
innodbcluster.mysql.oracle.com/mysql created
```
InnoDBクラスタができていることを確認します。

```
kubectl get innodbcluster mysql --watch
```
```
NAME    STATUS    ONLINE   INSTANCES   ROUTERS   AGE
・
・
・
mysql   ONLINE           3        3           1         6m25s
```
「mysql-0～3」と「mysql-router」のPodが作成されていることを確認します。

```
kubectl get pods
```
コマンド結果

```
NAME                 READY   STATUS    RESTARTS   AGE
mysql-0              2/2     Running   0          24m
mysql-1              2/2     Running   0          22m
mysql-2              2/2     Running   0          20m
mysql-router-xxxxx   1/1     Running   0          22m
```
「mysql」と「mysql-instances」というServiceが作成されていることを確認します。

```
kubectl get services
```
コマンド結果

```
NAME              TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)                               AGE
mysql             ClusterIP   10.96.101.101   <none>        6446/TCP,6448/TCP,6447/TCP,6449/TCP   25m
mysql-instances   ClusterIP   None            <none>        3306/TCP,33060/TCP,33061/TCP          25m
```
persistentvlumeとpersistentvlumeclaimの状況を確認します。

```
kubectl get persistentvolumes,persistentvolumeclaims
```
コマンド結果

```
NAME                                                                                                        CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                           STORAGECLASS   REASON   AGE
persistentvolume/csi-343c6a85-ceac-41ad-9ec3-5a9c66c16be2                                                   50Gi       RWO            Delete           Bound    default/nfs-pvc                 oci-bv                  54m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljr4ub2x5hdai6hrobi2c742b7cogbzttaln2cv7tvvn6efcd4zukga   50Gi       RWO            Delete           Bound    default/datadir-mycluster-2     oci                     58m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljr7zgyejcsd3gg5vbzlzgpr72nkxn2x4qtwax5lj4tskpsnnstnm2q   50Gi       RWO            Delete           Bound    default/datadir-mycluster-0     oci                     63m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljrel777yub2l3x6qvzbjpwg3l43qf7jrxlwwvpfwvnpyd4pa5fxu4q   50Gi       RWO            Delete           Bound    mysql-cluster/datadir-mysql-1   oci                     25m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljreozgjd5kz4zei24w47nee4raeef33e7o56auoqilcificgvugtqq   50Gi       RWO            Delete           Bound    default/datadir-mycluster-1     oci                     60m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljrgweatm5e7vx65yyn5g5r62ljoubdupbzfqaecdvcfvh3kptwqlaa   50Gi       RWO            Delete           Bound    mysql-cluster/datadir-mysql-0   oci                     27m
persistentvolume/ocid1.volume.oc1.ap-osaka-1.abvwsljrkqxhpalptoxbm7tqitfkxsponosncj2b7q72vfm4mnoohvuwnj2a   50Gi       RWO            Delete           Bound    mysql-cluster/datadir-mysql-2   oci                     22m
persistentvolume/wordpress-pv                                                                               50Gi       RWX            Delete           Bound    default/wordpress-pvc           wordpress               52m

NAME                                    STATUS   VOLUME                                                                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
persistentvolumeclaim/datadir-mysql-0   Bound    ocid1.volume.oc1.ap-osaka-1.abvwsljrgweatm5e7vx65yyn5g5r62ljoubdupbzfqaecdvcfvh3kptwqlaa   50Gi       RWO            oci            27m
persistentvolumeclaim/datadir-mysql-1   Bound    ocid1.volume.oc1.ap-osaka-1.abvwsljrel777yub2l3x6qvzbjpwg3l43qf7jrxlwwvpfwvnpyd4pa5fxu4q   50Gi       RWO            oci            25m
persistentvolumeclaim/datadir-mysql-2   Bound    ocid1.volume.oc1.ap-osaka-1.abvwsljrkqxhpalptoxbm7tqitfkxsponosncj2b7q72vfm4mnoohvuwnj2a   50Gi       RWO            oci            23m
```

### 2.InnoDB Cluster Status

InnoDB Clusterのステータスを確認します。

mysql-0に直接アクセスします。

```
kubectl exec -it mysql-0 -- mysqlsh root:root@localhost:3306 -- cluster status
```
```
Defaulting container name to sidecar.
Use 'kubectl describe pod/mysql-0 -n mysql-cluster' to see all of the containers in this pod.
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
WARNING: Using a password on the command line interface can be insecure.
{
    "clusterName": "mysql", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306"
}
```

mysql-0のプライマリーでデータベース、テーブルを作成して、データをインサートします。

mysql-0

```
kubectl exec -it -n mysql-cluster mysql-router-xxxxx -- mysql -uroot -h127.0.0.1 -proot -P6446
```
「test」データベースを作成して、データをインサートします。

```
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1150
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-0    |
+------------+
1 row in set (0.00 sec)

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table test.test (i int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test.test values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql> exit
Bye
```

mysql-1に接続して、グループレプリケーションできていることを確認します。

mysql-1に接続します。

```
kubectl exec -it -n mysql-cluster mysql-router-xxxxx -- mysql -uroot -h127.0.0.1 -proot -P6447
```
```
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-1    |
+------------+
1 row in set (0.00 sec)

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
6 rows in set (0.00 sec)

mysql> select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql> exit
Bye
```

mysql-2に接続して、グループレプリケーションできていることを確認します。MySQL Shellも対応しているので利用してみます。

mysql-2に接続します。

```
kubectl exec -it -n mysql-cluster mysql-2 -- mysqlsh root:root@localhost:3306 --sql
```
```
Defaulting container name to sidecar.
Use 'kubectl describe pod/mysql-2 -n mysql-cluster' to see all of the containers in this pod.
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
MySQL Shell 8.0.26

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 82
Server version: 8.0.26 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test                          |
+-------------------------------+
 MySQL  localhost:3306 ssl  SQL > select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.0005 sec)
 MySQL  localhost:3306 ssl  SQL > \q
Bye!
```

### 3.Pseudo-failure

mysql-0を削除して、挙動を確認します。

```
kubectl get pods
```
コマンド結果

```
NAME                 READY   STATUS    RESTARTS   AGE
mysql-0              2/2     Running   0          34m
mysql-1              2/2     Running   0          32m
mysql-2              2/2     Running   0          29m
mysql-router-xxxxx   1/1     Running   0          32m
```

mysql-0をdeleteします。

```
kubectl delete pod mysql-0
```
コマンド結果

```
pod "mysql-0" deleted
```


### 4.Restoration

Self-healingが開始されます。

```
kubectl get pods
```
コマンド結果

```
NAME                 READY   STATUS     RESTARTS   AGE
mysql-0              0/2     Init:0/2   0          6s
mysql-1              2/2     Running    0          40m
mysql-2              2/2     Running    0          37m
mysql-router-xxxxx   1/1     Running    0          40m
```

mysql-1からクラスタのステータスを確認します。mysql-1がプライマリーになっています。

```
kubectl exec -it -n mysql-cluster mysql-1 -- mysqlsh root:root@localhost:3306 -- cluster status
```
```
Defaulting container name to sidecar.
Use 'kubectl describe pod/mysql-1 -n mysql-cluster' to see all of the containers in this pod.
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
WARNING: Using a password on the command line interface can be insecure.
{
    "clusterName": "mysql", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306"
}
```

テーブルに4,5,6をインサートします。

```
kubectl exec -it -n mysql-cluster mysql-router-xxxxx -- mysql -uroot -h127.0.0.1 -proot -P6446
```
```
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 471
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
```
```
mysql> select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-1    |
+------------+
1 row in set (0.00 sec)

mysql> insert into test.test values (4), (5), (6);
Query OK, 3 rows affected (0.81 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+
6 rows in set (0.00 sec)

mysql> exit
Bye
```

mysql-1からクラスタのステータスを確認します。mysql-0が復旧してセカンダリーになります。復旧するまで何度か実行してみてください。

```
kubectl exec -it -n mysql-cluster mysql-1 -- mysqlsh root:root@localhost:3306 -- cluster status
```
```
Defaulting container name to sidecar.
Use 'kubectl describe pod/mysql-1 -n mysql-cluster' to see all of the containers in this pod.
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
WARNING: Using a password on the command line interface can be insecure.
{
    "clusterName": "mysql", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-0.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306": {
                "address": "mysql-2.mysql-instances.mysql-cluster.svc.cluster.local:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-1.mysql-instances.mysql-cluster.svc.cluster.local:3306"
}
```

mysql-0から4,5,6がテーブルにインサートされた状態でグループレプリケーションされていることを確認します。

```
kubectl exec -it -n mysql-cluster mysql-0 -- mysqlsh root:root@localhost:3306 --sql
```
```
Defaulting container name to sidecar.
Use 'kubectl describe pod/mysql-0 -n mysql-cluster' to see all of the containers in this pod.
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
MySQL Shell 8.0.26-operator

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 50
Server version: 8.0.26 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  SQL > select * from test.test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+
6 rows in set (0.0003 sec)
 MySQL  localhost:3306 ssl  SQL > \q
Bye!
```

プライマリを疑似障害として削除した場合に、残りのセカンダリの1つがプライマリに昇格して、停止したプライマリがセカンダリとして自動復旧して、グループレプリケーションでデータがレプリカされていることも確認できました。