테이블 생성후 자동 삭제 발생

안녕하세요, gaview 님.

MySQL 데이터베이스에서 테이블을 생성 이후 테이블이 삭제가 되는 문제가 발생한 것으로 이해했습니다.

데이터베이스에서 테이블이 삭제된 이유로는 사용자의 drop 등의 명령어를 통해서 삭제가 되었거나 다른 이유 때문일 수 있습니다.

유감스럽게도 gaview 님의 DB 생성 및 조작 이력은 제가 확인 할 수 없기에 현재 문제가 발생했던 원인을 파악하기 어렵고, AWS 측에서도 gaview 님의 DB 데이터를 조작하지 않기 때문에 추후 이에 대해서 동일한 문제를 겪으실 시 어떠한 과정을 거쳐 테이블이 없어지게 되었는지 확인 해 보실 필요가 있습니다.

이에 따라 테이블의 변동이력을 추적 할 수 있는 방법 중 하나는 RDS 에서 MySQL binary logging 을 설정하고, binary log 를 다운로드하여 로그 데이터를 확인하는 것입니다.

---

[1] MySQL 의 Binary log 란?

MySQL 서버 인스턴스의 데이터 수정에 대한 정보를 포함하는 로그 파일 세트입니다. 이진 로그에는 테이블 생성 또는 행 수정과 같은 데이터베이스 변경 사항을 설명하는 이벤트를 포함합니다.

.

[2] RDS 에서 MySQL binary logging 설정하기

이진 로깅을 설정하기 위해서는 MySQL 에서 자동 백업 기능을 설정해야 합니다.

1. RDS 에서 MySQL을 사용하는 경우 백업 보존 기간을 0 이 아닌 양수 값으로 설정하세요. [Reference A]
2. RDS 에서 Aurora-MySQL 을 사용하는 경우 파라미터 그룹에서 binlog_format 파라미터를 선택한 이진 로깅 형식(ROW, STATEMENT, 또는 MIXED)으로 설정해야 합니다. 자세한 사항은 다음을 참고하세요. [Reference B]

.

[3] Binary log 확인하기

우선, binary logging 이 잘 설정 되었는지 확인하기 위해서 MySQL 에 클라이언트에 접속하여 다음 명령어의 결과를 확인하세요.

mysql > show variables like "%bin%";

아래와 같이 log_bin 의 값이 ON 이면, 이진 로깅이 설정된 것입니다.

+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 38152                |
| binlog_format                   | MIXED                |
| innodb_locks_unsafe_for_binlog  | OFF                  |
| log_bin                         | ON                   |
| log_bin_trust_function_creators | OFF                  |
| log_bin_trust_routine_creators  | OFF                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 419430400            |
| sql_log_bin                     | ON                   |
| sync_binlog                     | 0                    |
+---------------------------------+----------------------+
10 rows in set (0.00 sec)

다음으로 binary log 파일을 확인할 수 있습니다.

mysql> show binary logs;

RDS 에서 이진 로그 파일을 보존 시간을 변경하고 싶다면 다음 명령어를 사용하여 보존 기간을 1일로 설정할 수 있습니다.

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);

.

[4] Binary log 다운로드 하기

다음은 Amazon RDS 의 이진 로그를 다운로드하는 하나의 예제 입니다.

1. Ubuntu 20.04 서버에 mysqlbinlog 를 사용하기 위한 유틸리티를 설치합니다.

sudo apt-get update
sudo apt install mysql-client-core-8.0
sudo apt install mysql-server-core-8.0

2. 다음의 명령어로 RDS 에 있는 binary log 파일을 다운로드합니다.

mysqlbinlog \
    --read-from-remote-server \ 
    --host={데이터베이스_엔드포인트} \
    --port={데이터베이스_포트}  \
    --user={데이터베이스_User} \
    --password \
    --raw \
    --verbose \
    {이진 로그 파일}

3. 아래 예시는 mysql-bin-changelog.000002 을 다운로드 한 결과입니다.

.

[5] binary log 데이터 확인하기

아래 명령어로 [4] 에서 다운로드 받은 이진 로그 데이터을 확인하고 분석할 수 있습니다.

mysqlbinlog {이진 로그 파일}

다음은 제가 Amazon RDS Aurora-mysql 를 이용한 환경을 구상하여 테스트로 october table 을 삭제한 예시입니다.

mysqlbinlog 를 통해 binlog 파일 내역을 살펴보면 october table 이 drop 을 통해 삭제 되었음을 확인할 수 있습니다.

이와 같은 방법으로 이후에 테이블이 동일하게 삭제가 되는 문제를 겪으실 시, 이에 대한 이력을 확인해 보시기 바랍니다.

해당 문제가 주기적으로 반복되는 것이 아니라 간헐적으로 발생하여 예측할 수 없는 경우 감사 로그 구성을 사용하는 것도 좋습니다. 자세한 사항은 다음을 참고하세요. [Reference C]

추가로 궁금하신 내용이 있을 시 편하게 댓글 남겨주세요 :)

---

[ Reference ]

[A] MySQL 이진 로깅 구성

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQL.BinaryFormat.html

[B] Aurora MySQL 이진 로깅 구성

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.MySQL.BinaryFormat.html

[C] Amazon RDS MySQL 및 Amazon Aurora for MySQL에 대한 데이터베이스 활동에 대한 감사 로그 구성

https://aws.amazon.com/ko/blogs/korea/configuring-an-audit-log-to-capture-database-activities-for-amazon-rds-for-mysql-and-amazon-aurora-with-mysql-compatibility/

[D] Amazon RDS MySQL DB 인스턴스에서 바이너리 로그를 다운로드 하려면 어떻게 해야 하나요?

https://repost.aws/ko/knowledge-center/rds-mysql-download-binary-logs

---