배경 및 목표
운영 환경에서 DELETE 쿼리 실행 시 조건문을 누락하여 데이터가 삭제 되는 사고가 발생하였다. 이와 같은 실수를 방지하고 데이터베이스의 안정성을 확보하기 위해 DELETE 쿼리 제한, 안전 모드 설정, 로그 분석 및 알림 체계를 구축한다.
DELETE 실행 권한 제한
1.
사용자 생성 및 초기 권한 부여
새로운 사용자(dev1)를 생성하고, coupon 데이터베이스에 대해 모든 권한 부여한다.
CREATE USER 'dev1'@'%' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON coupon.* TO 'dev1'@'%';
FLUSH PRIVILEGES;
-- 확인
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'dev1'@'%';
SQL
복사
•
%는 모든 IP에서 접속 허용을 의미한다. IP를 특정하려면 'dev1'@'192.168.1.%'처럼 설정한다.
2.
DELETE 권한을 제한
dev1 계정에서 coupon 데이터베이스 내 모든 테이블에서 DELETE 권한을 철회하여 제한한다.
REVOKE DELETE ON coupon.* FROM 'dev1'@'%';
-- 권한 확인
SHOW GRANTS FOR 'dev1'@'%';
SQL
복사
3.
DELETE 제한 확인
dev1 계정에서 DELETE 쿼리 실행 시 제한이 적용되는지 검증한다. DELETE 쿼리문 실행 시 Access denied 오류가 발생한다. 이는 dev1 계정에서 DELETE 권한이 없기 때문에 발생한 것이다. 이로써 특정 사용자가 데이터 삭제를 실행하지 못하도록 철저히 제한할 수 있다.
mysql -u dev1 -p
mysql> use coupon;
mysql> SELECT * from coupons;
mysql> INSERT INTO coupons (`title`, `coupon_type`, `total_quantity`, `issued_quantity`, `discount_amount`, `min_available_amount`, `date_issue_start`, `date_issue_end`)
VALUES ('test', 'test', 0, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK))
mysql> DELETE FROM coupons WHERE title='test';
SQL
복사
sql_safe_update 활성화
DELETE 권한으로 제한하면 가장 그렇지 못한 상황이라면 sql_safe_update를 활성화해보자. sql_safe_updates는 MySQL에서 안전한 쿼리 실행을 보장하기 위해 사용하는 설정으로, 실수로 인한 데이터 변경 또는 삭제를 방지한다. 특히 아래 두 가지를 제한한다.
•
조건이 없는 UPDATE, DELETE 실행
•
인덱스를 사용하지 않는 쿼리 실행
1.
sql_safe_updates 설정
1.1 임시 설정:sql_safe_updates를 활성화하려면 MySQL 터미널에서 아래 명령을 실행한다.
SET sql_safe_updates = 1;
SQL
복사
1.2 서버 전체 적용:서버를 재시작마다 적용하려면 MySQL 설정파일 (my.cnf)에 추가한다.
•
my.cnf
[mysqld]
sql_safe_updates = 1
SQL
복사
1.3 sql_safe_updates 설정이 활성화되었는지 확인한다.
-- 활설화 확인
SELECT @@sql_safe_updates;
SQL
복사
2.
sql_safe_updates 동작 확인
조건문 없이 DELETE 실행 시 제한
DELETE FROM coupons;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
SQL
복사
안전한 쿼리 작성
sql_safe_updates는 데이터 베이스 작업의 안전성을 높이는 유용한 도구로, 실수를 방지하고 쿼리 성능을 향상시킨다. 운영 환경에서는 기본적으로 활성화하고, 예외적으로 필요할 때만 비활성화하여 사용하는 것을 권장한다. sql_safe_updates 활성화 상태에서는 쿼리 실행 시 아래 조건을 만족해야 한다.
•
WHERE 조건 필수
•
인덱스 활용 필수
◦
인덱스가 없는 열을 기준으로 WHERE 절을 사용할 경우 실행되지 않을 수 있다.
◦
대상 열에 인덱스를 추가하거나 인덱스가 있는 열을 조건으로 사용해야 한다.
•
LIMIT 추가 권장
◦
많은 데이터를 수정하거나 삭제할 경우 실행을 제한하기 위해 LIMIT를 추가한다.
MySQL General Log 설정 및 활용
MySQL의 General Log는 서버에서 실행되는 모든 SQL 문과 명령어를 기록하는 로그기능으로, 문제를 디버깅하거나 실행 내역을 분석할 때 유용하다. 로그 출력 형식으로 파일(FILE), 테이블(TABLE) 두 가지가 있다. 출력 형식의 특성을 비교하여 운영 환경에 맞게 설정하면 데이터베이스의 보안과 성능을 효과적으로 관리할 수 있다.
•
파일(FILE): 로그가 파일 형태로 저장. 성능이 우선되며 외부 분석 도구에 적합하다.
•
테이블(TABLE): 로그가 테이블에 저장. SQL 쿼리를 통해 실시간 분석 가능하다.
기준 | 파일(FILE) | 테이블(TABLE) |
성능 | 파일에 기록하는 방식은 빠름. | 테이블에 기록할 때 약간의 오버헤드 발생. |
읽기/분석 용이성 | 텍스트 분석 도구로 직접 읽고 처리 가능. | SQL 쿼리를 통해 쉽게 필터링 및 집계 가능. |
데이터 관리 | 파일 크기 증가 시 로그 회전 필요. | 테이블 크기 관리 및 정리가 필요. |
영구성 | 서버 재시작 시 로그 데이터 초기화 가능. | 데이터베이스 테이블에 저장되므로 유지됨. |
보안 | OS 파일 권한 필요, 외부 접근 차단 가능. | 데이터베이스 접근 권한 필요. |
대규모 환경 적합성 | 대규모 시스템에서 부하가 적음. | 대규모 환경에서는 성능 저하 가능. |
General Log 설정
1.
로그를 저장 할 디렉토리와 파일을 생성한다.
$ mkdir /var/log/mysql
$ touch /var/log/mysql/general.log
$ chown mysql:mysql /var/log/mysql/general.log
$ chmod 660 /var/log/mysql/general.log
Bash
복사
2.
MySQL 설정 파일(my.cnf)을 수정한다.
•
my.cnf
[mysqld]
general_log = ON
general_log_file = /var/log/mysql/general.log
log_output = 'FILE' or 'TABLE' -- 출력 형식을 FILE 또는 TABLE 지정한다.
SQL
복사
3.
General Log 설정이 활성화되었는지 확인한다.
SHOW VARIABLES LIKE 'general_log%';
SQL
복사
4.
설정을 반영하기 위해 MySQL 서버를 재시작한다.
systemctl restart mysql
# 또는 도커를 사용하는 경우
docker-compose restart coupon-mysql
Bash
복사
4.
로그 파일의 내용을 확인한다.
# 파일(FILE)
cat /var/log/mysql/general.log
Bash
복사
-- 테이블(TABLE)
SELECT * FROM mysql.general_log LIMIT 10;
SQL
복사
General Log 활용
1. IP 별 쿼리 추적
특정 IP에서 실행된 쿼리를 필터링한다.
SELECT
event_time,
user_host,
command_type,
argument
FROM
mysql.general_log
WHERE
command_type = 'Query'
AND (argument LIKE 'SELECT%' COLLATE utf8mb4_general_ci
OR argument LIKE 'INSERT%' COLLATE utf8mb4_general_ci
OR argument LIKE 'UPDATE%' COLLATE utf8mb4_general_ci
OR argument LIKE 'DELETE%' COLLATE utf8mb4_general_ci)
AND user_host LIKE '%172.18.0.8%' -- 특정 IP 필터링
ORDER BY
event_time;
Bash
복사
2. WHERE 조건 누락된 DELETE 쿼리 확인
조건 없는 DELETE 쿼리를 탐지한다.
SELECT
event_time,
user_host,
argument AS query
FROM
mysql.general_log
WHERE
command_type = 'Query'
AND argument LIKE 'DELETE%' COLLATE utf8mb4_general_ci;
AND argument NOT LIKE '%WHERE%' COLLATE utf8mb4_general_ci;
SQL
복사
General Log 데이터를 활용하여 일별, 주별, 월별 쿼리 사용량을 추적하여 패턴을 분석할 수 있다. 더불어 Grafana와 같은 시각화 도구에 연결하여 실시간 모니터링 및 알림 설정이 가능하다.
Grafana 와 Slack 알림 설정
Grafana에서 특정 이벤트(예: WHERE 조건 없는 DELETE 쿼리 실행)를 감지하고 Slack으로 알림을 발송하도록 설정하는 과정이다.
설정 과정
Slack WebHook URL 생성
Grafana Contack Point 설정
1.
Grafane 메뉴에서 Alerting > Contact points 로 이동한다. Create contact point 버튼 클릭을 틀릭한다.
2.
Slack WebHook URL을 입력하여 설정한다.
Grafana Alert Rule 생성
1.
Grafana 메뉴에서 Alerting > Alert rules 로 이동한다. New alert rule 버튼 클릭하여 새로운 Alert Rule을 생성한다.
2.
Alert Rule 이름을 설정한다. (예: mysql_delete_without_where)
3.
Query 및 조건을 정의한다. PromQL 또는 SQL 쿼리를 입력한다.
SELECT COUNT(*) AS value
FROM mysql.general_log
WHERE command_type = 'Query'
AND argument LIKE 'DELETE%' COLLATE utf8mb4_general_ci
AND argument NOT LIKE '%WHERE%' COLLATE utf8mb4_general_ci
AND event_time > NOW() - INTERVAL 5 MINUTE;
SQL
복사
항목 | 설명 |
SELECT COUNT(*) | COUNT(*)를 사용하여 쿼리 조건을 만족하는 총 행의 수를 반환 |
command_type = 'Query' | Query 유형의 명령어만 선택 |
argument LIKE 'DELETE%' | DELETE로 시작하는 쿼리만 선택합니다. |
COLLATE utf8mb4_general_ci | 대소문자를 구분하지 않는 비교를 수행 |
argument NOT LIKE '%WHERE%' | WHERE 조건이 없는 DELETE 쿼리만 선택 |
event_time > NOW() - INTERVAL 5 MINUTE | 최근 5분 이내에 실행된 쿼리만 선택 |
주의 사항 
•
시간 설정: 쿼리 실행 감지와 알림 발송 간 시간 조정을 상황에 맞게 설정해야 한다.
•
쿼리 조건: DELETE 쿼리와 관련된 대소문자, 조건 유무를 명확히 감지할 수 있도록 쿼리를 작성해야 한다.
•
Threshold : 트리거를 정확히 설정하여 불필요한 알림이 발생하지 않도록 조정한다.
4.
Pending Period를 1m으로 설정하여 1분 동안 조건이 충족되면 알림이 발송되도록 설정한다.
5.
사전에 설정한 Contact Point와 연결하여 알림이 Slack으로 전송되도록 구성한다.
테스트
1.
아래의 쿼리를 실행하여 테스트 데이터를 생성한다.
USE coupon;
INSERT INTO coupons (`title`, `coupon_type`, `total_quantity`, `issued_quantity`, `discount_amount`, `min_available_amount`, `date_issue_start`, `date_issue_end`)
VALUES
('5만원 이상 구매 시 5천원 할인', 'FIRST_COME_FIRST_SERVED', 30000, 0, 5000, 50000, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK)),
('3만원 이상 구매 시 3천원 할인', 'FIRST_COME_FIRST_SERVED', 30000, 0, 3000, 30000, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK)),
('2만원 이상 구매 시 2천원 할인', 'FIRST_COME_FIRST_SERVED', 30000, 0, 2000, 20000, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK)),
('1만원 이상 구매 시 1천원 할인', 'FIRST_COME_FIRST_SERVED', 30000, 0, 1000, 10000, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK)),
('test', 'test', 0, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 1 WEEK), DATE_ADD(NOW(), INTERVAL 1 WEEK));
select * from coupons;
SQL
복사
2.
조건 유무와 대소문자를 구분하여 DELETE 쿼리를 실행한다.
-- 조건 포함(정상실행)
DELETE from coupons where title='test';
delete from coupons where title='test';
-- 조건 미포함(알림발생)
DELETE FROM coupons;
delete from coupons;
SQL
복사
결과 확인
•
Grafana Alerting
•
Slack 알림
Grafana와 Slack을 연동하여 특정 쿼리 조건에 대해 실시간 알림 설정하는 방법을 확인하였다. 이 설정은 운영 환경에서 중요한 쿼리 실행 감지와 알림 관리를 효과적으로 지원한다.
마무리
이번 작업을 통해 MySQL 데이터 삭제 사고를 방지 하기 위한 다양한 접근법을 구현하고 검증하였다.
•
DELETE 권한제한을 통해 특정 사용자가 데이터 삭제를 실행하지 못하도록 하였으며,
•
sql_safe_updaes 활성화를 통해 실수로 인한 조건 없는 DELETE와 UPDATE를 방지하였다.
•
또한 MySQL General Log를 활용하여 쿼리 로그를 분석하고, Grafane 및 Slack 알림 설정을 통해 실시간으로 조건 없는 DELETE 쿼리를 감지하고 알림을 발송할 수 있도록 구성하였다.
이 과정을 통해 데이터베이스의 안정성과 보안을 강화하고, 실시간 모니터링 체계를 구축하여 운영 환경에서 발생할 수 있는 실수를 효과적으로 방지할 수 있었다. 조사를 하다보니 SQL Exporter, Python이나 Go를 활용한 custom Exporter를 구현하여 좀 더 정교한 모니터링 체계를 설계하는 것에 관심이 생겼다.
Q&A
my.cnf 파일 위치 확인
Q. DELETE 실행 권한 제한과 sql_safe_updates는 어떤 차이가 있나요?
Q. Grafana에서 알림 설정 시 가장 고려해야 할 요소는 무엇인가요?
Q. General Log를 파일(FILE)과 테이블(TABLE)로 설정할 때 어떤 기준으로 선택해야 하나요?
Q. sql_safe_updates를 비활성화해야 하는 상황은 언제인가요?
Q. 로그 데이터 활용 방안은 무엇인가요?
Related Posts
Search