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?

Diallage

There is an alternative that involves using the tee command that allows you to log the input and output of mysql to a separate file on the client side, however this logs the entire mysql session, rather than outputting select query contents, and there's no formatting options like outputting to CSV style. – Antler

Answer 1

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 ..." > 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' > /tmp/myfile.txt

Heading

how would one add a delimiter? without the INTO OUTFILE I get an error – Polymath

By delimiter do you mean ` FIELDS TERMINATED BY ',' ` ? If so I am not sure how you would accomplish, maybe using CONCAT()? – Heading

Answer 2

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.)

Nicotinism

Answer 3

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.

Invitatory

LOAD DATA works fine but dumping the file while possible on the server that the DB is hosted at, is not possible to dump the file to the client which is on a different server. – Diallage

mysqldump and mysql -e swtich option both fall short of the intended result. mysqldump dumps the whole db/table and we cannot dump only few columns based on a condition. mysql -e swtich dumps the file to .txt format and pipe output cannot be achieved. – Diallage

Answer 4

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 && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && 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.

Foreland

PHP 7 is required because of line 81: $actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default; – Marc

Answer 5

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 > table.csv

Crural

Answer 6

Re: SELECT * INTO OUTFILE

Check if MySQL has permissions to write a file to the OUTFILE directory on the server.

Phototube

The OUTFILE is to write to the client's server which is different from the server that the database is hosted at. – Diallage

The MySQL docs (dev.mysql.com/doc/refman/5.0/en/select.html) defines the location of OUTFILE. It states: "The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. " So, I would check whether MySQL has permissions to write a file to the OUTFILE directory on the server. I don't know why there isn't a "LOCAL OUTFILE" version. If you need to export locally one option many people use is phpMyAdmin. – Phototube

Answer 7

Try setting path to /var/lib/mysql-files/filename.csv (MySQL 8). Determine what files directory is yours by typping SHOW VARIABLES LIKE "secure_file_priv"; in mysql client command line.

See answer about here: (...) --secure-file-priv in MySQL answered in 2015 by vhu user

Predicable