54

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?

Marc B
  • 356,200
  • 43
  • 426
  • 500
user1460822
  • 573
  • 1
  • 4
  • 7
  • 3
    It is most likely in the MySQL data directory (like /var/lib/mysql). To specify a directory use a full path like `INTO OUTFILE '/tmp/mydata.csv'` – Michael Berkowski Jul 14 '12 at 14:20
  • 1
    @Michael I can't see anything like /var/lib/mysql in my root directory. i mean there's no such folder called var. Any way around? – user1460822 Jul 14 '12 at 14:26
  • Write it into `/tmp` which is likely to be there, and likely to be writable by the MySQL server user. – Michael Berkowski Jul 14 '12 at 14:28
  • 1
    The funny thing that is happening is when i use path as /tmp/mydata.csv, the query gets executed , however, there's no such folder called tmp in my root directory! :s – user1460822 Jul 14 '12 at 14:31
  • 2
    @user1460822: Are you on a windows system? Pretty much every unix system on the planet has /tmp. On windows you might need to use `c:\temp` and create that dir – Marc B Jul 14 '12 at 14:54
  • Also see related [answer here](http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile) – graceman9 Nov 13 '15 at 17:04
  • 1
    I believe the default behaviour is that it is written to the directory of the selected database, i.e. within the MySQL data directory (e.g. /var/lib/mysql/mydatabase). – Robin Keskisarkka Mar 29 '18 at 09:28

8 Answers8

80

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.

Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • The funny thing that is happening is when i use path as /tmp/mydata.csv, the query gets executed , however, there's no such folder called tmp in my root directory! :s – user1460822 Jul 14 '12 at 14:31
  • 1
    @user1460822 If this is shared hosting, it's possible you just wrote that file to the system's common /tmp directory (which you can't access). It will get cleaned out eventually by automated scripts. You need to write the file into a location you _can_ access, whatever that may be in your root directory. If your root is something like `/home/users/your_username/public_html`, use that. – Michael Berkowski Jul 14 '12 at 14:35
  • @MichaelBerkowski, I tried to put the file in `/home/username/myfolder/`. But I got `Persmission denied` error message. I did `chmod 777 /home/username/myfolder` and it still. Is it because of `chmod` only affect directories (not files)? – Oki Erie Rinaldi Nov 30 '15 at 06:35
  • Note that if your mysql server is remote it will put file in its filesystem, not where your scripts are, and you should use scp to get it. – Tertium Nov 30 '15 at 10:35
  • 1
    @OkiErieRinaldi If you are trying to write it into your home directory, you would need to ensure that the MySQL daemon user can traverse into it, which means adding execute permissions on `/home/username` with `chmod o+x /home/username` . Homedirs are usually inaccessible to all but the owning user by design, so if you want to do it this way, I would recommend add the execute permission, then write the file, then immediately revoke it `chmod o-x /home/username`. 777 on the destination directory should work as long as the parent dir has execute. – Michael Berkowski Nov 30 '15 at 13:59
  • @Tertium Yes, that wasn't very explicit in this answer. I added a link to another answer which explains the local issue thoroughly with workarounds. – Michael Berkowski Nov 30 '15 at 14:02
  • Yeah, I've spend some time searching files on vps1, while they were on vps2 :) – Tertium Nov 30 '15 at 17:44
  • "And note that MySQL will write the file on the MySQL server, not on your client machine. " - That was my problem, too! – Mel_T Jan 27 '16 at 11:20
  • Would have given you another upvote if possible. A note about writing to /tmp on a Linux system running systemd was the solution for my problem. – picmate 涅 Oct 15 '18 at 18:29
  • "/tmp/wog" as "/tmp/systemd-private-8c6e018a2da84e548935c16c457c9bc2-mariadb.service-uBrn8f/tmp/wog" on FC33 MariaDB 15.1 – Nufosmatic Jan 13 '21 at 16:51
19

I suspect you are looking for the file on your client machine.

SELECT .. INTO OUTFILE writes the file on the server machine.

Shlomi Noach
  • 9,073
  • 1
  • 23
  • 20
  • yep... this is the exact problem I had when looking into this issue. Even though you are running the command on MySQL workbench, the file is written out on the server. Once I figured that out, it was a simple thing to just scp the file to my local machine. – David S Sep 24 '12 at 15:17
  • very valuable detail! – Alberto Montellano Dec 22 '15 at 15:35
5

Just for Example:

I have run below query in MySQL Workbench & I found my file in

SELECT * FROM `db`.`table` INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',';

D:\wamp\bin\mysql\mysql5.6.17\data\db\file.csv

You can use below example to set your file path:

SELECT * FROM `db`.`table` INTO OUTFILE 'C:\\file.csv' FIELDS TERMINATED BY ',';
Nono
  • 6,986
  • 4
  • 39
  • 39
2

I found my INTO OUTFILE files, created with the following command

select name from users into outfile "name.csv"

at the following location

C:\ProgramData\MySQL\MySQL Server 5.6\data\name.csv

This is probably the default location, as I've never changed anything.

StackG
  • 2,730
  • 5
  • 29
  • 45
2

In case anyone on OS X has this problem, mysql installed with homebrew has default output located at /usr/local/var/mysql/database-name/

seeker_of_bacon
  • 1,939
  • 2
  • 19
  • 20
1

If you don't specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

Rick
  • 7,007
  • 2
  • 49
  • 79
0

You find the file in C:\wamp\bin\mysql\mysql5.5.24\data, but the name will be UsersNamecarro.txt if you gave following select * from carro where id_cor<4 INTO OUTFILE 'C:\Users\Name\carro.txt';

So just write .....OUTFILE 'C:\carro.txt' to get file called carro.txt in data folder

Eric Stein
  • 13,209
  • 3
  • 37
  • 52
Naren
  • 1
0

+1 to @michael-berkowski and the answer i needed for windows was (as he stated)

MariaDB's data directory

to be specific, mine was in the data folder for database named "mydatabase"

C:\Program Files\MariaDB 10.3\data\mydatabase

WEBjuju
  • 5,797
  • 4
  • 27
  • 36