MySQL 데이터 변경 내역 추적 방법
직접 관리하는 서비스를 통해서 데이터에 액세스하는 경우라면 서비스 로그를 통해서 데이터 변경 내역을 확인할 수 있지만 외부로부터 DB에 직접 액세스해서 작업을 처리할 수 있는 경우라면 그게 불가능합니다. 이런 경우에도 데이터 변동 내역을 파악하고 있어야 하는 경우라면 어떤 방법을 쓸 수 있을지 찾아봤습니다. (MySQL 기준)
바이너리 로그(Binary Logs): 데이터베이스에서 발생한 모든 변경(INSERT, UPDATE, DELETE 등)을 기록합니다. 이 로그를 활성화하고 주기적으로 확인하여 데이터베이스의 변경 사항을 추적할 수 있습니다. 참고자료 링크
트리거(Trigger): 특정 테이블에 대한 변경 사항을 추적하려면 트리거를 사용할 수 있습니다. 예를 들어, 데이터가 INSERT, UPDATE, DELETE 될 때마다 이를 기록하는 별도의 이력 테이블에 기록을 남길 수 있습니다. 참고자료 링크
감사 로깅(Audit Logging): MySQL 엔터프라이즈 에디션에 포함된 기능으로 데이터베이스에 대한 모든 액세스와 변경 내역을 기록할 수 있습니다. 오픈 소스 버전에서는 제삼자의 감사 플러그인(MariaDB, audit plugin)을 대신 이용할 수 있습니다. 참고자료 링크
데이터베이스 변경 알림: 일부 애플리케이션은 데이터베이스 변경 사항을 실시간으로 감지하고 알림을 제공하는 기능을 내장하고 있습니다. 이를 통해 데이터베이스의 변경 사항을 즉시 알 수 있습니다. 예를 들어 CDC (change data capture) 같은 키워드로 검색을 해 보면 이런 용도의 애플리케이션을 찾아볼 수 있는데, Maxwell's daemon이라는 애플리케이션(뭔가 물리학을 좋아하는 사람이 만들었을 것 같은 이름이네요)을 쓰면 데이터가 변경될 때마다 카프카로 JSON을 보낸다든가 할 수 있습니다.
정기적인 데이터베이스 스냅샷: 데이터베이스의 전체 또는 중요 부분의 스냅샷을 정기적으로 취해 변경 사항을 추적할 수 있습니다. 이 방법은 매우 큰 데이터베이스에서는 실용적이지 않을 수 있습니다.
질의 로그(Query Log): MySQL은 실행된 모든 질의를 기록하는 질의 로그 기능을 제공합니다. 이 로그를 통해 데이터베이스에서 실행된 모든 SQL 명령을 볼 수 있습니다.
Query Log
질의 로그는 데이터베이스 서버에서 실행되는 모든 SQL 질의를 기록하는 기능입니다. 이 로그는 데이터베이스에 대한 진단 정보, 성능 분석, 감사, 문제 해결 등에 활용할 수 있습니다.
특징 및 고려사항
기록 내용: 질의 로그에는 SQL 명령문(INSERT, UPDATE, DELETE, SELECT 등)과 함께 해당 명령을 실행한 시간, 클라이언트 정보 등이 기록됩니다.
로그 파일: 기본적으로, 로그 파일은 MySQL 서버의 데이터 디렉토리에 위치합니다. 파일 이름은 보통 'hostname.log' 형식을 따릅니다.
성능 영향: 질의 로그는 서버 성능에 영향을 줄 수 있습니다. 모든 질의를 기록하기 때문에, 특히 높은 트랜잭션이 발생하는 시스템에서는 디스크 I/O가 증가하고 성능 저하가 발생할 수 있습니다.
보안 고려사항: 질의 로그는 민감한 정보를 포함할 수 있으므로 보안에 주의해야 합니다. 로그 파일에 대한 액세스 권한을 적절히 관리하는 것이 중요합니다.
사용 방법
설정 파일(my.cnf 또는 my.ini)에서 general_log, general_log_file 옵션을 써서 활성화하고 파일 경로를 설정할 수 있습니다.
general_log = 1
general_log_file = /path/to/your/logfile.log
아니면 다음과 같이 SQL 명령어를 실행시켜서 활성화할 수도 있습니다.
SET GLOBAL general_log_file = '/path/to/your/logfile.log';
SET GLOBAL general_log = 'ON';
# 다음과 같은 명령으로 중단시킬 수 있습니다.
SET GLOBAL general_log = 'OFF';
대신 서버를 재시작하면 초기화되기 때문에 영구적으로 설정을 바꾸고 싶다면 my.cnf 또는 my.ini 파일에서 설정을 바꿔야 합니다.
