안녕하세요. Johncomputer 입니다.

이번에는 아마 최근들어서 더 많은 분들이 이용하고 계신 Aurora MySQL에 대해서 같이 다뤄보려고 합니다.

운영을 하다보면 "자동화", "대규모" 작업을 하게 되는 경우가 있는데

이럴때는 항상 웹 화면에서 하나씩 다루기는 힘들거든요...

그렇다 보니 Lambda 라던가 EC2 아니면 개인 PC에서 CLI로 명령을 할 수 있는 방법이 있는데요

한번 같이 보겠습니다.

디테일한 옵션은 아래에 있고 전 주로 사용하는 스크립트 위주로 올려드릴게요

https://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-clusters.html

1. 기본 사용 명렁어

- 가장 중요한 내용 중 하나인데 DB 개념에서 Cluster, Instance 를 구별해서 볼 수 있어야 합니다.

버전은 Cluster 단위, Type 변경은 Instance 단위로 변경 가능합니다.

#!/bin/bash

# 1. Aurora 클러스터 목록 조회
# 현재 운영 중인 모든 Aurora 클러스터 정보를 확인
aws rds describe-db-clusters

# 2. 특정 클러스터의 상세 정보 조회
# 특정 Aurora 클러스터의 설정 및 상태를 확인
aws rds describe-db-clusters --db-cluster-identifier {my-cluster}

# 3. Aurora 인스턴스 목록 조회
# 현재 운영 중인 모든 데이터베이스 인스턴스를 확인
aws rds describe-db-instances

# 4. 특정 Aurora 인스턴스 상태 조회
# 특정 DB 인스턴스의 상세 정보를 가져옴
aws rds describe-db-instances --db-instance-identifier {my-instance}

# 5. Aurora 클러스터 백업 (스냅샷 생성)
# 클러스터의 백업을 생성하여 데이터 보호
aws rds create-db-cluster-snapshot --db-cluster-snapshot-identifier {my-cluster-snapshot} --db-cluster-identifier {my-cluster}

# 6. 스냅샷 목록 조회
# 생성된 모든 클러스터 스냅샷을 확인
aws rds describe-db-cluster-snapshots

# 7. Aurora 클러스터 복원 (스냅샷에서 복구)
# 기존 백업 스냅샷을 사용하여 새로운 Aurora 클러스터 생성
aws rds restore-db-cluster-from-snapshot --db-cluster-identifier {my-new-cluster} --snapshot-identifier {my-cluster-snapshot}

# 8. Aurora 클러스터 크기 조정 (자동 스케일링 설정 변경)
# Aurora Serverless의 최소 및 최대 용량을 조정
aws rds modify-db-cluster --db-cluster-identifier {my-cluster} --scaling-configuration MinCapacity={2},MaxCapacity={8}

# 9. Aurora 인스턴스 타입 변경 (업그레이드 또는 다운그레이드)
# Aurora 인스턴스의 스펙을 변경 (ex. db.r5.large → db.r6g.large)
aws rds modify-db-instance --db-instance-identifier {my-instance} --db-instance-class {db.r6g.large} --apply-immediately

# 10. Aurora 클러스터 삭제
# 최종 스냅샷 없이 Aurora 클러스터 삭제 (데이터 영구 삭제됨)
aws rds delete-db-cluster --db-cluster-identifier {my-cluster} --skip-final-snapshot

# 11. Aurora 인스턴스 재시작
# 특정 Aurora DB 인스턴스를 재부팅하여 문제 해결
aws rds reboot-db-instance --db-instance-identifier {my-instance}

# 12. Aurora 파라미터 변경
# 특정 파라미터 그룹의 값을 변경하고 적용
aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name {my-cluster-param-group} --parameters "ParameterName={max_connections},ParameterValue={500},ApplyMethod=immediate"

