MySQL DB 인스턴스에 대한 공통 DBA 작업
다음 콘텐츠에서는 MySQL 데이터베이스 엔진을 실행하는 DB 인스턴스의 일부 공통 DBA 작업에 대한 Amazon RDS별 구현을 설명합니다. 관리형 서비스 환경을 제공하기 위해 Amazon RDS는 DB 인스턴스에 대해 shell 액세스를 제공하지 않습니다. 또한 고급 권한이 필요한 특정 시스템 절차와 테이블에 대한 액세스를 제한합니다.
Amazon RDS의 MySQL 로그 파일 작업 방법에 대한 자세한 내용은 MySQL 데이터베이스 로그 파일 단원을 참조하십시오.
사전 정의된 사용자 이해
Amazon RDS는 새 RDS for MySQL DB 인스턴스를 사용하여 미리 정의된 여러 사용자를 자동으로 생성합니다. 미리 정의된 사용자 및 권한은 변경할 수 없습니다. 미리 정의된 사용자에 대한 권한은 삭제하거나 이름을 바꾸거나 수정할 수 없습니다. 이를 시도할 시에는 오류가 발생합니다.
-
rdsadmin -
superuser
권한이 있는 관리자가 독립 실행형 MySQL 데이터베이스에서 수행할 수 있는 많은 관리 작업을 처리하기 위해 생성된 사용자입니다. 이 사용자는 RDS for MySQL에서 다양한 관리 작업에 내부적으로 사용됩니다. -
rdsrepladmin – Amazon RDS에서 RDS for MySQL DB 인스턴스 및 클러스터의 복제 작업을 지원하기 위해 내부적으로 사용하는 사용자입니다.
세션 또는 쿼리 종료
rds_kill
및 rds_kill_query
명령을 사용하여 DB 인스턴스에서 사용자 세션이나 쿼리를 종료할 수 있습니다. 먼저 MySQL DB 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 섹션을 참조하세요.
CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)
예를 들어, 스레드 99에서 실행 중인 세션을 종료하려면 다음과 같이 입력합니다.
CALL mysql.rds_kill(99);
스레드 99에서 실행 중인 쿼리를 종료하려면 다음과 같이 입력합니다.
CALL mysql.rds_kill_query(99);
현재 복제 오류 넘어가기
에러가 읽기 전용 복제본의 응답을 중지시키고 데이터 무결성에 영향을 미치지 않는다면 읽기 전용 복사본의 에러를 건너뛸 수 있습니다.
참고
먼저 안전하게 건너뛸 수 있는 오류인지 확인해야 합니다. MySQL 유틸리티에서 읽기 전용 복제본에 연결한 후 다음 MySQL 명령을 실행합니다.
SHOW REPLICA STATUS\G
반환된 값에 대한 자세한 내용은 MySQL 설명서
이전 버전의 MySQL에는 SHOW SLAVE STATUS
대신 SHOW REPLICA STATUS
가 사용되었습니다. 8.0.23 이전 MySQL 버전을 사용하는 경우 SHOW SLAVE STATUS
를 사용합니다.
다음과 같은 방법으로 읽기 복제본에 대한 오류를 건너뛸 수 있습니다.
mysql.rds_skip_repl_error 프로시저 호출
Amazon RDS는 읽기 전용 복제본에서 오류를 건너뛰기 위해 호출할 수 있는 저장 프로시저를 제공합니다. 먼저 읽기 전용 복제본에 연결한 후 다음과 같이 적합한 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 단원을 참조하십시오.
오류를 건너뛰려면 다음 명령을 실행합니다.
CALL mysql.rds_skip_repl_error;
이 명령은 원본 DB 인스턴스에서 실행하거나, 혹은 복제 오류가 발생하지 않은 읽기 전용 복제본에서 실행하는 경우 아무런 효과도 없습니다.
mysql.rds_skip_repl_error
가 지원되는 MySQL 버전 등에 대한 자세한 내용은 mysql.rds_skip_repl_error 단원을 참조하십시오.
중요
mysql.rds_skip_repl_error
을 호출하려고 할 때 ERROR 1305 (42000): PROCEDURE
mysql.rds_skip_repl_error does not exist
와 같은 오류가 발생한 경우에는 MySQL DB 인스턴스를 최신 마이너 버전이나 mysql.rds_skip_repl_error에 등록된 최소 마이너 버전 중 하나로 업그레이드해야 합니다.
slave_skip_errors 파라미터 설정
하나 이상의 오류를 건너뛰려면 읽기 전용 복제본에 slave_skip_errors
정적 파라미터를 설정해야 합니다. 하나 이상의 특정 복제 오류 코드를 건너뛰도록 이 파라미터를 설정할 수 있습니다. 현재는 MySQL 5.7 DB 인스턴스용 RDS에 대해서만 이 파라미터를 설정할 수 있습니다. 파라미터에 대한 설정을 변경하면 DB 인스턴스를 재부팅해야만 새 설정이 적용됩니다. 이러한 파라미터의 설정에 대한 자세한 내용은 MySQL 설명서
이 파라미터를 별도의 DB 파라미터 그룹에 설정하는 것이 좋습니다. 이 DB 파라미터 그룹은 오류를 건너뛰어야 하는 읽기 전용 복제본과만 연결할 수 있습니다. 이 모범 사례를 따르면 다른 DB 인스턴스 및 읽기 전용 복제본에 미치는 잠재적 영향을 줄일 수 있습니다.
중요
이 파라미터에 기본값이 아닌 값을 설정하면 복제 불일치가 발생할 수 있습니다. 문제를 해결하기 위해 다른 옵션을 다 써 버렸고 읽기 전용 복제본의 데이터에 미칠 수 있는 잠재적인 영향을 확신하는 경우에만 이 파라미터를 기본값이 아닌 값으로 설정하세요.
충돌 복구 시간 개선을 위한 InnoDB 테이블스페이스 작업
MySQL의 모든 테이블은 테이블 정의, 데이터 및 인덱스로 구성되어 있습니다. InnoDB는 MySQL 스토리지 엔진으로서 테이블 데이터와 인덱스를 테이블스페이스에 저장하는 역할을 합니다. 이 스토리지 엔진은 전역적 공유 테이블스페이스를 생성하여 데이터 사전을 비롯한 기타 관련 메타데이터, 그리고 테이블 데이터와 인덱스도 저장합니다. 또한 테이블 및 파티션마다 별도의 테이블스페이스를 생성할 수도 있습니다. 이렇게 별도로 생성된 테이블스페이스는 확장자가 .ibd인 파일에 저장되며, 각 테이블스페이스 헤더에는 식별할 수 있도록 고유 번호가 포함됩니다.
Amazon RDS는 MySQL 파라미터 그룹을 통해 innodb_file_per_table
이라고 하는 파라미터를 하나 제공합니다. 이 파라미터는 InnoDB가 파라미터 값을 0으로 설정하여 새 테이블 데이터와 인덱스를 공유 테이블스페이스에 추가할지 또는 파라미터 값을 1로 설정하여 개별 테이블스페이스에 추가할지 제어합니다. Amazon RDS는 innodb_file_per_table
파라미터의 기본값을 1로 설정하여 개별 InnoDB 테이블을 삭제하고 해당 테이블에서 DB 인스턴스에 사용하는 스토리지를 회수할 수 있습니다. 대부분 사용 사례에서 innodb_file_per_table
파라미터는 1로 설정하는 것이 바람직합니다.
하지만 표준 스토리지(마그네틱)나 일반 SSD 스토리지를 사용하여 테이블 수가 1,000개를 넘거나, 혹은 프로비저닝된 IOPS 스토리지를 사용하여 테이블 수가 10,000개를 넘는 등 테이블 수가 많을 때는 innodb_file_per_table
파라미터를 0으로 설정해야 합니다. 이 파라미터를 0으로 설정하면 테이블스페이스가 개별적으로 생성되지 않기 때문에 데이터베이스 충돌 복구에 걸리는 시간을 개선할 수 있습니다.
MySQL은 충돌 복구 주기에서 테이블스페이스가 저장된 메타데이터 파일을 각각 처리합니다. MySQL이 공유 테이블스페이스에 저장된 메타데이터 정보를 처리하는 데 걸리는 시간은 다수의 테이블스페이스로 인해 수천 개의 테이블스페이스 파일을 처리하는 데 걸리는 시간에 비하면 무시해도 될 정도입니다. 테이블스페이스 번호는 각 파일의 헤더에 저장되기 때문에 모든 테이블스페이스 파일을 읽으려면 최대 몇 시간까지 걸릴 수 있습니다. 예를 들어 표준 스토리지에 InnoDB 테이블스페이스가 수백만 개 저장되어 있다면 충돌 복구 주기에서 처리하는 데만 5~8시간이 소요됩니다. 경우에 따라 충돌 복구 주기가 끝나더라도 InnoDB가 추가 정리가 필요하다고 판단할 경우에는 또 다른 충돌 복구 주기가 시작되면서 복구 시간이 연장됩니다. 또 한 가지, 충돌 복구 주기는 테이블스페이스 정보 처리 외에도 롤링백 트랜잭션, 손상된 페이지 복구, 그리고 그 밖의 작업까지 수반한다는 점도 잊어서는 안 됩니다.
innodb_file_per_table
파라미터는 파라미터 그룹에 속하기 때문에 DB 인스턴스에 사용되는 파라미터 그룹만 편집하면 파라미터 값이 변경됩니다. 따라서 DB 인스턴스를 재부팅할 필요가 없습니다. 예를 들어 설정을 1(개별 테이블 생성)에서 0(공유 테이블스페이스 사용)으로 변경하면 새로운 InnoDB 테이블이 공유 테이블스페이스에 추가되는 반면 기존 테이블은 개별 테이블스페이스를 그대로 유지합니다. InnoDB 테이블을 공유 테이블스페이스로 이동하려면 ALTER TABLE
명령을 사용해야 합니다.
여러 테이블스페이스를 공유 테이블스페이스로 마이그레이션
InnoDB 테이블의 메타데이터를 자체 테이블스페이스에서 공유 테이블스페이스로 이동할 수 있습니다. 이렇게 하면 innodb_file_per_table
파라미터 설정에 따라 테이블 메타데이터가 다시 작성됩니다. 먼저 MySQL DB 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 섹션을 참조하세요.
ALTER TABLE
table_name
ENGINE = InnoDB, ALGORITHM=COPY;
예를 들어 다음 쿼리는 공유 테이블스페이스에 없는 모든 InnoDB 테이블에 대해 ALTER TABLE
문을 반환합니다.
MySQL 5.7 DB 인스턴스의 경우:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
MySQL 8.0 DB 인스턴스의 경우:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
MySQL 테이블을 리빌드하여 테이블의 메타데이터를 공유 테이블스페이스로 이동하려면 테이블을 리빌드할 수 있는 스토리지 공간이 임시로 필요합니다. DB 인스턴스에 여유 스토리지 공간이 있어야 하는 이유도 바로 이 때문입니다. 리빌드 단계에서는 테이블이 잠겨서 쿼리에 액세스하지 못합니다. 작은 용량의 테이블이나 자주 액세스하지 않는 테이블의 경우 이것이 문제가 되지는 않습니다. 하지만 대용량의 테이블이나 동시 접속자 수가 많은 환경에서 자주 액세스하는 테이블이라면 테이블을 읽기 전용 복제본에 다시 빌드할 수 있습니다.
읽기 전용 복제본을 생성한 후 테이블 메타데이터를 읽기 전용 복제본의 공유 테이블스페이스로 마이그레이션할 수 있습니다. ALTER TABLE 문이 읽기 전용 복제본에 대한 액세스를 차단하더라도 원본 DB 인스턴스는 영향을 받지 않습니다. 따라서 테이블 리빌딩 프로세스 중 읽기 전용 복제본이 지연되더라도 원본 DB 인스턴스는 계속해서 이진 로그를 생성합니다. 리빌딩 프로세스에는 스토리지 공간이 추가로 필요할 뿐만 아니라 재생 로그 파일이 커질 수도 있기 때문에 원본 DB 인스턴스보다 큰 용량의 스토리지를 할당하여 읽기 전용 복제본을 생성해야 합니다.
읽기 전용 복제본을 생성하여 InnoDB 테이블을 다시 빌드한 후 공유 테이블스페이스를 사용하려면 다음 단계를 따라야 합니다.
-
이진 로깅을 계속 할 수 있도록 원본 DB 인스턴스에 백업 보존 기간이 활성화되어 있는지 확인합니다.
-
AWS Management Console 또는 AWS CLI를 사용하여 원본 DB 인스턴스의 읽기 전용 복제본을 생성합니다. 읽기 전용 복제본을 생성하려면 충돌 복구와 같이 다수의 동일한 프로세스를 거쳐야 하기 때문에 InnoDB 테이블스페이스가 많을 경우에는 생성 프로세스에 다소 시간이 걸릴 수 있습니다. 이때 읽기 전용 복제본에 할당하는 스토리지 공간은 현재 원본 DB 인스턴스에 사용 중인 스토리지 공간보다 많아야 합니다.
-
읽기 전용 복제본이 생성되면 파라미터 설정
read_only = 0
및innodb_file_per_table = 0
을 사용하여 파라미터 그룹을 생성합니다. 그런 다음 파라미터 그룹을 읽기 전용 복제본과 연결합니다. -
복제본에서 마이그레이션할 모든 테이블에 대해 다음 SQL 문을 실행합니다.
ALTER TABLE
name
ENGINE = InnoDB -
읽기 전용 복제본에서
ALTER TABLE
문을 모두 완료한 후에는 읽기 전용 복제본이 소스 DB 인스턴스에 연결되어 있고 두 인스턴스가 동기화되어 있는지 확인합니다. -
콘솔 또는 CLI를 사용하여 읽기 전용 복제본을 인스턴스로 승격합니다. 새로운 독립형 DB 인스턴스에 사용한 파라미터 그룹에서
innodb_file_per_table
파라미터가 0으로 설정되어 있는지 확인합니다. 새로운 독립형 DB 인스턴스의 이름을 변경하고 애플리케이션을 새로운 독립형 DB 인스턴스로 지정합니다.
전역적 상태 이력 관리
작은 정보
데이터베이스 성능을 분석하고자 Amazon RDS의 성능 개선 도우미를 사용할 수도 있습니다. 자세한 내용은 성능 개선 도우미를 통한 Amazon RDS 모니터링 단원을 참조하십시오.
MySQL은 작업 관련 정보를 알 수 있는 다수의 상태 변수를 유지하고 있습니다. 이 변수 값은 DB 인스턴스에서 잠금 또는 메모리 문제를 파악하는 데 효과적입니다. DB 인스턴스를 마지막으로 시작한 때부터 계속해서 누적되기 때문입니다. 대부분 상태 변수는 FLUSH STATUS
명령을 사용해 0으로 재설정할 수 있습니다.
Amazon RDS는 시간이 지나면서 이 변수 값의 스냅샷을 캡처하거나, 마지막 스냅샷 이후 모든 변경 사항과 함께 변수 값을 테이블에 기록하는 등 시간 경과에 따른 상태 변수 값을 모니터링할 수 있는 프로시저를 지원합니다. 이러한 인프라를 전역적 상태 이력(GoSH)이라고 부릅니다. GoSH는 버전 5.5.23부터 모든 MySQL DB 인스턴스에 설치되기 시작했지만 기본적으로 비활성화되어 있습니다.
GoSH를 활성화하려면 먼저 파라미터 event_scheduler
를 ON
으로 설정하여 DB 파라미터 그룹의 이벤트 스케줄러를 활성화해야 합니다. 또한 MySQL 5.7을 실행하는 MySQL DB 인스턴스의 경우 show_compatibility_56
파라미터를 1
에 설정해야 합니다. DB 파라미터 그룹의 생성 및 변경에 대한 자세한 내용은 파라미터 그룹 작업 단원을 참조하십시오. 이 파라미터를 활성화할 때 생기는 부작용에 관한 내용은 MySQL 5.7 참조 설명서에 나온 show_compatibility_56
그런 다음 아래 표의 프로시저에 따라 GoSH를 활성화 및 구성할 수 있습니다. 먼저 MySQL DB 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 섹션을 참조하세요. 각 절차에 대해 다음을 입력합니다.
CALL
procedure-name
;
여기서 procedure-name에는 표에 보이는 프로시저 중 하나가 들어갑니다.
프로시저 |
설명 |
---|---|
|
|
|
스냅샷 캡처 주기(분)를 지정합니다. 기본 값은 5입니다. |
|
스냅샷을 비활성화합니다. |
|
필요할 경우에만 스냅샷을 캡처합니다. |
|
|
|
테이블 로테이션 주기(일)를 지정합니다. 기본 값은 7입니다. |
|
테이블 로테이션을 비활성화합니다. |
|
필요에 따라 |
GoSH가 활성화되어 있을 때는 쓰기가 가능한 테이블에 쿼리를 요청할 수 있습니다. 예를 들어 Innodb 버퍼 풀의 적중률에 대한 쿼리를 요청하려면 다음과 같이 쿼리를 실행합니다.
select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'