TRIGGER / 데이터 베이스 백업 및 복원을 알아보자.

자동으로 기록을 남기고, 데이터를 백업 및 복원하는 것인가.

 

 

오늘은 데이터의 무결성과 안전성을 유지하는 방법인 Trigger와 백업 및 복원에 대해 공부해 보자.

오늘의 배움

Trigger의 개념과 활용 데이터베이스 백업과 복원의 중요성

---

TRIGGER

Trigger는 특정 데이터 변경 이벤트(DML 작업: INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 저장된 프로시저의 한 종류이다. 데이터의 무결성과 일관성을 유지하는 데 사용되며, 자동으로 로직을 실행하여 업무 규칙을 강제하는 데 도움이 된다.

 

Trigger의 특징

• 특정 테이블에 대한 데이터 변경 이벤트에 반응한다.
• BEFORE 또는 AFTER 시점에 실행된다.
• 행 단위 또는 문장 단위로 실행될 수 있다.

예를 들어, 레스토랑에서 손님이 주문할 때마다 자동으로 주문 내역을 기록하는 시스템처럼, Trigger가 데이터 변경 시 이 기록을 남기는 역할을 한다고 느꼈다.

 

Trigger의 활용

• 데이터 무결성 유지
• 변경 이력(Log) 관리
• 복잡한 비즈니스 로직 구현
• 연관된 테이블 간의 데이터 동기화

Trigger의 제한 사항

1. 동일한 이벤트에 대해 동일한 시점의 트리거는 하나만 존재해야 한다. 한 테이블에 AFTER INSERT 트리거는 하나만 만들 수 있다.
2. 트리거 내에서 COMMIT, ROLLBACK 문을 사용한 트랜잭션 제어는 할 수 없다.
3. 권한 부여 GRANT 및 권한 철회 REVOKE를 사용할 수 없다.
4. 뷰(View)나 임시 테이블에 트리거 생성은 불가하다.

Trigger 사용 시 고려 사항

• 트리거 내에서 동일한 테이블을 변경하여 트리거가 다시 실행되는 무한 루프가 발생하지 않도록 주의한다.
• 복잡한 로직은 트리거 실행 시간을 늘려 전체 성능에 영향을 줄 수 있으므로 성능을 고려하여 생성한다.
• 트리거는 자동 실행되므로 디버깅이 어렵다. 따라서 충분한 로그를 남기도록 설계한다.

Trigger와 Stored Procedure 비교

Trigger	Stored Procedure
특정 이벤트에 반응하여 자동 실행	명시적으로 호출하여 실행
호출할 수 없고, 명시적 인수 전달이 불가능	인수 전달이 가능하며 복잡한 로직 구현 가능
주로 데이터 무결성 유지와 자동화된 로직 실행에 사용	데이터 처리, 계산, 업무 로직 구현 등에 사용

 

---

Trigger 생성 및 활용

Trigger 기본 구문

CREATE TRIGGER [트리거명]
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON [테이블명]
FOR EACH ROW
BEGIN
    -- 트리거 실행 시 수행할 SQL 문;
END;

 

INSERT Trigger

• tbl_menu_log 테이블 생성

CREATE TABLE tbl_menu_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    menu_code INT,
    menu_name VARCHAR(100),
    action VARCHAR(10),
    action_time DATETIME
);

• INSERT 트리거 생성

-- DELIMITER 변경
DELIMITER $$

-- 기존에 존재한다면 삭제
DROP TRIGGER IF EXISTS trg_after_menu_insert$$

-- AFTER INSERT 트리거 생성
CREATE TRIGGER trg_after_menu_insert
AFTER INSERT ON tbl_menu
FOR EACH ROW
BEGIN
    -- tbl_menu_log에 로그 기록 추가
    INSERT INTO tbl_menu_log (menu_code, menu_name, action, action_time)
    VALUES (NEW.menu_code, NEW.menu_name, 'INSERT', NOW());
END$$

-- DELIMITER 복구
DELIMITER ;

• 트리거 작동 확인

-- tbl_menu에 새로운 메뉴 추가
INSERT INTO tbl_menu (menu_name, menu_price, category_code, orderable_status)
VALUES ('트리거메뉴', 9000, 2, 'Y');

-- tbl_menu_log에서 로그 확인
SELECT * FROM tbl_menu_log;

 

 

UPDATE Trigger

• UPDATE 트리거 생성

-- DELIMITER 변경
DELIMITER $$

-- 기존에 존재한다면 삭제
DROP TRIGGER IF EXISTS trg_after_menu_update$$

-- AFTER UPDATE 트리거 생성
CREATE TRIGGER trg_after_menu_update
AFTER UPDATE ON tbl_menu
FOR EACH ROW
BEGIN
    INSERT INTO tbl_menu_log (menu_code, menu_name, action, action_time)
    VALUES (NEW.menu_code, NEW.menu_name, 'UPDATE', NOW());
END$$

-- DELIMITER 복구
DELIMITER ;

 

 

DELETE Trigger

• DELETE 트리거 생성

-- DELIMITER 변경
DELIMITER $$

-- 기존에 존재한다면 삭제
DROP TRIGGER IF EXISTS trg_after_menu_delete$$

-- AFTER DELETE 트리거 생성
CREATE TRIGGER trg_after_menu_delete
AFTER DELETE ON tbl_menu
FOR EACH ROW
BEGIN
    -- 삭제 로그 기록
    INSERT INTO tbl_menu_log (menu_code, menu_name, action, action_time)
    VALUES (OLD.menu_code, OLD.menu_name, 'DELETE', NOW());
