By aPOST in SQL — 2024년 2월 2일

[sql] MySQL select 쿼리 결과를 파일로 저장하기 - Error Code 1290

SELECT INTO OUTFILE 문은 MySQL 데이터베이스에서 쿼리 결과를 파일로 내보내기 위해 사용되는 SQL 문입니다. 이 문은 SELECT 쿼리의 결과를 특정 파일에 저장하는 데에 주로 사용됩니다.

SELECT INTO OUTFILE 문의 기본 사용 문법은 다음과 같습니다. "file_path"에는 쿼리 결과를 저장하는 파일명을 표시합니다. "queryresult.txt"처럼 구분할 수 있는 파일 이름을 사용하는 것이 좋으며, 경로 표시가 없으면 현재 위치에 파일이 생성됩니다.

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM table_name
WHERE condition;

쿼리문은 SELECT 문과 완전히 같으며, SELECT 선택 컬럼 뒤에 "INTO OUTFILE '파일명'"으로 쿼리 결과를 저장할 파일을 표시하는 절이 추가된 것입니다.

예를 들어 product 테이블에서 품절 상품 정보를 출력해서 엑셀에서 확인하기 위해 파일로 상품 정보를 "product.csv" 파일에 덤프를 하는 쿼리문을 작성해보겠습니다.

SELECT product_id, product_name, madein, vendor, maker
INTO OUTFILE '/tmp/product.csv'
FROM product
WHERE instock = 0;

리눅스 환경일 경우 루트 권한이 없는 계정으로 쿼리문을 실행한다면 내 계정 홈 경로 밑에 파일을 생성해야 합니다. "~/product.csv" 처럼 내 계정의 홈 경로를 짧게 표현할 수 있습니다.

출력되는 파일 포맷은 텍스트 파일이며, 탭으로 구분되는 CSV 포맷입니다. 예를 들어 다음과 같이 출력됩니다.

1	무야호 블루투스 키보드	화이트	./img/large/kbd.jpg	2
2	라이언 바디 필로우		./img/large/pillow.jpg	1
3	피치핑크 잠옷 세트	옐로우,퍼플	./img/large/pajama.jpg	1
4	무지 3색 볼펜		./img/large/ballpoint3.jpg	1
5	티라노 12색 색연필 세트		./img/large/colorpen12.jpg	3
7	무야호 블루투스 키보드-레드	화이트	./img/large/kbd.jpg	2

SELECT INTO OUTFILE 파일 쓰기 권한

파일 쓰기 권한과 관련해서 리눅스 환경에서 MySQL 커맨드, 또는 쿼리문을 실행하는 계정의 쓰기 권한과 MySQL의 파일 보안 관련 옵션 두 가지를 확인해야 합니다.

리눅스 파일 쓰기 권한

리눅스 환경에서 사용할 경우, 파일 쓰기(수정) 권한이 없는 디렉토리 위치, 또는 디스크에는 SELECT INTO OUTFILE 문을 사용할 수 없습니다. 특히 웹 호스팅 환경일 경우, 데이터 덤프를 위해 터미널 환경에서 SELECT INTO OUTFILE 문을 사용할 때는 쓰기 권한이 있는 디렉토리를 생성 파일 경로로 지정해야 쿼리문을 사용할 수 있습니다.

호스팅 환경에서 SELECT INTO OUTFILE 문을 사용할 때는 다음 두 가지를 꼭 확인해야 합니다.

MySQL 서버가 결과를 저장할 파일의 경로에 쓰기 권한이 있어야 합니다.
사용자가 해당 디렉토리에 파일을 생성할 수 있는 권한이 있어야 합니다.

MySQL 파일 쓰기 권한

MySQL이 "--secure-file-priv" 모드로 실행되고 있으면 SELECT INTO OUTFILE 문을 사용할 수 없습니다. MySQL을 기본 설치 후 사용하고 있으면 이 옵션이 기본으로 포함되어 켜져있기 때문에 다음과 같은 에러를 만나게 됩니다.

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

MySQL 5.1.17 이후부터 보안을 위해 읽기/쓰기 전용 경로를 제한하면서 추가된 내용입니다.

이 에러는 --secure-file-priv 옵션이 MySQL 서버가 파일을 읽거나 쓸 수 있는 디렉토리를 제한하기 때문에 발생합니다.

--secure-file-priv 옵션을 사용할 때 MySQL은 파일을 저장할 수 있는 정해진 디렉토리를 변수 값으로 가지고 있으며, 이 디렉토리에만 쓰기를 할 수 있습니다.

