Can't find the file created by outfile in MySQL
file, file-io, mysql, php
Solution
MySQL may be writing the file into its own data directory, like `/var/lib/mysql/<databasename>` for example. To specify the path, use a full path.
However, it must be a directory that is writable by the user account the MySQL server daemon is running under. For that reason, I'll often use `/tmp`:
Specify the path you want to write to as in:
INTO OUTFILE '/tmp/mydata.csv'
And note that MySQL will write the file on the MySQL server, not on your client machine. Therefore remote connections will create output files on the remote server. See also SELECT INTO OUTFILE local ? for more details and workarounds.
Systemd & Linux
A note about writing to `/tmp` on a Linux system running `systemd`:
Some years after originally posting this, I found myself unable to locate a file written to `/tmp` via
...INTO OUTFILE '/tmp/outfile.csv'
on a MariaDB 5.5 server running Fedora Linux with `systemd`. Instead of writing the file directly to `/tmp/outfile.csv` as specified, that directory and file were created beneath a systemd directory in `/tmp`:
/tmp/systemd-mariadb.service-XXXXXXX/tmp/outfile.csv
While the file `outfile.csv` itself and the `tmp/` subdirectory were both created world-writable, the systemd service directory itself has 700 permissions and is root-owned, requiring `sudo` access to retrieve the file within it.
Rather than specifying the absolute path in MariaDB as `/tmp/outfile.csv` and specifying it relatively as `outfile.csv`, the file was written as expected into MariaDB's data directory for the currently selected database.
Problem
I am using the following query to create a CSV file ``` SELECT email INTO OUTFILE "mydata.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM users; ``` But i am unable to find the mydata.csv file anywhere when i am searching through filezilla. Any idea where this file is getting stored? The query runs successfully without any errors! Any Help?