저장 프로시저(Stored Procedure)까지 백업하려면

저장 프로시저(Stored Procedure)까지 백업하려면

저장 프로시저(Stored Procedure)는 데이터베이스 내부에 저장된 SQL 프로그램으로, 일반 데이터처럼 백업 대상에 포함될 수 있습니다. 다만 기본 백업 명령으로는 제외되므로 mysqldump 실행 시 --routines 옵션을 반드시 지정해야 합니다. 

---

1️⃣ 기본 원리

MySQL의 저장 프로시저, 함수, 트리거, 이벤트 등은 실제 데이터처럼 mysql 시스템 데이터베이스의 메타데이터로 저장되어 있습니다.
즉, mysqldump를 통해 데이터베이스 전체를 백업하면 이들 객체도 같이 백업됩니다.

---

2️⃣ mysqldump 백업 시 프로시저 포함 방법

mysqldump -u root -p --routines --events mydb > mydb_backup.sql

 

옵션	의미
--routines	저장 프로시저와 사용자 정의 함수(Stored Function) 포함
--events	이벤트 스케줄러(Event Scheduler) 포함
--triggers	트리거 포함 (기본적으로 ON)

⚠️ --routines 옵션을 빠뜨리면 프로시저와 함수는 백업되지 않습니다.
따라서 DB 전체 백업 시 반드시 포함해야 합니다.

---

3️⃣ 예시

mysqldump -u root -p --routines --events --triggers mydb > mydb_full.sql

생성된 mydb_full.sql 파일을 열어보면 하단에 다음과 같은 구문이 들어 있습니다.

/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `getDeptEmployees`(...)
BEGIN
   ...
END */;

→ 이렇게 프로시저가 SQL 스크립트로 포함되어 있습니다.

 

---

4️⃣ 복원 시 (Restore)

복원할 때는 일반 SQL처럼 source 명령이나 < 리디렉션으로 실행하면 됩니다.

mysql -u root -p mydb < mydb_full.sql

프로시저 정의문도 자동으로 재생성됩니다.

---

5️⃣ Workbench에서의 백업

MySQL Workbench GUI에서 Data Export 기능을 사용할 때,
‘Advanced Options’ 또는 ‘Dump stored routines and events’를 반드시 체크해야 합니다.

✅ 체크 항목:

• Dump stored routines (프로시저, 함수)
• Dump events (이벤트)
• Dump triggers (트리거)

---

6️⃣ 데이터베이스 간 복사(마이그레이션) 시 주의점

• 프로시저 내 DEFINER='user@host' 정보 때문에 권한 문제가 발생할 수 있습니다.
  → 복원 후 아래 명령으로 점검 가능합니다. 필요시 ALTER DEFINER 또는 CREATE DEFINER=CURRENT_USER로 수정하세요.
• SELECT db, name, type, definer FROM mysql.proc;

---

✅ 정리

구분	포함 여부	설정 방법
데이터(테이블 내용)	기본 포함	mysqldump mydb
트리거	기본 포함	자동
저장 프로시저/함수	기본 미포함	--routines 추가
이벤트	기본 미포함	--events 추가

---

🔧 추천 백업 명령 (안전한 기본형)

mysqldump -u root -p --routines --events --triggers --single-transaction --quick mydb > mydb_backup.sql

추천 백업 명령 사용(root 권한을 가진 manager 활용)

---