By skyforbes 
The other day I had to export some data from a client database and have received this error on his Windows 7 machine.
These are the commands I executed.
use my_app_db;
SELECT * FROM customers WHERE id > 1000 ORDER BY `last_name` INTO OUTFILE ‘/customers.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’;
ERROR 1290 (HY000): The MySQL server is running with the — secure-file-priv option so it cannot execute this statement
It seems after a certain version (5.5?) there’s a setting the MySQL ini file called secure-file-priv which restricts where the exported file can be saved.
secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”
I’ve tried editing the my.ini file from C:/ProgramData/MySQL/MySQL Server 5.7/ but the file was locked. I stopped the mysql service and the mysqld program from services and task manager respectively.
I still wasn’t able to comment out the above mentioned line. Since this was one time export I didn’t want to spend too much time on this.
The solution
Save the file in white listed folder. Make sure you use the same slashes.
Connect to the mysql database and enter the root password
“C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql” -uroot -p
use my_app_db;
SELECT * FROM customers WHERE id > 1000 ORDER BY `last_name` INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/customers.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘\n’;
The code above will export the data but won’t export the heading columns which is weird.
To export the columns you need to execute the following command and then merge the two files using your favourite text editor. You will need to copy the contents of customers_heading_cols.csv and insert it as first line in customers.csv. You may have to remove a trailing comma though.
SELECT column_name FROM information_schema.columns WHERE table_schema = ‘my_app_db’ AND table_name = ‘customers’ INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/customers_heading_cols.csv’ FIELDS TERMINATED BY ‘’ OPTIONALLY ENCLOSED BY ‘”’ LINES TERMINATED BY ‘,’;
Next: open customers.csv in excel by double clicking on it and all should be cool.
Related
http://stackoverflow.com/questions/31951468/error-code-1290-the-mysql-server-is-running-with-the-secure-file-priv-option
http://stackoverflow.com/questions/23415610/turn-off-load-data-infile-or-into-output
Slavi is Entrepreneur, Developer running http://Orbisius.com & http://qSandbox.com do follow me on Twitter or on Medium.