MySQL Query 결과 File로 저장

1. DB 접속 후, 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 deniey 발생 시
 ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
 
 GRANT FILE ON *.* TO 'kccfres'@'localhost';
 
 
 * secure-file-priv 처리
 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
 
 1. 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)

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

2. DB 접속하지 않은 상태에서 실행

mysql -user -pass -e "select cols from orders" dbname > /tmp/orders.csv

//리모트인 경우 
mysql -h {아이피} -user -pass -e "select cols from orders" dbname > /tmp/orders.csv

참고

https://www.lesstif.com/dbms/mysql-client-query-csv-text-54951965.html

https://xn--lg3bu39b.xn--mk1bu44c/77