쿼리 결과를 파일로 저장하기

SELECT * INTO OUTFILE '파일경로를 포함한 파일명'
         FIELDS TERMINATED BY ','
         ENCLOSED BY '"'
         ESCAPED BY '\\'
         LINES TERMINATED BY '\n'
FROM 테이블 limit 2;


또는


SELECT * FROM 테이블 limit 2
INTO OUTFILE '파일경로를 포함한 파일명'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'

 

권한 오류 발생

ERROR 1045 (28000): Access denied for user '계정'@'localhost' (using password: YES)

GRANT FILE ON *.* TO '계정'@'localhost';

 

secure-file-priv 오류

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

저장 허용하는 경로 확인

mysql> SHOW VARIABLES LIKE "secure_file_priv";

해당 경로로 수정

일반적으로 /var/lib/mysql-files/

 

 

쉘 스크립트 예제

rm -f /var/lib/mysql-files/list.csv
lastid=`cat /core/log/lastid.log`
/usr/bin/mysql -u계정 -p패스워드 << EOF
SELECT * FROM 테이블 where idx > $lastid order by idx desc into outfile '/var/lib/mysql-files/list.csv' fields terminated by ',' lines terminated by '\n';
EOF
mv /var/lib/mysql-files/list.csv /home/log/