[MySQL] SELECT INTO OUTFILE + LOAD DATA INFILE 완벽 가이드 – 쿼리 결과 파일로 저장하고 다시 불러오는 방법

MySQL에서는 SELECT INTO OUTFILE을 사용해 쿼리 결과를 파일로 저장할 수 있고, 반대로 LOAD DATA INFILE을 사용하면 외부 파일의 데이터를 테이블로 불러올 수 있다.
이 두 기능을 조합하면, 복잡한 데이터 이관, 대용량 백업, 정기 보고서 자동화 등에 매우 효과적인 파이프라인을 구축할 수 있다.

✅ SELECT INTO OUTFILE로 결과를 저장하기

SELECT INTO OUTFILE은 SELECT 결과를 서버에 텍스트 파일로 저장한다. 기본 사용법은 다음과 같다.

SELECT user_id, user_name, email
FROM users
WHERE created_at >= CURDATE() - INTERVAL 7 DAY
INTO OUTFILE '/var/lib/mysql-files/new_users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

이 쿼리는 최근 7일간 가입한 사용자의 정보를 CSV 형식으로 저장한다.
저장된 파일은 FIELDS 옵션으로 구분자, 인코딩, 줄바꿈 방식을 설정할 수 있다.

파일 경로와 권한 주의사항

• MySQL은 기본적으로 secure_file_priv 변수로 지정된 디렉토리에서만 파일 입출력을 허용한다.
• 해당 디렉토리는 아래 쿼리로 확인 가능하다.

SHOW VARIABLES LIKE 'secure_file_priv';

반응형

✅ LOAD DATA INFILE로 파일 불러오기

저장한 CSV 파일을 다시 MySQL로 불러올 때 사용하는 명령이 LOAD DATA INFILE이다.

기본 문법

LOAD DATA INFILE '/var/lib/mysql-files/new_users.csv'
INTO TABLE users_backup
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

• INTO TABLE: 데이터를 불러올 테이블 지정
• IGNORE 1 LINES: 헤더 라인을 무시할 경우 사용
• FIELDS, LINES 옵션은 SELECT INTO OUTFILE과 동일하게 맞춰야 한다

대상 테이블은 반드시 존재해야 하며, 컬럼 순서도 파일 순서와 일치해야 한다

CREATE TABLE users_backup (
    user_id INT,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

✅ 실전 예제: 특정 테이블 데이터를 백업하고 복원하기

1단계 – 파일로 내보내기

SELECT * FROM orders
INTO OUTFILE '/var/lib/mysql-files/orders_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

2단계 – 새로운 테이블에 복사

CREATE TABLE orders_archive LIKE orders;

LOAD DATA INFILE '/var/lib/mysql-files/orders_backup.csv'
INTO TABLE orders_archive
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

이 예제는 orders 테이블의 데이터를 orders_archive 테이블로 파일을 통해 이관하는 과정이다.
간단한 INSERT INTO ... SELECT보다, 서버 간 파일 이동 또는 대용량 처리가 필요한 상황에 적합하다.

반응형

✅ 에러 방지 체크리스트

체크 항목	설명
파일 경로가 secure_file_priv 경로에 있는가?	SHOW VARIABLES로 확인 필요
LOAD 권한이 있는가?	FILE 권한이 있어야 함
FIELDS, LINES 옵션이 저장 시와 동일한가?	일치하지 않으면 파싱 오류 발생
테이블 구조가 파일 컬럼 순서와 일치하는가?	불일치 시 에러 또는 잘못된 값 삽입

 

✅ 보안과 권한 설정 팁

• 외부 사용자가 접근 가능한 경로에는 파일을 저장하지 않아야 한다
• MySQL 사용자에게는 FILE 권한이 있어야 INTO OUTFILE 및 LOAD DATA INFILE이 가능하다
• 로컬 환경에서 파일을 로드할 때는 LOCAL 옵션 사용

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE your_table;

단, 이 경우 --local-infile 옵션이 활성화되어 있어야 하며, 서버 측에서도 허용되어야 한다.

✅ INTO OUTFILE vs LOAD DATA INFILE 요약 비교

항목	INTO OUTFILE	LOAD DATA INFILE
역할	데이터 저장	데이터 불러오기
경로	서버 파일 경로	서버 or LOCAL 가능
주 사용 목적	CSV 추출, 보고서, 백업	초기 데이터 적재, 마이그레이션
성능	빠름	매우 빠름 (대량 데이터 처리에 적합)

두 기능을 함께 사용하면 MySQL만으로도 데이터 이관 및 추출 자동화 파이프라인을 구성할 수 있다.

✅ 공식 문서 링크

• MySQL SELECT INTO OUTFILE 공식문서
• MySQL LOAD DATA INFILE 공식문서