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.

• mustaccio: 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.
• User981636: 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\mysqloutput10122014円outfiletest.csv' (Errcode: 13 - Permission denied)
• mustaccio: 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.
• RolandoMySQLDBA: When you are connected to MySQL, please run SELECT USER(),CURRENT_USER();. What is the output ?
• User981636: @rolandomysqldba the output is root@localhost, is it correct?

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

Answer 2

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';

default location

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