다음 쿼리문으로 secure_file_priv 옵션을 사용할 때 쓰기를 할 수 있도록 설정한 디렉토리를 확인할 수 있습니다.

SHOW VARIABLES LIKE 'secure_file_priv';

value 항목에 표시된 값이 쓰기를 할 수 있는 경로입니다. 리눅스 환경에서는 "/var/lib/mysql-files" 디렉토리가 쓰기 가능한 경로이며, 윈도우 환경에서는 "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/" 처럼 MySQL 설치 버전에 따른 경로가 지정됩니다. MySQL 설치시 기본 옵션 값입니다.

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

앞서의 예제 쿼리문이 문제 없이 실행되려면 다음처럼 수정해야 합니다.

SELECT product_id, product_name, madein, vendor, maker
INTO OUTFILE '/var/lib/mysql-files/product.csv'
FROM product
WHERE instock = 0;

파일 쓰기 경로를 제한하는 것은 보안상 안전을 위해서이기 때문에 secure_file_priv 옵션을 해제하고 MySQL을 사용하는 것은 바람직하지 않습니다. XSS 공격으로 DB를 통해 시스템으로 우회 침투를 해서 서버를 점령할 수 있기 때문에 MySQL 서버가 파일을 쓸 수 있는 권한은 반드시 제한해야 합니다.

쓰기 가능하도록 정해진 경로에 생성된 덤프 파일을 복사해오는 것을 추천합니다.

임시로 이 제한을 해제하려면 MySQL 환경 설정 파일인 my.cnf에서 secure-file-priv="" 로 설정 경로를 지운 후 MySQL을 재시작하면 됩니다.

언급한대로 보안상 위험하므로 영구적으로 해제하고 사용하는 것은 좋지 않습니다. 그것 보다는 my.cnf 환경 파일의 secure-file-priv 옵션 값에 쓰기 권한이 있는 다른 경로를 지정해서 사용하는 것을 권장합니다.

파일 경로와 파일 중복 주의사항

윈도우용 MySQL에서 secure_file_priv 옵션에 경로를 표시할 때 디렉토리 구분을 윈도우 운영체제의 구분자인 "\"가 아닌 "/"로 합니다. 윈도우 환경에서 MySQL 쿼리문을 실행할 때는 디렉토리 경로 표시에 주의해야 합니다.
SELECT INTO OUTFILE로 파일을 생성할 때 파일 덮어쓰기가 되지 않으므로 중복 파일이 있을 경우 다른 파일명을 지정해야 합니다.

SELECT INTO OUTFILE 을 사용할 수 없을 때 우회 방법

MySQL 프롬프트에서 SELECT INTO OUTFILE 문을 실행할 수 없는 경우에도 제한을 우회하는 방법으로 쿼리 결과 파일을 생성할 수 있습니다. 리눅스 터미널에서 다음과 같이 파이프를 사용해 쿼리문을 전달하면 쿼리 결과를 파일로 저장할 수 있습니다.

echo "쿼리문" | mysql --user=db_username --password=db_password db_name > ~/queryresult.txt

이 커맨드는 쿼리 결과가 queryresult.txt 파일로 리다이렉션됩니다. 사용 파라미터 옵션들은 다음과 같습니다.

쿼리문: "SELECT * FROM product;"와 같은 실제 SQL 쿼리입니다.
db_username: 액세스하려는 데이터베이스의 MySQL 사용자 계정입니다.
db_password: 위에서 사용한 MySQL 사용자의 비밀번호입니다.
db_name: 액세스하려는 MySQL 데이터베이스의 이름입니다.

출력 파일 포맷 설정

SELECT INTO OUTFILE 문으로 출력하는 텍스트 파일은 탭으로 필드를 구분합니다. 일반적으로 사용하는 CSV 포맷과 구분자가 다르기 때문에 출력한 파일을 다른 프로그램에서 임포트 할 때 구분자를 따로 명시해야 하는 불편함이 있습니다.

구분자를 변경할 수 없으면 텍스트 편집기에서 재 가공을 하는 불편함이 있게 됩니다.

SELECT INTO OUTFILE 문은 INTO OUTFILE 절 뒤에 추가 절을 사용해서 몇 가지 출력 구분자를 수정할 수 있습니다.

예를 들어 다음 쿼리문은 필드 식별자를 ","로 하고, 줄 바꿈 표시는 "\r\n"으로 해서 파일을 출력하도록 합니다.

MySQL의 파일 출력 기본 줄 바꿈 표시는 "\n"이고, 필드 구분자는 탭입니다.

SELECT * FROM product INTO OUTFILE '/var/lib/mysql-files/product.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';