본문으로 건너뛰기

MySQL에서 SELECT 결과를 파일로 저장하고 복구하는 방법은 무엇인가요?

💡 MySQL의 SELECT INTO OUTFILE로 쿼리 결과를 파일로 저장하고, LOAD DATA INFILE로 파일 데이터를 테이블로 복구할 수 있습니다. 이 방법은 대용량 데이터 백업 및 마이그레이션에 유용합니다.

1. SELECT INTO OUTFILE - 쿼리 결과를 파일로 저장

1.1 기본 사용법

SELECT INTO OUTFILE은 SELECT 쿼리 결과를 서버의 파일시스템에 텍스트 파일로 저장합니다.

기본 구문:

SELECT * INTO OUTFILE '/경로/파일명.txt'
FROM 테이블명;

실전 예시:

mysql> USE mysql;
Database changed

mysql> SELECT * INTO OUTFILE '/tmp/userlist.txt' FROM user;
Query OK, 6 rows affected (0.00 sec)

생성된 파일 확인:

[root@localhost ~]# ls -l /tmp/userlist.txt
-rw-rw-rw- 1 mysql mysql 542 Feb 25 13:42 /tmp/userlist.txt

[root@localhost ~]# cat /tmp/userlist.txt
localhost       root            Y       Y       Y       Y       Y       Y...
127.0.0.1       root            Y       Y       Y       Y       Y       Y...

특징:

  • 탭(Tab)으로 구분된 텍스트 파일 생성
  • **줄바꿈(Newline)**으로 행 구분
  • 컬럼명 및 테이블 구조 제외 (순수 데이터만 저장)
  • 파일이 이미 존재하면 오류 발생

1.2 파일 저장 위치

SELECT INTO OUTFILE로 생성된 파일은 MySQL 서버가 실행되는 시스템에 저장됩니다.

기본 저장 위치 (버전별):

MySQL 버전기본 저장 위치설정 변수
MySQL 5.5 이하제한 없음 (임의 경로 가능)-
MySQL 5.7 이상/var/lib/mysql-files/ (보안 강화)secure_file_priv
MySQL 8.0/var/lib/mysql-files/secure_file_priv

secure_file_priv 확인:

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

가능한 값:

  • 경로 지정 (예: /var/lib/mysql-files/): 해당 디렉토리만 허용
  • 빈 문자열 (''): 모든 경로 허용 (보안 위험)
  • NULL: SELECT INTO OUTFILE 기능 완전 비활성화

secure_file_priv 변경 방법 (my.cnf):

[mysqld]
secure_file_priv = /home/mysql/export/

변경 후 MySQL 재시작:

[root@localhost ~]# systemctl restart mysqld

1.3 CSV 형식으로 저장

쉼표(,)로 구분된 CSV 파일로 저장할 수 있습니다.

CSV 구문:

SELECT * INTO OUTFILE '/tmp/userlist.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM user;

생성된 CSV 파일:

"localhost","root","","Y","Y","Y","Y","Y","Y"
"127.0.0.1","root","","Y","Y","Y","Y","Y","Y"

옵션 설명:

  • FIELDS TERMINATED BY ',': 필드를 쉼표로 구분
  • ENCLOSED BY '"': 필드를 큰따옴표로 감쌈
  • LINES TERMINATED BY '\n': 행을 줄바꿈으로 구분
  • OPTIONALLY ENCLOSED BY '"': 문자열 필드만 따옴표로 감쌈

1.4 특정 컬럼만 저장

필요한 컬럼만 선택하여 저장할 수 있습니다.

SELECT host, user, password INTO OUTFILE '/tmp/user_simple.txt'
FIELDS TERMINATED BY ','
FROM mysql.user;

1.5 WHERE 조건으로 필터링

조건에 맞는 데이터만 파일로 저장할 수 있습니다.

SELECT * INTO OUTFILE '/tmp/root_users.txt'
FROM mysql.user
WHERE user = 'root';

2. LOAD DATA INFILE - 파일 데이터를 테이블로 복구

2.1 기본 사용법

LOAD DATA INFILE은 파일의 데이터를 MySQL 테이블로 가져옵니다.

기본 구문:

LOAD DATA INFILE '/경로/파일명.txt'
INTO TABLE 테이블명;

실전 예시:

1단계: 동일한 구조의 테이블 생성

mysql> CREATE TABLE user_backup LIKE mysql.user;
Query OK, 0 rows affected (0.05 sec)

2단계: 데이터 복구

mysql> LOAD DATA INFILE '/tmp/userlist.txt' INTO TABLE user_backup;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

3단계: 복구된 데이터 확인

