MySQL - SELECT INTO OUTFILE LOCAL

Question

MySQL is awesome  I am currently involved in a major server migration and previously  our small database used to be hosted on the same server as the client  So we used to do this   SELECT   INTO OUTFILE      LOAD DATA INFILE       Now  we moved the database to a different server and SELECT   INTO OUTFILE      no longer works  understandable - security reasons I believe  But  interestingly LOAD DATA INFILE      can be changed to LOAD DATA LOCAL INFILE      and bam  it works   I am not complaining nor am I expressing disgust towards MySQL  The alternative to that added 2 lines of extra code and a system call form a  sql script  All I wanted to know is why LOAD DATA LOCAL INFILE works and why is there no such thing as SELECT INTO OUTFILE LOCAL   I did my homework  couldn t find a direct answer to my questions above  I couldn t find a feature request   MySQL either  If someone can clear that up  that had be awesome    Is MariaDB capable of handling this problem

User · Accepted Answer

From the manual  The SELECT     INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine  If you want to create the resulting file on some client host other than the server host  you cannot use SELECT     INTO OUTFILE  In that case  you should instead use a command such as mysql -e  SELECT       gt   file name to generate the file on the client host     http   dev mysql com doc refman 5 0 en select html  An example   mysql -h my db com -u usrname--password pass db name -e  SELECT foo FROM bar   gt   tmp myfile txt

User · Answer

Since I find myself rather regularly looking for this exact problem  in the hopes I missed something before      I finally decided to take the time and write up a small gist to export MySQL queries as CSV files  kinda like https   stackoverflow com a 28168869 but based on PHP and with a couple of more options  This was important for my use case  because I need to be able to fine-tune the CSV parameters  delimiter  NULL value handling  AND the files need to be actually valid CSV  so that a simple CONCAT is not sufficient since it doesn t generate valid CSV files if the values contain line breaks or the CSV delimiter   Caution  Requires PHP to be installed on the server   Can be checked via php -v    Install  mysql2csv via  wget https   gist githubusercontent com paslandau 37bf787eab1b84fc7ae679d1823cf401 raw 29a48bb0a43f6750858e1ddec054d3552f3cbc45 mysql2csv -O mysql2csv -q  amp  amp   sha256sum mysql2csv   cmp  lt  echo  b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv       echo  ERROR comparing hash  Found    sha256sum mysql2csv     amp  amp  chmod  x mysql2csv    download content of the gist  check checksum and make it executable   Usage example    mysql2csv --file   tmp result csv  --query  SELECT 1 as foo  2 as bar   --user  username  --password  password    generates file  tmp result csv with content  foo bar 1 2   help for reference    mysql2csv --help Helper command to export data for an arbitrary mysql query into a CSV file  Especially helpful if the use of  SELECT     INTO OUTFILE  is not an option  e g  because the mysql server is running on a remote host   Usage example    mysql2csv --file   tmp result csv  --query  SELECT 1 as foo  2 as bar   --user  username  --password  password   cat  tmp result csv  Options          -q --query name  required                  The query string to extract data from mysql          -h --host name                  Default  127 0 0 1  The hostname of the mysql server          -D --database name                 The default database          -P --port name                  Default  3306  The port of the mysql server          -u --user name                 The username to connect to the mysql server          -p --password name                 The password to connect to the mysql server          -F --file name                  Default  php   stdout  The filename to export the query result to   php   stdout  prints to console           -L --delimiter name                  Default     The CSV delimiter          -C --enclosure name                  Default     The CSV enclosure  that is used to enclose values that contain special characters           -E --escape name                  Default     The CSV escape character          -N --null name                  Default   N  The value that is used to replace NULL values in the CSV file          -H --header name                  Default  1  If  0   the resulting CSV file does not contain headers          --help                 Prints the help for this command

User · Answer

Try setting path to  var lib mysql-files filename csv  MySQL 8   Determine what files directory is yours by typping SHOW VARIABLES LIKE  quot secure file priv quot   in mysql client command line  See answer about here        --secure-file-priv in MySQL answered in 2015 by vhu user

User · Answer

Re  SELECT   INTO OUTFILE   Check if MySQL has permissions to write a file to the OUTFILE directory on the server

User · Answer

Using mysql CLI with -e option as Waverly360 suggests is a good one  but that might go out of memory and get killed on large results   Havent find the reason behind it   If that is the case  and you need all records  my solution is  mysqldump   mysqldump2csv   wget https   raw githubusercontent com jamesmishra mysqldump-to-csv master mysqldump to csv py mysqldump -u username -p --host hostname database table   python mysqldump to csv py  gt  table csv

User · Answer

You can achieve what you want with the mysql console with the -s  --silent  option passed in   It s probably a good idea to also pass in the -r  --raw  option so that special characters don t get escaped   You can use this to pipe queries like you re wanting   mysql -u username -h hostname -p -s -r -e  select concat  this       works     EDIT  Also  if you want to remove the column name from your output  just add another -s  mysql -ss -r etc

User · Answer

The path you give to LOAD DATA INFILE is for the filesystem on the machine where the server is running  not the machine you connect from  LOAD DATA LOCAL INFILE is for the client s machine  but it requires that the server was started with the right settings  otherwise it s not allowed  You can read all about it here  http   dev mysql com doc refman 5 0 en load-data-local html As for SELECT INTO OUTFILE I m not sure why there is not a local version  besides it probably being tricky to do over the connection  You can get the same functionality through the mysqldump tool  but not through sending SQL to the server

[mysql] MySQL - SELECT * INTO OUTFILE LOCAL ?

Examples related to mysql

Examples related to sql

Examples related to mariadb

Examples related to into-outfile