소개
이 실습에서는 MySQL의 기본적인 오류 처리 및 로깅 기법을 배웁니다. 효과적인 오류 관리는 견고하고 유지 관리하기 쉬운 데이터베이스 애플리케이션을 만드는 데 중요합니다. 오류를 적절히 관리하면 문제를 진단하고, 쿼리 실행을 파악하고, 데이터 무결성을 보장할 수 있습니다.
먼저 서버에 전송된 모든 SQL 문을 기록하는 일반 쿼리 로그를 활성화합니다. 이 로그는 디버깅과 감사에 유용하지만, 성능에 영향을 주고 디스크 공간을 많이 차지할 수 있으므로 일시적으로 사용해야 합니다. 다음으로 저장 프로시저에서 DECLARE HANDLER를 사용해 예기치 않은 오류를 정상적으로 처리합니다. SIGNAL 문으로 사용자 정의 오류 조건을 만들고 발생시켜 비즈니스 규칙을 적용하는 방법도 배웁니다. 마지막으로 서버 작업과 중요한 문제에 대한 정보가 담긴 MySQL 오류 로그를 확인합니다.
이 실습을 마치면 MySQL 오류 처리 및 로깅의 기본기를 익혀 더욱 안정적인 데이터베이스 솔루션을 구축할 수 있습니다.
일반 쿼리 로그 활성화 및 확인
일반 쿼리 로그는 클라이언트에서 받은 모든 SQL 문을 기록합니다. 디버깅과 감사에 매우 유용하지만, 성능에 영향을 주고 디스크 공간을 많이 차지할 수 있으므로 일시적으로 사용해야 합니다. 이 단계에서는 로그를 활성화하고, 몇 가지 작업을 실행한 다음, 로그 파일을 확인합니다.
먼저 데스크톱에서 터미널을 엽니다.
root 사용자로 MySQL 서버에 연결합니다. 이 실습 환경에서는 sudo를 사용하면 비밀번호 없이 연결할 수 있습니다.
sudo mysql -u root
mysql> 프롬프트가 나타나면 전역 일반 쿼리 로그를 활성화합니다.
SET GLOBAL general_log = 'ON';
기본적으로 로그 파일은 MySQL 데이터 디렉터리에 저장됩니다. 더 쉽게 확인할 수 있도록 로그 파일 위치를 /tmp 디렉터리로 변경합니다.
SET GLOBAL general_log_file = '/tmp/mysql_general.log';
다음 명령을 실행해 변경한 위치를 확인합니다.
SHOW VARIABLES LIKE 'general_log_file';
출력에 방금 지정한 경로가 표시되어야 합니다.
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log_file | /tmp/mysql_general.log |
+------------------+-------------------------+
1 row in set (0.01 sec)
이제 로그 항목을 생성하도록 명령을 몇 개 실행합니다.
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
SELECT 'Logging this query' AS message;
명령 실행을 마치면 MySQL 셸을 종료합니다.
exit
터미널로 돌아와 로그 파일의 내용을 확인합니다.
sudo cat /tmp/mysql_general.log
실행한 명령과 연결 정보, 타임스탬프가 표시됩니다. 이를 통해 일반 쿼리 로그가 정상적으로 작동하는지 확인할 수 있습니다.
/usr/sbin/mariadbd, Version: 10.6.18-MariaDB-0ubuntu0.22.04.1 (Ubuntu 22.04). started with:
Tcp port: 3306 Unix socket: /run/mysqld/mysqld.sock
Time Id Command Argument
250728 14:12:46 33 Query SHOW VARIABLES LIKE 'general_log_file'
250728 14:12:50 33 Query CREATE DATABASE IF NOT EXISTS testdb
33 Query SELECT DATABASE()
33 Init DB testdb
33 Query show databases
33 Query show tables
33 Query SELECT 'Logging this query' AS message
250728 14:12:56 33 Quit
마지막으로 작업을 마치면 로그를 비활성화하는 것이 좋습니다. 터미널에서 바로 비활성화할 수 있습니다.
sudo mysql -u root -e "SET GLOBAL general_log = 'OFF';"
이렇게 하면 로그 파일이 계속 커져 서버 성능에 영향을 주는 것을 막을 수 있습니다.
저장 프로시저에서 오류 처리
저장 프로시저는 기본 키 열에 중복 데이터를 삽입하려는 경우처럼 여러 가지 이유로 실패할 수 있습니다. 오류 핸들러를 사용하면 오류를 포착해 프로시저가 중단되는 대신 적절히 처리할 수 있습니다. 이 단계에서는 중복 키 오류를 처리하는 오류 핸들러가 포함된 저장 프로시저를 만듭니다.
먼저 MySQL 서버에 연결합니다.
sudo mysql -u root
testdb 데이터베이스가 없으면 만들고, 해당 데이터베이스로 전환합니다. 그런 다음 products 테이블을 만듭니다.
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
quantity INT
);
이제 새 제품을 삽입하는 저장 프로시저를 만듭니다. 이 프로시저에는 중복 키 오류(SQLSTATE 23000)를 포착하고 사용자 정의 메시지를 반환하는 DECLARE HANDLER가 포함되어 있습니다.
DELIMITER 명령은 문 종결자를 ;에서 //로 변경합니다. 이렇게 하면 프로시저 본문 안의 세미콜론을 올바르게 처리할 수 있습니다.
DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
-- Declare an EXIT handler for duplicate key errors
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'Error: Product with this ID already exists.' AS message;
END;
-- Attempt to insert the product
INSERT INTO products (id, name, quantity) VALUES (p_id, p_name, 0);
SELECT 'Product inserted successfully.' AS message;
END //
DELIMITER ;
프로시저를 테스트해 보겠습니다. 먼저 새 제품을 삽입합니다.
CALL insert_product(1, 'Laptop');
삽입이 성공하고 성공 메시지가 반환되어야 합니다.
+--------------------------------+
| message |
+--------------------------------+
| Product inserted successfully. |
+--------------------------------+
1 row in set (0.00 sec)
이번에는 같은 id를 가진 제품을 삽입해 봅니다.
CALL insert_product(1, 'Desktop');
이번에는 오류 핸들러가 실행되어 일반적인 MySQL 오류 대신 사용자 정의 오류 메시지가 표시됩니다.
+-----------------------------------------------+
| message |
+-----------------------------------------------+
| Error: Product with this ID already exists. |
+-----------------------------------------------+
1 row in set (0.00 sec)
이처럼 오류 핸들러를 사용하면 저장 프로시저를 더 사용하기 쉽고 견고하게 만들 수 있습니다.
SIGNAL로 사용자 정의 오류 발생시키기
오류 핸들러는 오류를 포착하는 반면, SIGNAL 문은 오류를 발생시킵니다. 표준 데이터베이스 제약 조건으로 처리할 수 없는 비즈니스 규칙을 적용할 때 유용합니다. 이 단계에서는 SIGNAL을 사용해 제품 수량에 음수가 입력되지 않도록 하는 프로시저를 만듭니다.
현재도 MySQL 셸에 연결되어 있어야 합니다. 연결이 끊겼다면 다시 연결합니다.
sudo mysql -u root
testdb 데이터베이스를 사용 중인지 확인합니다.
USE testdb;
이제 제품 수량을 업데이트하는 저장 프로시저를 만듭니다. 이 프로시저는 새 수량이 음수인지 확인하고, 음수라면 SIGNAL로 사용자 정의 오류를 발생시킵니다.
DELIMITER //
CREATE PROCEDURE update_quantity(IN p_id INT, IN p_quantity INT)
BEGIN
-- Check if the quantity is negative
IF p_quantity < 0 THEN
-- Raise a custom error
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: Quantity cannot be negative.';
END IF;
-- Update the quantity if the check passes
UPDATE products SET quantity = p_quantity WHERE id = p_id;
SELECT 'Quantity updated successfully.' AS message;
END //
DELIMITER ;
SQLSTATE '45000'은 사용자 정의 오류에 사용하는 일반 상태 코드입니다. MESSAGE_TEXT는 클라이언트에 표시할 오류 메시지를 설정합니다.
프로시저를 테스트해 보겠습니다. 먼저 이전 단계에서 만든 'Laptop' 제품의 수량을 올바른 값으로 업데이트합니다.
CALL update_quantity(1, 50);
명령이 정상적으로 실행되어야 합니다.
+--------------------------------+
| message |
+--------------------------------+
| Quantity updated successfully. |
+--------------------------------+
1 row in set (0.00 sec)
이제 음수로 수량을 업데이트해 봅니다.
CALL update_quantity(1, -10);
이 호출은 SIGNAL 문을 실행하고, 사용자 정의 오류를 표시하며 프로시저를 종료합니다.
ERROR 1644 (45000): Error: Quantity cannot be negative.
이 결과를 통해 SIGNAL을 사용해 데이터베이스에서 사용자 정의 비즈니스 로직을 적용할 수 있음을 확인할 수 있습니다.
MySQL 오류 로그 확인
MySQL 오류 로그는 서버 수준 문제를 진단하는 주요 자료입니다. 서버의 시작 및 종료 이벤트, 심각한 오류, 경고가 기록됩니다. 이 단계에서는 오류 로그 구성을 확인하고, 컨테이너 환경과 기존 설치 환경의 로깅 방식을 비교합니다.
현재도 MySQL 셸에 연결되어 있어야 합니다. 먼저 log_error 변수를 조회해 오류 로그 파일의 위치를 확인합니다.
SHOW VARIABLES LIKE 'log_error';
이 LabEx VM 환경(Docker 컨테이너)에서는 오류 로그 경로가 비어 있을 수 있습니다.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error | |
+---------------+-------+
1 row in set (0.001 sec)
참고: 이 LabEx VM처럼 컨테이너를 사용하는 환경에서는 MySQL/MariaDB 오류 로그가 기존 로그 파일 대신 컨테이너의 표준 오류 스트림으로 출력되도록 구성되는 경우가 많습니다. 이는 Docker 컨테이너에서 "12-factor app" 방법론을 따를 때 흔히 사용하는 방식입니다.
존재하지 않는 데이터베이스에 접근해 오류 처리를 확인합니다.
USE non_existent_database;
예상대로 클라이언트에서 명령이 실패합니다.
ERROR 1049 (42000): Unknown database 'non_existent_database'
이제 MySQL 셸을 종료하고 터미널로 돌아갑니다.
exit
기존 방식으로 MySQL을 설치한 운영 환경에서는 일반적으로 Ubuntu 시스템의 /var/log/mysql/error.log에서 오류 로그를 찾을 수 있습니다. 기존 로그 파일이 있는지 다음 명령으로 확인합니다.
sudo ls -la /var/log/mysql/ 2> /dev/null || echo "MySQL log directory not found (normal in containerized environments)"
환경별 오류 로깅 방식 이해하기:
- 기존 설치 환경:
/var/log/mysql/error.log와 같은 파일에 오류 로그가 기록됩니다. - 컨테이너 환경: 오류가 stdout/stderr로 전송되고 컨테이너 런타임에서 수집되는 경우가 많습니다.
- 클라우드 데이터베이스: 일반적으로 클라우드 공급자의 관리 인터페이스에서 오류 로그를 확인합니다.
운영 환경에서는 다음과 같은 명령을 사용해 오류 로그를 정기적으로 확인합니다.
sudo tail -f /var/log/mysql/error.log(로그를 실시간으로 확인)sudo grep -i error /var/log/mysql/error.log(특정 오류 검색)
이러한 작업은 서버 상태를 모니터링하고 시작 실패, 테이블 손상, 권한 문제와 같은 문제를 해결하는 데 필수적입니다.
요약
이 실습에서는 MySQL의 기본적인 오류 처리 및 로깅 기법을 배웠습니다. 먼저 SQL 문을 추적하는 데 중요한 일반 쿼리 로그를 활성화하고 구성했습니다. 그런 다음 저장 프로시저에서 DECLARE HANDLER를 사용해 견고한 오류 처리를 구현하고, 특정 오류를 적절히 처리하는 방법을 익혔습니다.
또한 SIGNAL 문으로 사용자 정의 오류를 발생시켜 비즈니스 규칙을 적용하고 명확하고 구체적인 피드백을 제공하는 방법을 배웠습니다. 마지막으로 기존 설치 환경과 Docker 컨테이너 같은 컨테이너 환경에서 오류 로그가 어떻게 다른지 등 MySQL 오류 로깅의 개념을 살펴봤습니다.
기존 MySQL 설치 환경에서는 /var/log/mysql/error.log와 같은 파일에 오류 로그를 기록하지만, 컨테이너 환경에서는 컨테이너 오케스트레이션 플랫폼과의 통합을 위해 오류 출력을 stdout/stderr로 전달하는 경우가 많다는 점을 익혔습니다. 이러한 차이를 이해하는 것은 최신 배포 환경에서 작업할 때 중요합니다.
이러한 기법을 익히면 다양한 배포 환경에서 안정적인 데이터베이스 애플리케이션을 구축하고, 문제를 효과적으로 해결하며, MySQL 데이터베이스의 무결성을 보장할 수 있습니다.
