[mysql] SELECT .. INTO OUTFILE
SELECT .. INTO OUTFILEÀ» »ç¿ëÇϸé ÆÄÀÏ·Î ´ýÇÁÇÒ ¼ö ÀÖ´Ù.
select id, name from members where id >= 1 INTO OUTFILE '/home/www/file.txt'
ÇÏÁö¸¸, mysql master¸¦ ½ÇÇàÇÒ ¶§, --secure-file-priv=path ¿É¼ÇÀ» ÁÖ¾î ½ÇÇàÇß´Ù¸é, slave¿¡¼µµ SELECT .. INTO OUTFILEÀ» »ç¿ëÇÒ ¼ö ¾ø´Ù.
Áï, ¾Æ·¡¿Í °°Àº ¿¡·¯°¡ ¹ß»ýÇÑ´Ù.
ERROR 1290 (HY000): The MariaDB server is running with the --secure-file-priv option so it cannot execute this statement
--secure-file-priv º¯¼ö¸¦ È®ÀÎÇغ»´Ù.
mysql> show variables like 'secure%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| secure_auth | OFF |
| secure_file_priv | /xxx/yyy |
+------------------+---------------+
mysql ½½·¹À̺ê·Î Á¢±ÙÇÏ¿© secure_file_priv ¿É¼ÇÀÇ °ª¿¡ ¸Â°Ô SELECT .. INTO OUTFILEÀ» »ç¿ëÇÑ´Ù ÇÏ´õ¶óµµ, mysql client ÀÇ ·ÎÄà µð·ºÅ丮¿¡´Â ÀüÇô ÀúÀåÇÒ ¼ö ¾ø´Ù.
select id, name from members where id >= 1 INTO OUTFILE '/xxx/yyy/file.txt'
½ÇÇàÀº µÇ´õ¶óµµ mysql master ¼¹ö¿¡ À§Ä¡ÇÑ /xxx/yyy¿¡¸¸ ÀúÀåµÇ´Ï ¿ø°Ý¿¡¼ ¿¬°áµÈ mysql clientÀÇ ·ÎÄà µð·ºÅ丮¿¡´Â mysql µ¥ÀÌÅ͸¦ ÀúÀåÇÒ ¼ö ¾ø´Ù.
Âü°í·Î,
secure_file_priv ¿É¼ÇÀº SELECT .. INTO .. ¿Í LOAD ¹®µµ ¿µÇâÀ» ÁØ´Ù.
Ãâó: http://knight76.tistory.com/entry/mysql-SELECT-INTO-OUTFILE [±è¿ëȯ ºí·Î±×(2004-2017)]