MySQL output file location - accessing server files

Question

I am running the MySQL Workbench 6.2 CE on Windows7. I have an output table of 200,000 rows and I need to save it. I will have to repeat this task, so I would like to iterate the following code:

SELECT * FROM mytable
INTO OUTFILE 'mytable.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This saves my output but I cannot access it. It seems it is saved in the server database. I have the server installed in my desktop so I should be able to access it. Do you know how could I find the filepath to my saved csv files?

Edit1: I can save it manually in the Workbench GUI but I would like to find a code-based solution as I have to iterate this process many times.

Edit2: Putting all the file path doesn't work. It gives an error: Errcode: 13 - Permission denied

PS: Sorry if it is a basic question, I am new to MySQL.

May be you can specify the full path to the file yourself, e.g. ...OUTFILE 'c:\mytable.csv'... -- that way you will know exactly where the file is. — mustaccio
– mustaccio, Commented Dec 10, 2014 at 16:56
Hi @Mustaccio, I get an error when I specify the path myself: Error Code: 1. Can't create/write to file 'C:\Users\username\Desktop\mysqloutput\10122014outfiletest.csv' (Errcode: 13 - Permission denied) — User981636
– User981636, Commented Dec 10, 2014 at 17:27
Notice how the path I suggested is a bit different from yours? My guess would be that the account that runs the MySQL service must be able to write to the file. — mustaccio
– mustaccio, Commented Dec 10, 2014 at 17:40
When you are connected to MySQL, please run SELECT USER(),CURRENT_USER();. What is the output ? — RolandoMySQLDBA
– RolandoMySQLDBA, Commented Dec 10, 2014 at 18:02
@rolandomysqldba the output is root@localhost, is it correct? — User981636
– User981636, Commented Dec 11, 2014 at 17:44

András Váczi · Accepted Answer · 2017-12-22 20:48:32Z

1

If this is a one-time thing, you can do the following to export the result into csv file using MySQL workbench:

SELECT * FROM mytable

then the result will appear in the result table (area) all you need to do use export button in that area:

enter image description here

Otherwise, if you want to create output file you can do it by using a shared folder:

if its over a network //my_pc/folder/test.csv that both machines have read/write access
and if its under the same server use for example d:/test.csv

and save the file into any location you want.

edited Dec 22, 2017 at 20:48

András Váczi

31.8k13 gold badges103 silver badges152 bronze badges

answered Dec 10, 2014 at 17:28

Ahmad Abuhasna

2,7184 gold badges25 silver badges36 bronze badges

Add a comment |

a_vlad · Accepted Answer · 2016-11-18 12:44:28Z

By default (if it not disabled) MySQL will save file to same folder as database, in my case - sakila

SELECT * FROM actor
INTO OUTFILE 'mytable.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

You can also add restriction to my.ini file, like

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_secure-file-priv

if strict mode not enabled, You can use "any" path (any enabled by windows) for output file, but not use user folders or root folder for avoid permissions collisions. create something like c:/exchange

Stack Exchange Network

MySQL output file location - accessing server files

2 Answers 2

Your Answer

Hot Network Questions

MySQL output file location - accessing server files

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions