38탄-5. EKS DB -MySQL InnoDB 2/
<1> MySQL InnoDB Cluster 상세 정보 : MySQL Shell 의 SQL 모드 & 모드 변경
<2> 추가 정보 확인
<3> MySQL 라우터 확인 & 프라이머리 변경
<4> 다수의 MySQL 클라이언트 파드를 통해 MySQL 라우터 시 부하분산 확인
<5> 반복적으로 데이터 INSERT 및 MySQL 서버에 복제 확인 : 세컨더리파드에 INSERT 시도
<1> MySQL InnoDB Cluster 상세 정보 : MySQL Shell 의 SQL 모드 & 모드 변경
# 접속
sql mode로 변경
MIC=mycluster.mysql-cluster.svc.cluster.local
kubectl exec -it -n mysql-operator deploy/mysql-operator -- mysqlsh mysqlx://root@$MIC --password=sakila --sqlx
# 데이터베이스 확인
SQL > show databases;
# PERSIST 설정된 시스템 변수 확인
SQL > SELECT * FROM performance_schema.persisted_variables;
# 현재 싱글 프라이머리 모드 동작 중
mycluster-0 파드가 프라이머리(소스)서버이며, 나머지 파드는 세컨더리(레플리카)서버이다.
SQL > SELECT MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-----------------------------------------------------------------+-------------+
| MEMBER_HOST | MEMBER_ROLE |
+-----------------------------------------------------------------+-------------+
| mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local | SECONDARY |
| mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local | PRIMARY |
| mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local | SECONDARY |
+-----------------------------------------------------------------+-------------+
# 그룹 멤버 상태 확인
SQL > SELECT MEMBER_ID,MEMBER_STATE FROM performance_schema.replication_group_members;
+--------------------------------------+--------------+
| MEMBER_ID | MEMBER_STATE |
+--------------------------------------+--------------+
| e87266a2-db74-11ec-bce8-0235afde6de4 | ONLINE |
| f1af17d8-db74-11ec-bd27-5a10126add15 | ONLINE |
| f299e9c3-db74-11ec-bca0-729b96e7fbcf | ONLINE |
+--------------------------------------+--------------+
# 그룹 복제 Group Replication 에 묶인 그룹 멤버 상태 정보 확인
SQL > SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 719da3f9-d2a8-11ec-af79-ba7e60d88c8d
MEMBER_HOST: mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.1.0
MEMBER_COMMUNICATION_STACK: MySQL
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 7e48b4c1-d2a8-11ec-b050-de005de3704a
MEMBER_HOST: mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.1.0
MEMBER_COMMUNICATION_STACK: MySQL
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 80df97bc-d2a8-11ec-afa2-12ecfd025671
MEMBER_HOST: mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.1.0
MEMBER_COMMUNICATION_STACK: MySQL
# Group Replication 이 관리하는 멤버 목록과 상태 정보(View ID) 확인 : 그룹 멤버 변경 시 변경됨
SQL > SELECT VIEW_ID FROM performance_schema.replication_group_member_stats LIMIT 1;
+---------------------+
| VIEW_ID |
+---------------------+
| 16524381853527486:3 |
+---------------------+
3이 계속 증가한다~~ = 최신 정보 유지
# 그룹 복제에서 추방 시 다시 재가입 동작 여부 확인 : 0 일 경우 다시 가입 시도하지 않는다, 예를 들어 3이라면 그룹 재가입을 3번 시도한다
SQL > SELECT COUNT(*) FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%auto-rejoin%';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
# 스토리지 엔진 : InnoDB 사용!
SQL > SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
<2> 추가 정보 확인
1
# 플러그인 확인
SQL > SHOW PLUGINS;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
...(생략)...
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
| clone | ACTIVE | CLONE | mysql_clone.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
# 그룹 멤버 확인
SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 3896825f-7007-11ee-abea-92ad8ae2be7d | mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | ONLINE | SECONDARY | 8.1.0 | MySQL |
| group_replication_applier | 38b3bcf4-7007-11ee-acb3-b64e04162b41 | mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | ONLINE | PRIMARY | 8.1.0 | MySQL |
| group_replication_applier | 3a440805-7007-11ee-ad59-e2b30a30be9b | mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | ONLINE | SECONDARY | 8.1.0 | MySQL |
+---------------------------+--------------------------------------+-----------------------------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
# 그룹 멤버 포트, 버전 확인
SQL > SELECT MEMBER_HOST,MEMBER_PORT,MEMBER_VERSION FROM performance_schema.replication_group_members;
+-----------------------------------------------------------------+-------------+----------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_VERSION |
+-----------------------------------------------------------------+-------------+----------------+
| mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | 8.1.0 |
| mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | 8.1.0 |
| mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local | 3306 | 8.1.0 |
+-----------------------------------------------------------------+-------------+----------------+
# 복제 테스트
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');
SELECT * FROM t1;
SHOW BINLOG EVENTS;
+------------------+-----+----------------+-----------+-------------+----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------+
| mycluster.000001 | 4 | Format_desc | 1000 | 126 | Server ver: 8.1.0, Binlog ver: 4 |
| mycluster.000001 | 126 | Previous_gtids | 1000 | 157 | |
| mycluster.000001 | 157 | Stop | 1000 | 180 | |
+------------------+-----+----------------+-----------+-------------+----------------------------------+
# Using Group Replication Group Write Consensus : Inspecting a Group's Write Concurrency
SQL > SELECT group_replication_get_write_concurrency();
+-------------------------------------------+
| group_replication_get_write_concurrency() |
+-------------------------------------------+
| 10 |
+-------------------------------------------+
# A Group's Communication Protocol Version
SQL > SELECT group_replication_get_communication_protocol();
+------------------------------------------------+
| group_replication_get_communication_protocol() |
+------------------------------------------------+
| 8.0.27 |
+------------------------------------------------+
# PERSIST 로 설정된 시스템 변수 목록 조회
SQL > SELECT * FROM performance_schema.persisted_variables;
+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_offset | 2 |
| auto_increment_increment | 1 |
| super_read_only | ON |
| binlog_transaction_dependency_tracking | WRITESET |
| group_replication_communication_stack | MYSQL |
| group_replication_start_on_boot | OFF |
| replica_parallel_workers | 4 |
| slave_parallel_workers | 4 |
| group_replication_paxos_single_leader | OFF |
| group_replication_enforce_update_everywhere_checks | OFF |
| group_replication_exit_state_action | ABORT_SERVER |
| group_replication_ssl_mode | REQUIRED |
| group_replication_group_name | 4411d3c6-7007-11ee-a919-b64e04162b41 |
| group_replication_view_change_uuid | 4412077a-7007-11ee-a919-b64e04162b41 |
| group_replication_local_address | mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local:3306 |
| group_replication_single_primary_mode | ON |
| group_replication_recovery_use_ssl | ON |
| group_replication_group_seeds | mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local:3306,mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local:3306 |
| group_replication_recovery_ssl_verify_server_cert | OFF |
+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
2
로그 확인
https://dev.mysql.com/doc/mysql-operator/en/mysql-operator-logs.html
# logs 확인
kubectl logs mycluster-0 -c sidecar -n mysql-cluster
kubectl logs mycluster-0 -c mysql -n mysql-cluster
kubectl logs deploy/mycluster-router -n mysql-cluster
kubectl logs deploy/mysql-operator -n mysql-operator
# kubetail 확인 - MySQL 서버
kubectl krew install tail
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h -c sidecar # Initialization, including initial setup of data (initDB) and ongoing maintenance tasks for a specific instance, such as TLS certification updates
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h -c mysql # The MySQL Server itself
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h -c fixdatadir
# Sets appropriate permissions and ownership of the MySQL data directory, upon initialization.
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h -c initconf # InnoDBCluster's initialization
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h -c initmysql
# Initializes the MySQL Server, including its data directory.
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database --since 2h
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=database
# kubetail 확인 - MySQL 라우터
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=router --since 2h
kubectl tail -n mysql-cluster -l app.kubernetes.io/component=router
# kubetail 확인 - MySQL 오퍼레이터
kubectl tail -n mysql-operator -l name=mysql-operator --since 2h
kubectl tail -n mysql-operator -l name=mysql-operator
<3> MySQL 라우터 확인 & 프라이머리 변경
1
# MySQL 라우터 bash 셸 접속
kubectl exec -it -n mysql-cluster deploy/mycluster-router -- bash
--------------------
# help
mysqlrouter --help
mysqlrouter --version
# 관련 파일 확인
ls -al /tmp/mysqlrouter/
total 36
drwx------ 5 mysqlrouter mysqlrouter 4096 May 24 15:20 .
drwxrwxrwt 1 root root 4096 May 24 15:20 ..
drwx------ 2 mysqlrouter mysqlrouter 4096 May 24 15:20 data
drwx------ 2 mysqlrouter mysqlrouter 4096 May 24 15:20 log
-rw------- 1 mysqlrouter mysqlrouter 1870 May 24 15:20 mysqlrouter.conf
-rw------- 1 mysqlrouter mysqlrouter 87 May 24 15:20 mysqlrouter.key
drwx------ 2 mysqlrouter mysqlrouter 4096 May 24 15:20 run
-rwx------ 1 mysqlrouter mysqlrouter 135 May 24 15:20 start.sh
-rwx------ 1 mysqlrouter mysqlrouter 158 May 24 15:20 stop.sh
cat /tmp/mysqlrouter/mysqlrouter.conf
[DEFAULT]
...
connect_timeout=5
read_timeout=30
dynamic_state=/tmp/mysqlrouter/data/state.json
...
[metadata_cache:bootstrap] # 라우터에 접속할 InnoDB 클러스터의 정보를 구성하고 관리
cluster_type=gr
router_id=1
user=mysqlrouter
metadata_cluster=mycluster
ttl=0.5 # MySQL 라우터가 내부적으로 캐싱하고 있는 클러스터 메타데이터를 갱신하는 주기, 단위(초)
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0 # 해당 옵션 활성화시, 클러스터의 그룹 복제 변경사항을 MySQL 라우터가 알람을 받을 수 있다, 알람 받으면 클러스터 메타데이터를 갱신한다
# 각각 MySQL 기본 프로토콜로 연결되는 '읽기전용포트', 읽기-쓰기포트'와 X프로토콜로 연결되는 읽기전용포트', 읽기-쓰기포트'로 총 4개의 TCP 포트를 사용
# role 이 PRIMART 시 : 기본 round-robin 동작, MySQL 라우터 부트스트랩 설정 시 first-available 설정이 자동 지정, 2가지 중 선택(round-robin,first-available)
# role 이 SECONDARY 시 : 기본 round-robin 동작, MySQL 라우터 부트스트랩 설정 시 round-robin-with-fallback 설정이 자동 지정, 3가지 중 선택(round-robin,first-available,round-robin-with-fallback)
# role 이 PRIMART_AND_SECONDARY 시 : 기본 round-robin 동작, 2가지 중 선택(round-robin,first-available)
[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/?role=PRIMARY # 라우팅 전달 대상이 URL 포맷은 동적이 대상임, role 프라이머리 서버로 연결(읽기-쓰기)
routing_strategy=first-available # 쿼리 요청 전달 전략(4가지): round-robin, round-robin-with-fallback(세컨더리 서버에 RR, 세컨더리 없으면 프라이어머로 RR)
protocol=classic # 쿼리 요청 전달 전략(이어서): first-available(목록 중 사용 가능 첫번째 서버 연결, 연결안되면 그 다음 서버로 시도)
# 쿼리 요청 전달 전략(이어서): next-available(first-available 와 동일하나, 연결 오류 서버는 연결 불가로 표시하고, 연결 대상에서 제외, 단 정적으로 서버 지정시만 가능)
[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://mycluster/?role=SECONDARY # role 는 어떤 타입의 MySQL 서버로 연결할지를 설정, 여기서는 세컨터리 타입 서버로 연결(읽기전용)
routing_strategy=round-robin-with-fallback
protocol=classic # 3306 기존 mysql TCP 통신 방법
[routing:bootstrap_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://mycluster/?role=PRIMARY
routing_strategy=first-available
protocol=x
[routing:bootstrap_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://mycluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
[http_server]
port=8443
ssl=1
ssl_cert=/tmp/mysqlrouter/data/router-cert.pem
ssl_key=/tmp/mysqlrouter/data/router-key.pem
...
exit
--------------------
# mysqlrouter 설정 확인
kubectl exec -it -n mysql-cluster deploy/mycluster-router -- mysqlrouter --help
kubectl exec -it -n mysql-cluster deploy/mycluster-router -- cat /tmp/mysqlrouter/mysqlrouter.conf
...(생략)...
# 메타데이터 캐시 정보 확인
kubectl exec -it -n mysql-cluster deploy/mycluster-router -- cat /tmp/mysqlrouter/data/state.json | jq
{
"metadata-cache": {
"group-replication-id": "4411d3c6-7007-11ee-a919-b64e04162b41",
"cluster-metadata-servers": [
"mysql://mycluster-2.mycluster-instances.mysql-cluster.svc.cluster.local:3306",
"mysql://mycluster-0.mycluster-instances.mysql-cluster.svc.cluster.local:3306",
"mysql://mycluster-1.mycluster-instances.mysql-cluster.svc.cluster.local:3306"
]
},
"version": "1.0.0"
}
# 라우터 계정 정보 확인
kubectl get secret -n mysql-cluster mycluster-router -o jsonpath={.data.routerUsername} | base64 -d;echo
mysqlrouter
kubectl get secret -n mysql-cluster mycluster-router -o jsonpath={.data.routerPassword} | base64 -d;echo
LLYjH-2wUqE-0=vmS-V673B-fWuia
# (옵션) 모니터링
watch -d "kubectl exec -it -n mysql-cluster deploy/mycluster-router -- cat /tmp/mysqlrouter/data/state.json"
<4> 다수의 MySQL 클라이언트 파드를 통해 MySQL 라우터 시 부하분산 확인
https://malwareanalysis.tistory.com/342?category=1099737
1
# mysql 클라이언트 파드 YAML 내용 확인
curl -s https://raw.githubusercontent.com/gasida/DOIK/main/2/myclient-new.yaml -o myclient.yaml
cat myclient.yaml | yh
# myclient 파드 1대 배포 : envsubst 활용
PODNAME=myclient1 envsubst < myclient.yaml | kubectl apply -f -
# myclient 파드 추가로 2대 배포
for ((i=2; i<=3; i++)); do PODNAME=myclient$i envsubst < myclient.yaml | kubectl apply -f - ; done
# myclient 파드들 확인
kubectl get pod -l app=myclient
NAME READY STATUS RESTARTS AGE
myclient1 1/1 Running 0 20s
myclient2 0/1 ContainerCreating 0 10s
myclient3 1/1 Running 0 8s
# 파드1에서 mysql 라우터 서비스로 접속 확인 : TCP 3306
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SHOW DATABASES;"
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME,@@SERVER_ID;"
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME,host from information_schema.processlist WHERE ID=connection_id();"
# 파드1에서 mysql 라우터 서비스로 접속 확인 : TCP 6446
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6446 -e "SELECT @@HOSTNAME,@@SERVER_ID;"
# 파드1에서 mysql 라우터 서비스로 접속 확인 : TCP 6447 >> 3초 간격으로 확인!
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6447 -e "SELECT @@HOSTNAME,@@SERVER_ID;"
3초 간격
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6447 -e "SELECT @@HOSTNAME,@@SERVER_ID;"
# 파드들에서 mysql 라우터 서비스로 접속 확인 : MySQL 라우터정책이 first-available 라서 무조건 멤버 (프라이머리) 첫번쨰로 전달, host 에는 라우터의 IP가 찍힌다.
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "select @@hostname, @@read_only, @@super_read_only";echo; done
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME,host from information_schema.processlist WHERE ID=connection_id();";echo; done
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME;USE employees;SELECT * FROM employees LIMIT $i;";echo; done
# 파드들에서 mysql 라우터 서비스로 접속 확인 : TCP 6447 접속 시 round-robin-with-fallback 정책에 의해서 2대에 라운드 로빈(부하분산) 접속됨
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6447 -e "SELECT @@HOSTNAME,host from information_schema.processlist WHERE ID=connection_id();";echo; done
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6447 -e "SELECT @@HOSTNAME;USE employees;SELECT * FROM employees LIMIT $i;";echo; done
for ((i=1; i<=3; i++)); do kubectl exec -it myclient$i -- mysql -h mycluster.mysql-cluster -uroot -psakila --port=6447 -e "select @@hostname, @@read_only, @@super_read_only";echo; done
<5> 반복적으로 데이터 INSERT 및 MySQL 서버에 복제 확인 : 세컨더리파드에 INSERT 시도
복제가 되는지 확인 해보자
1
# 파드1에서 mysql 라우터 서비스로 접속 확인
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila
--------------------
# 간단한 데이터베이스 생성
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');
SELECT * FROM t1;
exit
--------------------
# 조회
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "USE test;SELECT * FROM t1;"
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
# 추가 후 조회
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "USE test;INSERT INTO t1 VALUES (2, 'Luis2');"
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "USE test;SELECT * FROM t1;"
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | Luis |
| 2 | Luis2 |
# 반복 추가 및 조회
for ((i=3; i<=100; i++)); do kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME;USE test;INSERT INTO t1 VALUES ($i, 'Luis$i');";echo; done
kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "USE test;SELECT * FROM t1;"
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | Luis |
| 2 | Luis2 |
| 3 | Luis3 |
| 4 | Luis4 |
| 5 | Luis5 |
| 6 | Luis6 |
| 7 | Luis7 |
| 8 | Luis8 |
| 9 | Luis9 |
# 모니터링 : 신규 터미널 3개
watch -d "kubectl exec -it myclient1 -- mysql -h mycluster-0.mycluster-instances.mysql-cluster.svc -uroot -psakila -e 'USE test;SELECT * FROM t1 ORDER BY c1 DESC LIMIT 5;'"
watch -d "kubectl exec -it myclient2 -- mysql -h mycluster-1.mycluster-instances.mysql-cluster.svc -uroot -psakila -e 'USE test;SELECT * FROM t1 ORDER BY c1 DESC LIMIT 5;'"
watch -d "kubectl exec -it myclient3 -- mysql -h mycluster-2.mycluster-instances.mysql-cluster.svc -uroot -psakila -e 'USE test;SELECT * FROM t1 ORDER BY c1 DESC LIMIT 5;'"
# 원하는 갯수 만큼 추가, CTRL+C 로 취소
for ((i=101; i<=1000; i++)); do kubectl exec -it myclient1 -- mysql -h mycluster.mysql-cluster -uroot -psakila -e "SELECT @@HOSTNAME;USE test;INSERT INTO t1 VALUES ($i, 'Luis$i');";echo; done
# (참고) 세컨더리 MySQL 서버 파드에 INSERT 가 되지 않는다 : --super-read-only option
kubectl exec -it myclient1 -- mysql -h mycluster-1.mycluster-instances.mysql-cluster.svc -uroot -psakila -e "USE test;INSERT INTO t1 VALUES (1089, 'Luis1089');"
혹은
kubectl exec -it myclient1 -- mysql -h mycluster-2.mycluster-instances.mysql-cluster.svc -uroot -psakila -e "USE test;INSERT INTO t1 VALUES (1089, 'Luis1089');"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
command terminated with exit code 1
다음은
https://brunch.co.kr/@topasvga/3490
주말 CloudNet 스터디 내용 참고하여 정리한 부분입니다.
https://gasidaseo.notion.site/gasidaseo/CloudNet-Blog-c9dfa44a27ff431dafdd2edacc8a1863
감사합니다.