mysql> SELECT host, user FROM user_backup;
+-----------------------+------+
| host                  | user |
+-----------------------+------+
| localhost             | root |
| localhost.localdomain | root |
| 127.0.0.1             | root |
| ::1                   | root |
| localhost             |      |
| localhost.localdomain |      |
+-----------------------+------+
6 rows in set (0.00 sec)

2.2 CSV 파일 복구

CSV 형식 파일을 복구할 때는 SELECT INTO OUTFILE에서 사용한 옵션과 동일하게 지정해야 합니다.

LOAD DATA INFILE '/tmp/userlist.csv'
INTO TABLE user_backup
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2.3 컬럼 순서 지정

파일의 컬럼 순서가 테이블과 다를 경우, 명시적으로 지정할 수 있습니다.

LOAD DATA INFILE '/tmp/custom_order.txt'
INTO TABLE user_backup
(host, user, password);

2.4 중복 데이터 처리

REPLACE 옵션 - 기존 데이터 덮어쓰기:

LOAD DATA INFILE '/tmp/userlist.txt'
REPLACE INTO TABLE user_backup;

IGNORE 옵션 - 중복 데이터 무시:

LOAD DATA INFILE '/tmp/userlist.txt'
IGNORE INTO TABLE user_backup;

2.5 특정 행 건너뛰기 (헤더 제외)

CSV 파일에 헤더 행이 있을 경우, IGNORE n LINES로 건너뛸 수 있습니다.

LOAD DATA INFILE '/tmp/userlist_with_header.csv'
INTO TABLE user_backup
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

3. 실전 활용 예제

3.1 대용량 테이블 백업 및 복구

백업 (100만 행 테이블):

SELECT * INTO OUTFILE '/backup/orders_20250117.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
FROM orders
WHERE order_date >= '2025-01-01';

복구:

LOAD DATA INFILE '/backup/orders_20250117.txt'
INTO TABLE orders_backup
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

3.2 데이터베이스 간 데이터 이전

서버 A에서 데이터 추출:

-- 서버 A (source)
SELECT * INTO OUTFILE '/tmp/customers.txt' FROM customers;

파일을 서버 B로 전송:

[root@serverA ~]# scp /tmp/customers.txt root@serverB:/tmp/

서버 B에서 데이터 가져오기:

-- 서버 B (destination)
CREATE TABLE customers LIKE source_db.customers;  -- 구조 복사
LOAD DATA INFILE '/tmp/customers.txt' INTO TABLE customers;

3.3 Excel에서 사용할 CSV 파일 생성

Excel에서 읽을 수 있는 CSV 파일로 데이터를 추출합니다.

SELECT customer_name, email, phone, city INTO OUTFILE '/tmp/customer_list.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM customers
WHERE country = 'Korea';

주의: Windows Excel에서는 LINES TERMINATED BY '\r\n' 사용

3.4 정기 백업 스크립트 (Cron)

백업 스크립트 작성:

/home/scripts/daily_backup.sh:

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="your_password"

# 테이블 백업
mysql -u ${MYSQL_USER} -p${MYSQL_PASS} <<EOF
USE mydb;
SELECT * INTO OUTFILE '${BACKUP_DIR}/users_${DATE}.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
FROM users;
EOF

echo "[$(date)] Backup completed: users_${DATE}.txt" >> /var/log/mysql_backup.log

실행 권한 부여:

[root@localhost ~]# chmod +x /home/scripts/daily_backup.sh

Cron 등록 (매일 새벽 2시):

[root@localhost ~]# crontab -e
0 2 * * * /home/scripts/daily_backup.sh

4. 권한 및 보안

4.1 필요한 권한

SELECT INTO OUTFILE 권한:

  • FILE 권한 필요

권한 확인:

mysql> SHOW GRANTS FOR 'backup_user'@'localhost';
+-------------------------------------------------------+
| Grants for backup_user@localhost                      |
+-------------------------------------------------------+
| GRANT FILE ON *.* TO 'backup_user'@'localhost'        |
| GRANT SELECT ON mydb.* TO 'backup_user'@'localhost'   |
+-------------------------------------------------------+

권한 부여:

mysql> GRANT FILE ON *.* TO 'backup_user'@'localhost';
mysql> GRANT SELECT ON mydb.* TO 'backup_user'@'localhost';
mysql> FLUSH PRIVILEGES;

4.2 보안 고려사항

1. secure_file_priv 설정

  • 파일 저장 경로를 제한하여 보안 위험 감소
  • 프로덕션 환경에서는 반드시 설정 권장