# 13. Aurora MySQL 버전 업그레이드
# 기존 Aurora 클러스터의 엔진 버전을 업그레이드 (사전 테스트 필수!)
aws rds modify-db-cluster --db-cluster-identifier {my-cluster} --engine-version {8.0.mysql_aurora.3.04.0} --apply-immediately

2. describe 심화 - 특정 인스턴스 목록 뽑기 jq json

- 아래처럼 쿼리를 이용해서 원하는 출력값만 얻을 수 있습니다.

- output style 은 text, json, table등 보기 편하게도 가능해요

aws rds describe-db-clusters --region ap-northeast-3 --query "DBClusters[?contains(DBClusterIdentifier, '필터값')]" --output json
aws rds describe-db-clusters --query "DBClusters[?contains(DBClusterIdentifier, '필터값')].{DBClusterIdentifier:DBClusterIdentifier, Endpoint:Endpoint, ReaderEndpoint:ReaderEndpoint}" --output text
aws rds describe-db-clusters --query "DBClusters[?contains(DBClusterIdentifier, '필터값')].{DBClusterIdentifier:DBClusterIdentifier, Endpoint:Endpoint, ReaderEndpoint:ReaderEndpoint}" --output table

3. 기타 사용 명령어 

--region	#실행할 AWS 리전을 지정	--region ap-northeast-2
--output	#출력 형식 지정 (json, table, text, yaml)	--output table
--query	#특정 데이터 필드만 출력	--query "DBClusters[*].DBClusterIdentifier"
--profile	#사용할 AWS 프로필 지정	--profile my-profile
--no-paginate	#페이지 나누기 없이 전체 데이터 출력	--no-paginate

- paginator의 경우 100개가 넘는 리스트를 다룰 때 사용됩니다.

안녕하세요. Johncom 입니다.

information Schema의 테이블에는 테이블 관련 정보들이 정의되어 기록되고 있습니다.

모든 값들은 byte 단위로 기록되고 있어 간단한 변환이 필요합니다.

아래 내용은 "MB" 기준으로 계산 되어 "GB" 계산을 진행하려면 /1024를 추가해야 합니다.

DESC information_schema.TABLES;

< information_shema.TABLES 구조 >

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

TABLE 정보

Query

1. 간단한 DB 사이즈 TOTAL SIZE 조회 ( 데이터 + 인덱스 )

SELECT table_schema "DATABASE",
ROUND(SUM(data_length+index_length)/1024/1024,1) "DATA(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

2. DB 사이즈 중 DATA와 INDEX 나눠서 보기

SELECT
table_schema "DATABASE",
ROUND(SUM(data_length)/(1024/1024),2) as 'DATA(MB)',
ROUND(SUM(index_length)/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY 2 DESC;

3.특정 DB내 테이블 정보 가져오기

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
WHERE table_schema = 'test'
ORDER BY 3 DESC;

4.DB-TABLE 별 COUNT와 DATABASE SIZE 확인하기

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES;

자주 내가 날리는 쿼리

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
ORDER BY data_length DESC;

--  USER 생성

-- 서버 설정 
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 특정 플러그인 지정 (과거사용 5.7 이하)
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

-- 권한

-- 모든 권한 + 모든 DB 접근 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

-- 특정 DB 전체 권한 
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- 특정 테이블 전체 권한
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';

-- 특정 테이블 SELECT 권한
GRANT SELECT ON database_name.table_name TO 'username'@'host';

-- 특정 테이블 SELECT, INSERT 권한
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

-- 특정 프로시저 실행 권한
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'host';


-- 특정 유저 권한 확인
SHOW GRANTS FOR 'username'@'host';

-- 특정 권한 취소
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';
REVOKE SELECT ON mydatabase.mytable FROM 'testuser'@'localhost';


-- 마지막에는 까먹지 말고 꼭!
FLUSH PRIVILEGES;

-- 유저 조회

-- USER와 HOST를 조회
SELECT host, user FROM mysql.user;