END$$

-- DELIMITER 복구
DELIMITER ;

 

---

Trigger 삭제

DROP TRIGGER IF EXISTS trg_after_menu_insert;
DROP TRIGGER IF EXISTS trg_after_menu_update;
DROP TRIGGER IF EXISTS trg_after_menu_delete;

 

 

Trigger 목록 조회

데이터베이스에 존재하는 Trigger 목록 확인

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'menudb';   # 데이터베이스 명

 

 

Trigger 권한 관리

• Trigger 생성 권한
  • 트리거를 생성하려면 해당 테이블에 대한 CREATE TRIGGER 권한이 필요하다.

GRANT CREATE TRIGGER ON 데이터베이스명.* TO ohgiraffers@'%';

• 권한 철회

REVOKE CREATE TRIGGER ON 데이터베이스명.* FROM ohgiraffers@'%';

 

 

에러 핸들링

• 트리거 내에서 에러가 발생하면 해당 DML 작업 전체가 실패하고 롤백된다.
• SIGNAL 문을 사용하여 사용자 정의 에러를 발생시킬 수 있다.

---

데이터 베이스 백업 및 복원

 

백업은 데이터 손실에 대비하여 현재의 데이터베이스 상태를 저장하는 과정이며, 복원은 백업된 데이터를 사용하여 데이터베이스를 이전 상태로 복구하는 과정이다.

 

MySQL에서 백업과 복원은 데이터의 무결성과 지속성을 보장하는 데 필수적이며, 시스템 장애, 사용자 실수 또는 기타 예기치 않은 상황에 대비할 수 있도록 해준다.

 

백업의 종류

1. 논리적 백업(Logical Backup)
   • 데이터베이스의 구조와 데이터를 SQL 문장으로 덤프 하여 저장하는 방법이다.
   • mysqldump 유틸리티를 사용한다.
   • 이식성이 높고 특정 데이터만 백업 가능한 장점이 있지만, 대용량 데이터의 경우 시간이 오래 걸린다.
2. 물리적 백업(Physical Backup)
   • 데이터 파일 자체를 복사하여 백업하는 방법이다.
   • mysqlhotcopy, 파일 시스템 복사 등을 사용한다.
   • 속도가 빠르고 대용량 데이터에 적합하지만, 동일한 OS와 파일 시스템 환경이 필요하다는 제한점이 있다.

 

mysqldump를 이용한 백업

mysqldump를 이용한 백업

mysqldump -u [username] -p --all-databases > all_databases.sql

 

 

특정 데이터베이스 백업

특정 데이터베이스를 백업하는 명령어

mysqldump -u [username] -p [database_name] > [backup_file].sql

 

특정 테이블 백업

특정 테이블만 백업하는 명령어

mysqldump -u [username] -p [database_name] [table1 table2 ...] > [backup_file].sql

 

백업 옵션

• -add-drop-table: 테이블을 생성하기 전에 기존 테이블을 삭제하는 DROP TABLE 문 추가
• -routines: Stored Procedures와 Functions도 백업
• -triggers: 트리거도 백업
• -single-transaction: 백업 중 데이터 일관성을 위해 트랜잭션 사용 (InnoDB 엔진에 유용)
• 옵션 사용 예시

mysqldump -u root -p --single-transaction --routines --triggers my_database > my_database_full_backup.sql

---

mysqldump를 이용한 복원

 

전체 데이터베이스 복원

• 전체 데이터베이스를 복원하는 명령어

mysql -u [username] -p < [backup_file].sql

특정 데이터베이스 복원

• 데이터베이스를 생성한 후 복원

CREATE DATABASE my_database;

mysql -u [username] -p my_database < [backup_file].sql

특정 테이블 복원

• 데이터베이스 내에서 특정 테이블만 복원

mysql -u [username] -p [database_name] < [tables_backup].sql

---

물리적 백업 및 복원

물리적 백업 및 복원 절차

1. 데이터의 일관성을 위해 MySQL 서비스를 중지한다.

sudo systemctl stop mysql

 

2. 데이터 디렉토리를 압축하여 백업한다.

sudo tar -czvf mysql_data_backup.tar.gz /var/lib/mysql

 

3. 압축된 백업 파일을 해제하고 원래 위치에 복사한다.

sudo tar -xzvf mysql_data_backup.tar.gz -C /

 

4. MySQL 서비스를 다시 시작한다.

sudo systemctl start mysql

---

백업과 복원 시 주의 사항

1. 백업 파일에는 중요한 데이터가 포함되어 있으므로 보안 유지를 위해 적절한 권한 설정과 안전한 저장소에 보관한다.
2. 복원 과정에서 문제가 없는지 주기적으로 복원 테스트를 수행한다.
3. 백업 파일이 누적되면 스토리지 공간이 부족해질 수 있으므로 오래된 백업은 정리하는 등 스토리지를 지속적으로 관리한다.
4. -single-transaction 옵션을 사용하여 백업 중 데이터의 일관성을 유지한다.

 

---

 

 

데이터 백업과 복원은 무슨 일을 하던 가장 중요한 부분이라고 생각한다.

ctrl+s를 손에 달고 살던...

다만 저장하는 방식이 달라진 것뿐, 데이터베이스에서도 여전히 중요한 부분.

 

다음에는 SQL에서 가장 많이 사용된다는, 'SELECT'를 배워보자.