2. 파일 권한 확인

  • 생성된 파일은 mysql 사용자 소유로 생성됨
  • 민감한 데이터는 권한 조정 필수
[root@localhost ~]# chmod 600 /tmp/userlist.txt
[root@localhost ~]# chown mysql:mysql /tmp/userlist.txt

3. FILE 권한 최소화

  • FILE 권한은 모든 데이터베이스에 적용됨
  • 꼭 필요한 사용자에게만 부여

5. 에러 및 트러블슈팅

5.1 오류: "The MySQL server is running with the --secure-file-priv option"

원인:

  • secure_file_priv로 지정된 디렉토리 외에 파일을 저장하려고 시도

해결:

-- 허용된 디렉토리 확인
mysql> SHOW VARIABLES LIKE 'secure_file_priv';

-- 올바른 경로 사용
mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/userlist.txt' FROM user;

5.2 오류: "File '/tmp/userlist.txt' already exists"

원인:

  • 동일한 파일명이 이미 존재함

해결:

# 기존 파일 삭제
[root@localhost ~]# rm /tmp/userlist.txt

# 또는 다른 파일명 사용
mysql> SELECT * INTO OUTFILE '/tmp/userlist_new.txt' FROM user;

5.3 오류: "Can't create/write to file '/tmp/userlist.txt' (Errcode: 13 - Permission denied)"

원인:

  • MySQL 사용자(mysql)에게 디렉토리 쓰기 권한이 없음

해결:

# 디렉토리 권한 확인
[root@localhost ~]# ls -ld /tmp
drwxr-xr-x 10 root root 4096 Feb 25 13:42 /tmp

# 디렉토리 권한 수정
[root@localhost ~]# chmod 1777 /tmp

5.4 오류: "LOAD DATA INFILE: Incorrect format"

원인:

  • SELECT INTO OUTFILE과 LOAD DATA INFILE의 구분자 옵션이 다름

해결:

  • 저장 시 사용한 옵션과 복구 시 옵션을 동일하게 맞춤
-- 저장 시
SELECT * INTO OUTFILE '/tmp/data.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM mytable;

-- 복구 시 동일한 옵션 사용
LOAD DATA INFILE '/tmp/data.txt'
INTO TABLE mytable
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

5.5 한글 깨짐 문제

원인:

  • 파일 인코딩과 MySQL 문자셋 불일치

해결:

-- UTF-8 문자셋 명시
LOAD DATA INFILE '/tmp/userlist.txt'
INTO TABLE user_backup
CHARACTER SET utf8mb4;

6. SELECT INTO OUTFILE vs mysqldump

기준SELECT INTO OUTFILEmysqldump
속도매우 빠름중간
저장 내용순수 데이터만테이블 구조 + 데이터 (SQL)
복구 용이성테이블 구조 별도 필요SQL 파일 실행만으로 복구
대용량 데이터적합 (효율적)적합 (대용량은 느릴 수 있음)
데이터 형식텍스트/CSVSQL INSERT 문
압축별도 압축 필요--compress 옵션 지원
트랜잭션없음 (일관성 보장 X)--single-transaction 지원
전체 DB 백업불가능 (테이블별)가능 (--all-databases)

권장 사용 시나리오:

  • SELECT INTO OUTFILE: 대용량 테이블 단일 백업, CSV 데이터 추출
  • mysqldump: 전체 데이터베이스 백업, 테이블 구조 포함 백업

7. 대안 방법

7.1 mysql 명령줄 출력 리다이렉션

[root@localhost ~]# mysql -u root -p -e "SELECT * FROM mysql.user" > /tmp/userlist.txt

7.2 mysqldump (테이블 구조 포함)

[root@localhost ~]# mysqldump -u root -p mydb mytable > /tmp/mytable_backup.sql

7.3 MySQL Workbench (GUI)

  • Table Data Export Wizard 사용
  • CSV, JSON, XML 등 다양한 형식 지원

8. Best Practices

  1. 정기 백업 자동화: Cron으로 주기적인 백업 스케줄 설정

  2. 백업 파일 압축: 디스크 공간 절약을 위해 gzip 압축

    [root@localhost ~]# gzip /backup/users_20250117.txt

  3. 백업 파일 원격 전송: rsync 또는 scp로 원격 서버에 백업

    [root@localhost ~]# rsync -avz /backup/ backup-server:/mysql-backups/

  4. 트랜잭션 일관성: 트랜잭션 중에 백업하면 일관성 문제 발생 가능

    • 가능하면 mysqldump의 --single-transaction 사용

  5. 파일명에 날짜 포함: 백업 파일 관리 용이

    userlist_20250117_020000.txt

9. 참고 자료