MySQL export INTO OUTFILE --secure-file-priv error when using set directory

Question

I am trying to run the following in a MySQL database:

SELECT * FROM mysql.db INTO OUTFILE "C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\db.csv" FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

I get an error:

 SQL Error (1290): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 

When I run the following:

 mysql> SELECT @@secure_file_priv; 

I get:

 +------------------------------------------------+ | @@secure_file_priv | +------------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ | +------------------------------------------------+ 

So why doesn't it export the file, although I use the specified location --secure-file-priv?

I am used to MSSQL and new to MySQL.

Answer 1

Argh. It was an insulting trait, my \ had to // s

So now my request is:

 SELECT * FROM mysql.db INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/db_pipe.csv" FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

Answer 2

It is important to use the path location specified in:

 mysql> SELECT @@secure_file_priv; 

If you use a custom path location, you will still get this error. As mentioned by Lateralus, remember to change the path to slashes.

Answer 3

It worked for me. It requires a double backslash, and if you are trying to use any tools to connect to mysql, just add escape characters.

 SELECT * INTO OUTFILE 'C:\\\\ProgramData\\\\MySQL\\\\MySQL Server 8.0\\\\Uploads\\\\employees.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM employees;