Gasida님의 Database Operator In Kubernetes study(=DOIK) 스터디 진행 중 테스트 한 내용입니다.
- Percona Server for MySQL on Kubernetes 설치
- Percona Server for MySQL on Kubernetes 기본동작 확인
- Scale동작 확인
- 장애상황 확인
- 기타
Percona Distribution for MySQL Operator는 쿠버네티스에서 오픈 소스 MySQL 클러스터의 배포 및 관리를 자동화하는 툴이며 다음 구성 요소가 포함됩니다.
- Percona Server for MySQL
- Percona XtraBackup (MySQL Server기반의 Hotbackup 유틸리티)
- Orchestrator(MySQL의 복제 토폴로지 관리자).
일반 MySQL Server 인스턴스로 각 노드 간에 데이터가 동기화 됩니다.. 3개이상의 노드 사용을 권장하며, Node affinity를 사용하여 일부 노드에 장애가 발생하면 해당 노드가 포함된 파드가 다른 노드에 자동으로 다시 생성됩니다.
Kubernetes는 스테이트 풀 애플리케이션에 데이터 스토리지를 제공하기 위해 PVC(Persistent Volume Claim)를 사용하여 구현합니다. . PVC는 포드에 대한 자동 스토리지 프로비저닝을 구현하는 데 사용됩니다. 오류가 발생하면 컨테이너 스토리지 인터페이스(CSI)가 다른 노드에서 스토리지를 다시 마운트할 수 있어야 합니다. PVC 스토리지 클래스는 이 기능을 지원해야 합니다(쿠버네티스와 OpenShift는 각각 버전 1.9 및 3.9에서 이 기능을 지원합니다).
Helm Chart로 설치했던 Mysql Operator에서는 모든 노드에서 Write가 가능한 Multi Primary이지만, Percona Mysql Operator에서는 Primary Node의 데이터가 Secondary Node로만 sync되는 단방향의 Replication 방식입니다.
이 방식은 Secondary Node에서 SUPER권한이 있을 경우 독립적인 DML을 수행할 수 있지만 다른노드에는 전파가 되지않아 데이터 정합성이 깨지는 상황이 발생하기 때문에 관리자 외에 절때 Super권한을 부여해서는 안됩니다!!(장애 테스트 시나리오에 추가 설명)
1. Percona Server for MySQL on Kubernetes 설치
▶ git복사
$ git clone -b v0.1.0 https://github.com/percona/percona-server-mysql-operator
Cloning into 'percona-server-mysql-operator'...
remote: Enumerating objects: 8818, done.
remote: Counting objects: 100% (1262/1262), done.
remote: Compressing objects: 100% (481/481), done.
remote: Total 8818 (delta 1004), reused 800 (delta 770), pack-reused 7556
Receiving objects: 100% (8818/8818), 8.65 MiB | 11.89 MiB/s, done.
Resolving deltas: 100% (4819/4819), done.
Note: switching to '5e54df71dd008adb00456dd69b7cb0b321caa548'.
You are in 'detached HEAD' state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by switching back to a branch.
If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -c with the switch command. Example:
git switch -c <new-branch-name>
Or undo this operation with:
git switch -
Turn off this advice by setting config variable advice.detachedHead to false
# 확인
$ cd percona-server-mysql-operator
$ ls -lrt
total 180
drwxr-xr-x 3 root root 4096 Jun 22 11:24 api
-rw-r--r-- 1 root root 2599 Jun 22 11:24 README.md
-rw-r--r-- 1 root root 952 Jun 22 11:24 PROJECT
-rw-r--r-- 1 root root 9599 Jun 22 11:24 Makefile
-rw-r--r-- 1 root root 559 Jun 22 11:24 LICENSE-HEADER
-rw-r--r-- 1 root root 11342 Jun 22 11:24 LICENSE
-rw-r--r-- 1 root root 15476 Jun 22 11:24 Jenkinsfile
-rw-r--r-- 1 root root 4610 Jun 22 11:24 CONTRIBUTING.md
-rw-r--r-- 1 root root 354 Jun 22 11:24 code-of-conduct.md
drwxr-xr-x 6 root root 4096 Jun 22 11:24 cmd
drwxr-xr-x 2 root root 4096 Jun 22 11:24 build
drwxr-xr-x 2 root root 4096 Jun 22 11:24 controllers
drwxr-xr-x 9 root root 4096 Jun 22 11:24 config
drwxr-xr-x 11 root root 4096 Jun 22 11:24 pkg
-rw-r--r-- 1 root root 77753 Jun 22 11:24 go.sum
-rw-r--r-- 1 root root 3499 Jun 22 11:24 go.mod
drwxr-xr-x 5 root root 4096 Jun 22 11:24 e2e-tests
drwxr-xr-x 2 root root 4096 Jun 22 11:24 deploy
drwxr-xr-x 11 root root 4096 Jun 22 11:24 vendor
▶ CRD설치
$ kubectl apply -f deploy/crd.yaml
customresourcedefinition.apiextensions.k8s.io/perconaservermysqlbackups.ps.percona.com created
customresourcedefinition.apiextensions.k8s.io/perconaservermysqlrestores.ps.percona.com created
customresourcedefinition.apiextensions.k8s.io/perconaservermysqls.ps.percona.com created
▶ 네임스페이스 생성 및 변경
$ kubectl create namespace mysql
namespace/mysql created
$ kubectl config set-context $(kubectl config current-context) --namespace=mysql
Context "DOIK-Lab" modified.
▶ RBAC(role-based access control) 생성
$ kubectl apply -f deploy/rbac.yaml
serviceaccount/percona-server-mysql-operator created
role.rbac.authorization.k8s.io/percona-server-mysql-operator-leaderelection created
role.rbac.authorization.k8s.io/percona-server-mysql-operator created
rolebinding.rbac.authorization.k8s.io/percona-server-mysql-operator-leaderelection created
rolebinding.rbac.authorization.k8s.io/percona-server-mysql-operator created
▶ Mysql Operator 설치
$ kubectl apply -f deploy/operator.yaml
configmap/percona-server-mysql-operator-config created
deployment.apps/percona-server-mysql-operator created
▶ 패스워드 보호를 위한 secret파일 암호화
$ vi deploy/secrets.yaml
apiVersion: v1
kind: Secret
metadata:
name: cluster1-secrets
type: Opaque
stringData:
root: root_12345
xtrabackup: backup_12345
monitor: monitor_12345
clustercheck: clustercheck_12345
proxyadmin: admin_12345
pmmserver: admin
operator: operator_12345
replication: replication_12345
orchestrator: orchestrator_12345
$ envsubst < deploy/secrets.yaml | kubectl apply -f -
$ kubectl get secret cluster1-secrets
NAME TYPE DATA AGE
cluster1-secrets Opaque 8 2m5s
$ kubectl get secret cluster1-secrets -o json | jq
{
"apiVersion": "v1",
"data": {
"clustercheck": "Y2x1c3RlcmNoZWNrXzEyMzQ1",
"monitor": "bW9uaXRvcl8xMjM0NQ==",
"operator": "b3BlcmF0b3JfMTIzNDU=",
"orchestrator": "b3JjaGVzdHJhdG9yXzEyMzQ1",
"pmmserver": "YWRtaW4=",
"replication": "cmVwbGljYXRpb25fMTIzNDU=",
"root": "cm9vdF8xMjM0NQ==",
"xtrabackup": "YmFja3VwXzEyMzQ1"
},
"kind": "Secret",
"metadata": {
"annotations": {
"kubectl.kubernetes.io/last-applied-configuration": "{\"apiVersion\":\"v1\",\"kind\":\"Secret\",\"metadata\":{\"annotations\":{},\"name\":\"cluster1-secrets\",\"namespace\":\"default\"},\"stringData\":{\"clustercheck\":\"clustercheck_12345\",\"monitor\":\"monitor_12345\",\"operator\":\"operator_12345\",\"orchestrator\":\"orchestrator_12345\",\"pmmserver\":\"admin\",\"replication\":\"replication_12345\",\"root\":\"root_12345\",\"xtrabackup\":\"backup_12345\"},\"type\":\"Opaque\"}\n"
},
"creationTimestamp": "2022-06-22T06:24:25Z",
"name": "cluster1-secrets",
"namespace": "default",
"resourceVersion": "58095",
"uid": "f16bfd54-4247-4303-bc07-5f025efbe7f0"
},
"type": "Opaque"
}
▶ 클러스터 생성 : 5분가랑 소요
$ kubectl apply -f deploy/cr.yaml ## 5분가량 소요
# pod확인
$ kubectl get pod
NAME READY STATUS RESTARTS AGE
cluster1-mysql-0 1/1 Running 0 4m58s
cluster1-mysql-1 1/1 Running 1 (2m59s ago) 3m59s
cluster1-mysql-2 1/1 Running 1 (95s ago) 2m24s
cluster1-orc-0 2/2 Running 0 4m58s
percona-server-mysql-operator-5c9bdc8f6d-r9hhg 1/1 Running 0 9m23s
▶ 헤드리스 서비스 확인
$ kubectl get svc,ep
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/cluster1-mysql ClusterIP None <none> 3306/TCP 10m
service/cluster1-mysql-primary ClusterIP 10.200.1.218 <none> 3306/TCP 10m
service/cluster1-mysql-unready ClusterIP None <none> 3306/TCP 10m
service/cluster1-orc ClusterIP None <none> 3000/TCP,10008/TCP 10m
NAME ENDPOINTS AGE
endpoints/cluster1-mysql 172.16.1.19:3306,172.16.2.19:3306,172.16.3.16:3306 10m
endpoints/cluster1-mysql-primary 172.16.2.19:3306 10m
endpoints/cluster1-mysql-unready 172.16.1.19:3306,172.16.2.19:3306,172.16.3.16:3306 10m
endpoints/cluster1-orc 172.16.1.17:3000,172.16.1.17:10008 10m
▶ 엔드포인트 슬라이스 정보 확인
$ kubectl describe endpointslices
Name: cluster1-mysql-cpwmw
Namespace: mysql
Labels: app.kubernetes.io/component=mysql
app.kubernetes.io/instance=cluster1
app.kubernetes.io/managed-by=percona-server-operator
app.kubernetes.io/name=percona-server
app.kubernetes.io/part-of=percona-server
endpointslice.kubernetes.io/managed-by=endpointslice-controller.k8s.io
kubernetes.io/service-name=cluster1-mysql
service.kubernetes.io/headless=
Annotations: endpoints.kubernetes.io/last-change-trigger-time: 2022-06-24T01:49:36Z
AddressType: IPv4
Ports:
Name Port Protocol
---- ---- --------
mysql 3306 TCP
Endpoints:
- Addresses: 172.16.2.3
Conditions:
Ready: true
Hostname: cluster1-mysql-0
TargetRef: Pod/cluster1-mysql-0
NodeName: k8s-w1
Zone: <unset>
- Addresses: 172.16.3.5
Conditions:
Ready: true
Hostname: cluster1-mysql-1
TargetRef: Pod/cluster1-mysql-1
NodeName: k8s-w2
Zone: <unset>
- Addresses: 172.16.1.6
Conditions:
Ready: true
Hostname: cluster1-mysql-2
TargetRef: Pod/cluster1-mysql-2
NodeName: k8s-w3
Zone: <unset>
Events: <none>
~
~
~
(생략)
$ kubectl get endpointslices
NAME ADDRESSTYPE PORTS ENDPOINTS AGE
cluster1-mysql-hldsr IPv4 3306 172.16.2.19,172.16.3.16,172.16.1.19 11m
cluster1-mysql-primary-m92n4 IPv4 3306 172.16.2.19 11m
cluster1-mysql-unready-wd85n IPv4 3306 172.16.2.19,172.16.3.16,172.16.1.19 11m
cluster1-orc-hz2d8 IPv4 10008,3000 172.16.1.17 11m
▶ nslooup정보 확인
# netshoot 이미지로 netdebug 파드에 zsh 실행
$ kubectl run -it --rm netdebug --image=nicolaka/netshoot --restart=Never -- zsh
> nslookup cluster1-mysql
Server: 10.200.1.10
Address: 10.200.1.10#53
Name: cluster1-mysql.mysql.svc.cluster.local
Address: 172.16.2.19
Name: cluster1-mysql.mysql.svc.cluster.local
Address: 172.16.1.19
Name: cluster1-mysql.mysql.svc.cluster.local
Address: 172.16.3.16
> nslookup -type=srv cluster1-mysql
Server: 10.200.1.10
Address: 10.200.1.10#53
cluster1-mysql.mysql.svc.cluster.local service = 0 33 3306 cluster1-mysql-0.cluster1-mysql.mysql.svc.cluster.local.
cluster1-mysql.mysql.svc.cluster.local service = 0 33 3306 cluster1-mysql-1.cluster1-mysql.mysql.svc.cluster.local.
cluster1-mysql.mysql.svc.cluster.local service = 0 33 3306 cluster1-mysql-2.cluster1-mysql.mysql.svc.cluster.local.
> nslookup cluster1-mysql-0.cluster1-mysql
Server: 10.200.1.10
Address: 10.200.1.10#53
Name: cluster1-mysql-0.cluster1-mysql.mysql.svc.cluster.local
Address: 172.16.2.19
▶ 클라이언트 접속용 파드 생성
cat <<EOF | kubectl create -f -
apiVersion: v1
kind: Pod
metadata:
name: myclient
labels:
app: myclient
spec:
nodeName: k8s-m
containers:
- name: myclient
image: mysql:8.0.29
command: ["tail"]
args: ["-f", "/dev/null"]
terminationGracePeriodSeconds: 0
EOF
pod/myclient created
▶ 헤드리스 서비스로 접속하여 Readonly 옵션 확인
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT @@HOSTNAME, @@READ_ONLY;"
+------------------+-------------+
| @@hostname | @@read_only |
+------------------+-------------+
| cluster1-mysql-0 | 0 |
+------------------+-------------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT @@HOSTNAME, @@READ_ONLY;"
+------------------+-------------+
| @@hostname | @@read_only |
+------------------+-------------+
| cluster1-mysql-1 | 1 |
+------------------+-------------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT @@HOSTNAME, @@READ_ONLY;"
+------------------+-------------+
| @@hostname | @@read_only |
+------------------+-------------+
| cluster1-mysql-2 | 1 |
+------------------+-------------+
2. Percona Server for MySQL on Kubernetes 기본동작 확인
▶ 클러스터 엔드포인트 분산 확인
# Primary Endpoint 접속확인
$ for i in {1..30}; do kubectl exec -it myclient -- mysql -h cluster1-mysql-primary -uroot -proot_12345 -e "SELECT @@HOSTNAME;SELECT @@SERVER_ID;" ; done | sort | uniq -c | sort -nr | grep cluster1
30 | cluster1-mysql-0 |
# Cluster Endpoint 접속확인
$ for i in {1..30}; do kubectl exec -it myclient -- mysql -h cluster1-mysql -uroot -proot_12345 -e "SELECT @@HOSTNAME;SELECT @@SERVER_ID;" ; done | sort | uniq -c | sort -nr | grep cluster1
11 | cluster1-mysql-2 |
11 | cluster1-mysql-0 |
8 | cluster1-mysql-1 |
▶ DML 동기화 확인
# Primary Node에 접속하여 테이블 생성
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.01 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)
# Secondary Node에서 Insert 확인
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
3. Scale동작 확인
▶ Scale 전 확인
$ kubectl get sts
NAME READY AGE
cluster1-mysql 3/3 3h36m
$ kubectl get pod
NAME READY STATUS RESTARTS AGE
cluster1-mysql-0 1/1 Running 0 16m
cluster1-mysql-1 1/1 Running 1 (31m ago) 32m
cluster1-mysql-2 1/1 Running 0 21m
▶ 5개의 POD로 증가 설정
$ kubectl patch ps cluster1 --type='json' -p='[{"op": "replace", "path": "/spec/mysql/size", "value": 5 }]'
▶ 확인
$ kubectl get pod
NAME READY STATUS RESTARTS AGE
cluster1-mysql-0 1/1 Running 0 20m
cluster1-mysql-1 1/1 Running 1 (36m ago) 36m
cluster1-mysql-2 1/1 Running 0 26m
cluster1-mysql-3 1/1 Running 0 9m
cluster1-mysql-4 1/1 Running 0 3m
4. 장애상황 확인
▶[장애1] Primary Pod 장애 발생
→ 모니터링 터미널 생성
# [터미널1] 모니터링 : cluster1-mysql-0 노드 모니터링
$ while true; do kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "SELECT count(*) FROM test.t1; " ; date;sleep 1; done |grep -v insecure
+----------+
| count(*) |
+----------+
| 0 |
+----------+
Fri Jun 24 14:00:52 KST 2022
# [터미널2] 모니터링 : cluster1-mysql-1 노드 모니터링
$ while true; do kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "SELECT count(*) FROM test.t1; " ; date;sleep 1; done |grep -v insecure
+----------+
| count(*) |
+----------+
| 0 |
+----------+
Fri Jun 24 14:00:52 KST 2022
# [터미널3] 모니터링 : cluster1-mysql-2 노드 모니터링
$ while true; do kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "SELECT count(*) FROM test.t1; " ; date;sleep 1; done |grep -v insecure
+----------+
| count(*) |
+----------+
| 0 |
+----------+
Fri Jun 24 14:00:52 KST 2022
→ Primary Node에 데이터 Insert 수행
# [터미널4]
$ for ((i=1; i<=10000; i++)); do kubectl exec -it myclient -- mysql -h cluster1-mysql-primary -uapp_user -papp_12345 -e "INSERT INTO test.t1 VALUES ($i, 'Luis$i'); " ;echo; done |grep -v insecure
→ Primary Pod 장애 발생 : cluster1-mysql-0 파드 강제 삭제
$ kubectl delete pod/cluster1-mysql-0
pod "cluster1-mysql-0" deleted
→ Insert 터미널 수초 순단 후 Insert 정상 수행
ERROR 2003 (HY000): Can't connect to MySQL server on 'cluster1-mysql-primary:3306' (111)
command terminated with exit code 1
ERROR 2003 (HY000): Can't connect to MySQL server on 'cluster1-mysql-primary:3306' (111)
command terminated with exit code 1
ERROR 2003 (HY000): Can't connect to MySQL server on 'cluster1-mysql-primary:3306' (111)
command terminated with exit code 1
ERROR 2003 (HY000): Can't connect to MySQL server on 'cluster1-mysql-primary:3306' (111)
command terminated with exit code 1
→ Primary Node가 cluster1-mysql-1파드로 자동 변경
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT @@HOSTNAME, @@READ_ONLY;"
+------------------+-------------+
| @@HOSTNAME | @@READ_ONLY |
+------------------+-------------+
| cluster1-mysql-2 | 0 |
+------------------+-------------+
▶ [장애2] Secondary Node에 DML이 발생하였을 경우 : 노드간 데이터 정합성 불일치 발생!!
Mysql Replication의 동기화는 단방향으로만 이루어지는 Single Primary방식으로 Primary Node에서만 DML을 수행하여야 합니다. 기본동작 확인에서 Primary에서 insert를 수행할 경우 Secondary Node로 동기화가 잘 되는것을 확인하였습니다. 이번에는 Secondary Node에서 DML을 수행하면 어떻게 되는지 확인하여 보도록 하겠습니다.
→ 테스트 전 데이터 조회
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
→ Secondary Node인 cluster1-mysql-1 POD에 Insert 수행 : 정상 수행 됨(??!!)
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345
mysql> INSERT INTO test.t1 VALUES (2, 'Luis');
Query OK, 1 row affected (0.01 sec)
mysql> commit ;
Query OK, 0 rows affected (0.01 sec)
→ 각 노드에서 데이터 조회 : cluster1-mysql-1 파드에만 Insert되는 데이터 불일치 발생!!
# cluster1-mysql-0 파드 조회
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
# cluster1-mysql-1 파드 조회
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | Luis |
+----+------+
# cluster1-mysql-2 파드 조회
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
→ cluster1-mysql-1 파드 로그 확인 : 정합성관련 에러 발생
$ kubectl logs cluster1-mysql-1
2022-06-24T03:06:12.073279Z 9 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=cluster1-mysql-1-relay-bin' to avoid this problem.
2022-06-24T03:06:12.088373Z 9 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='cluster1-mysql-0.cluster1-mysql.mysql', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2022-06-24T03:06:12.112958Z 10 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
→ cluster1-mysql-1노드에 접속하여 slave status 로그 확인
$ mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: cluster1-mysql-0.cluster1-mysql.mysql
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 2146
Relay_Log_File: cluster1-mysql-1-relay-bin.000004
Relay_Log_Pos: 864
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2146
Relay_Log_Space: 1337
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 40999760
Master_UUID: 18c1bcd6-f35f-11ec-af7a-5a5531fc38a8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 3
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 18c1bcd6-f35f-11ec-af7a-5a5531fc38a8:7-8
Executed_Gtid_Set: 18c1bcd6-f35f-11ec-af7a-5a5531fc38a8:1-8,
7bd16623-f36a-11ec-8d29-fa36a42d3451:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
→ 정합성을 보장하지 못하여 더이상 이 노드에는 Primary Node와 동기화가 되지 않습니다. 장애발생 노드는 전체 초기화 후 DB를 재구성해주어야 하며, 잘못 Insert 된 데이터를 살리려면 업무 담당자들과 같이 일일이 데이터를 검증해야 하는 불상사가 발생합니다. 이렇기 때문에, Master노드가 아닌 노드에서 DML이 가능한 권한인 SUPER권한은 반드시 관리자와 같은 최소한의 유저에게만 부여하여야 합니다.
→ 장애복구 방안 : 장애발생 Node를 삭제 후 재구성을 해주어야 합니다. 데이터까지 같이 재구성을 해주어야 하기 때문에 PVC와 함께 삭제해줘야 하며, 장애발생가 발생했던 Node의 데이터는 Primary Node기준으로 재싱크가 진행됩니다.
$ kubectl delete pvc/datadir-cluster1-mysql-1 pod/cluster1-mysql-1
persistentvolumeclaim "datadir-cluster1-mysql-1" deleted
pod "cluster1-mysql-1" deleted
$ kubectl get pod
NAME READY STATUS RESTARTS AGE
cluster1-mysql-0 1/1 Running 1 (142m ago) 143m
cluster1-mysql-1 1/1 Running 1 (34s ago) 61s
cluster1-mysql-2 1/1 Running 1 (16m ago) 17m
→ 장애 복구 후 데이터 확인 : cluster1-mysql-1파드에 Insert된 데이터는 삭제 됨
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uroot -proot_12345 -e "SELECT * FROM test.t1; "
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
→ 장애방지 대책 : All privileges, Super권한이 없는 APP_USER계정 생성
# App_User계정 생성(DML권한만 부여)
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uroot -proot_12345
mysql> create user app_user@'%' identified by 'app_12345' ;
Query OK, 0 rows affected (0.03 sec)
mysql> grant select, insert, update, delete on test.* to app_user@'%' ;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for app_user@'%' ;
+--------------------------------------------------------------------+
| Grants for app_user@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `app_user`@`%` |
+--------------------------------------------------------------------+
→ 장애방지 대책 : Primary Node에서만 Insert(데이터 변경작업)가 수행되며 Secondary Node에서는 read-only에러 발생
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-0.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "INSERT INTO test.t1 VALUES (3, 'Luis');; "
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-1.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "INSERT INTO test.t1 VALUES (3, 'Luis');; "
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
command terminated with exit code 1
$ kubectl exec -it myclient -- mysql -h cluster1-mysql-2.cluster1-mysql.mysql.svc -uapp_user -papp_12345 -e "INSERT INTO test.t1 VALUES (3, 'Luis');; "
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
command terminated with exit code 1
5. 기타
▶ 파드 볼륨 증설 : 현재까지 테스트 된 바로는 클러스터 전체 삭제 후 재배포를 해야하기 때문에 다운타임이 필요
→ 증설 전 확인
$ kubectl get pod,pvc
NAME READY STATUS RESTARTS AGE
pod/cluster1-mysql-0 1/1 Running 0 26m
pod/cluster1-mysql-1 1/1 Running 1 (42m ago) 43m
pod/cluster1-mysql-2 1/1 Running 0 32m
pod/cluster1-orc-0 2/2 Running 0 3h47m
pod/myclient 1/1 Running 0 3h36m
pod/percona-server-mysql-operator-5c9bdc8f6d-cgkbj 1/1 Running 0 3h48m
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
persistentvolumeclaim/datadir-cluster1-mysql-0 Bound pvc-2ec1ad75-18d1-47dc-8019-14b1719d6d12 2G RWO local-path 3h47m
persistentvolumeclaim/datadir-cluster1-mysql-1 Bound pvc-49b18541-2d23-428d-9b92-4e1ab5c6020b 2G RWO local-path 84m
persistentvolumeclaim/datadir-cluster1-mysql-2 Bound pvc-7bed00ce-2003-42fb-8fcb-fb54c1cb9fa2 2G RWO local-path 100m
persistentvolumeclaim/datadir-cluster1-orc-0 Bound pvc-ae4d9f13-2a94-42ba-8e3d-344da0b7d5fb 1G RWO local-path 3h47m
→ CNPG처럼 아래 명령어로 적용 후 순차 재기동하여 적용하려 하였으나 안됨..
$ kubectl patch PerconaServerMySQL cluster1 --type=merge -p '{"spec":{"mysql":{"volumeSpec":{"persistentVolumeClaim":{"resources":{"requests":{"storage":"10G"}}}}}}}'
→ 현재 yaml파일 백업
$ kubectl get ps cluster1 -o yaml > CR_backup.yaml
→ yaml파일의 size정보 수정
apiVersion: ps.percona.com/v1alpha1
kind: PerconaServerMySQL
metadata:
annotations:
kubectl.kubernetes.io/last-applied-configuration: |
{"apiVersion":"ps.percona.com/v1alpha1","kind":"PerconaServerMySQL","metadata":{"annotations":{},"creationTimestamp":"2022-06-24T11:59:35Z","generation":5,"name":"cluster1","namespace":"mysql","resourceVersion":"13619","uid":"b4791de7-3950-4db5-a19b-369ce929834b"},"spec":{"mysql":{"affinity":{"antiAffinityTopologyKey":"kubernetes.io/hostname"},"expose":{"enabled":false,"type":"ClusterIP"},"image":"percona/percona-server:8.0.25","imagePullPolicy":"Always","resources":{"limits":{"memory":"1G"},"requests":{"memory":"512M"}},"size":3,"sizeSemiSync":0,"volumeSpec":{"persistentVolumeClaim":{"resources":{"requests":{"storage":"6G"}}}}},"orchestrator":{"image":"percona/percona-server-mysql-operator:0.1.0-orchestrator","imagePullPolicy":"Always","resources":{"limits":{"memory":"256M"},"requests":{"memory":"128M"}},"size":1,"volumeSpec":{"persistentVolumeClaim":{"resources":{"requests":{"storage":"1G"}}}}},"pmm":{"enabled":false,"image":"percona/pmm-client:2.25.0","imagePullPolicy":"Always","serverHost":"monitoring-service","serverUser":"admin"},"secretsName":"cluster1-secrets","sslSecretName":"cluster1-ssl"},"status":{"mysql":{"ready":3,"size":3,"state":"ready"},"orchestrator":{"ready":1,"size":1,"state":"ready"}}}
creationTimestamp: "2022-06-24T12:34:20Z"
generation: 2
name: cluster1
namespace: mysql
resourceVersion: "15595"
uid: 06e13226-cd4a-4728-870a-5d32d00e34ca
spec:
mysql:
affinity:
antiAffinityTopologyKey: kubernetes.io/hostname
expose:
enabled: false
type: ClusterIP
image: percona/percona-server:8.0.25
imagePullPolicy: Always
resources:
limits:
memory: 1G
requests:
memory: 512M
size: 3
sizeSemiSync: 0
volumeSpec:
persistentVolumeClaim:
resources:
requests:
storage: 10G
orchestrator:
image: percona/percona-server-mysql-operator:0.1.0-orchestrator
imagePullPolicy: Always
resources:
limits:
memory: 256M
requests:
memory: 128M
size: 1
volumeSpec:
persistentVolumeClaim:
resources:
requests:
storage: 1G
pmm:
enabled: false
image: percona/pmm-client:2.25.0
imagePullPolicy: Always
serverHost: monitoring-service
serverUser: admin
secretsName: cluster1-secrets
sslSecretName: cluster1-ssl
status:
mysql:
ready: 1
size: 3
state: initializing
orchestrator:
ready: 1
size: 1
state: ready
→ 클러스터 삭제 : 서비스 다운타임 발생
$ kubectl delete -f CR_backup.yaml
perconaservermysql.ps.percona.com "cluster1" deleted
→ 수정 된 yaml파일로 클러스터 재기동
$ kubectl apply -f CR_backup.yaml
perconaservermysql.ps.percona.com/cluster1 created
→ PVC0, POD0 재기동하여 적용
$ kubectl delete pvc/datadir-cluster1-mysql-0 pod/cluster1-mysql-0
persistentvolumeclaim "datadir-cluster1-mysql-0" deleted
pod "cluster1-mysql-0" deleted
$ kubectl get pvc
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
datadir-cluster1-mysql-0 Bound pvc-c34c56f0-0b11-475b-98fd-a69f2af9e01e 10G RWO local-path 64s
datadir-cluster1-mysql-1 Bound pvc-7c86aa07-b636-40af-bbc4-71c4ff9c8eb8 2G RWO local-path 9m59s
datadir-cluster1-mysql-2 Bound pvc-c459aa2a-f37a-4f1d-87d3-06c24c03584d 2G RWO local-path 8m22s
datadir-cluster1-orc-0 Bound pvc-b32ce65f-e8f9-49ac-8780-60347623879c 1G RWO local-path 84m
→ PVC1, POD1 재기동하여 적용
$ kubectl delete pvc/datadir-cluster1-mysql-1 pod/cluster1-mysql-1
persistentvolumeclaim "datadir-cluster1-mysql-1" deleted
pod "cluster1-mysql-1" deleted
$ kubectl get pvc
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
datadir-cluster1-mysql-0 Bound pvc-c34c56f0-0b11-475b-98fd-a69f2af9e01e 10G RWO local-path 4m37s
datadir-cluster1-mysql-1 Bound pvc-8a7a1c09-9e43-4d60-bdb7-478aefe47f52 10G RWO local-path 3m3s
datadir-cluster1-mysql-2 Bound pvc-c459aa2a-f37a-4f1d-87d3-06c24c03584d 2G RWO local-path 11m
datadir-cluster1-orc-0 Bound pvc-b32ce65f-e8f9-49ac-8780-60347623879c 1G RWO local-path 87m
→ PVC2, POD2 재기동하여 적용
$ kubectl delete pvc/datadir-cluster1-mysql-2 pod/cluster1-mysql-2
persistentvolumeclaim "datadir-cluster1-mysql-2" deleted
pod "cluster1-mysql-2" deleted
$ kubectl get pvc
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
datadir-cluster1-mysql-0 Bound pvc-c34c56f0-0b11-475b-98fd-a69f2af9e01e 10G RWO local-path 6m3s
datadir-cluster1-mysql-1 Bound pvc-8a7a1c09-9e43-4d60-bdb7-478aefe47f52 10G RWO local-path 4m29s
datadir-cluster1-mysql-2 Bound pvc-0ea7462a-c618-45a6-8c99-8faf2626549a 10G RWO local-path 51s
datadir-cluster1-orc-0 Bound pvc-b32ce65f-e8f9-49ac-8780-60347623879c 1G RWO local-path 89m
'K8S' 카테고리의 다른 글
| Cloud Native PostgreSQL 오퍼레이터 (0) | 2022.06.22 |
|---|---|
| Percona Distribution for MongoDB - 샤딩 (3/3) (0) | 2022.06.19 |
| Percona Distribution for MongoDB 오퍼레이터 - 복제 (2/3) (0) | 2022.06.19 |
| Percona Distribution for MongoDB 오퍼레이터 - 기본설치 (1/3) (0) | 2022.06.19 |
| Helm을 이용해 K8S에 MariaDB Galera 설치하기 (0) | 2022.05.29 |
