MySQL 오류 처리 및 로깅

소개

이 랩에서는 기본적인 MySQL 오류 처리 및 로깅 기법을 배우게 됩니다. 효과적인 오류 관리는 견고하고 유지보수 가능한 데이터베이스 애플리케이션을 구축하는 데 매우 중요하며, 문제 진단, 쿼리 실행 이해, 데이터 무결성 보장에 도움이 됩니다.

먼저 서버로 전송되는 모든 SQL 문을 캡처하기 위해 일반 쿼리 로그 (general query log) 를 활성화하여 디버깅 및 감사에 유용한 도구로 활용할 것입니다. 다음으로, 예상치 못한 오류를 우아하게 관리하기 위해 DECLARE HANDLER를 사용하여 저장 프로시저 내에서 오류 처리를 구현할 것입니다. 또한 비즈니스 규칙을 강제하기 위해 SIGNAL 문을 사용하여 사용자 정의 오류 조건 (custom error conditions) 을 생성하고 트리거하는 방법을 배우게 됩니다. 마지막으로 서버 작업 및 중요 문제에 대한 필수 정보를 포함하는 MySQL 오류 로그 (MySQL error log) 를 검사할 것입니다.

이 랩이 끝나면 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';"

이렇게 하면 로그가 계속 증가하여 서버 성능에 영향을 미치는 것을 방지할 수 있습니다.

저장 프로시저에서 오류 처리하기

저장 프로시저 (Stored Procedure) 는 기본 키 (primary key) 열에 중복 데이터를 삽입하려는 시도와 같이 여러 가지 이유로 실패할 수 있습니다. 오류 처리기 (error handler) 를 사용하면 이러한 오류를 잡아내고 프로시저가 충돌하는 것을 방지하면서 우아하게 대응할 수 있습니다. 이 단계에서는 중복 키 오류에 대한 오류 처리기를 포함하는 저장 프로시저를 생성할 것입니다.

먼저 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 명령은 문장 구분 기호 (statement terminator) 를 ;에서 //로 변경하여 프로시저 본문 내의 세미콜론이 올바르게 처리되도록 합니다.

DELIMITER //

CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    -- 중복 키 오류에 대한 EXIT 핸들러 선언
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT 'Error: Product with this ID already exists.' AS message;
    END;

    -- 제품 삽입 시도
    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
    -- 수량이 음수인지 확인
    IF p_quantity < 0 THEN
        -- 사용자 정의 오류 발생시키기
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Error: Quantity cannot be negative.';
    END IF;

    -- 확인이 통과되면 수량 업데이트
    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 오류 로깅이 일반적인 로그 파일 대신 컨테이너의 표준 오류 스트림 (standard error stream) 으로 출력되도록 구성되는 경우가 많습니다. 이는 "12-factor app" 방법론을 따르기 위한 Docker 컨테이너의 일반적인 관행입니다.

존재하지 않는 데이터베이스에 액세스하려고 시도하여 오류 처리를 시연해 보겠습니다.

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)"

다양한 환경에서의 오류 로깅 이해:

1. 전통적인 설치: 오류 로그는 /var/log/mysql/error.log와 같은 파일에 기록됩니다.
2. 컨테이너화된 환경: 오류는 종종 stdout/stderr로 전송되어 컨테이너 런타임에 의해 캡처됩니다.
3. 클라우드 데이터베이스: 오류 로그는 일반적으로 클라우드 제공업체의 관리 인터페이스를 통해 액세스됩니다.

프로덕션 환경에서는 다음과 같은 명령을 사용하여 정기적으로 오류 로그를 검토하게 됩니다.

• 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 데이터베이스의 무결성을 보장하는 데 더 나은 준비를 갖추게 되었습니다.