INTO OUTFILE 구문 사용

https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file

쿼리시 INTO OUTFILE 구문을 사용하여 결과를 파일로 저장

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Access denied 처리

https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold

다음과 같은 에러가 날 경우 FILE 권한을 Grant

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

 GRANT FILE ON *.* TO 'kccfres'@'localhost';

secure-file-priv 처리

https://www.digitalocean.com/community/questions/mysql-can-t-use-load-data-infile-secure-file-priv-option-is-preventing-execution

아래와 같은 에러가 나고 저장이 안 될 때

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

Mysql client 로 연결후 SHOW variable 로 MySQL 환경 변수 확인

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

INTO OUTFILE 의 저장 경로를 위의 secure_file_priv 폴더로 변경
1. INTO OUTFILE '/tmp/orders.csv' → INTO OUTFILE '/var/lib/mysql-files/orders.csv'
쿼리 재실행

Browser not supported