Command line MySQL query export output to file not working in Windows

Question

Version of mysql is mysql Ver 14.14 Distrib 5.5.53, for Win64 (AMD64)

After running below commands, the output still goes to stdout

mysql -uroot -proot DBinstance

select * from tablename\G INTO OUTFILE 'c:\users\12345\Downloads\some_non_existingfile'

What is the problem with above command?

Edit:

I get below error after re-correcting the select query:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv opti on so it cannot execute this statement

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

Currently G:\..\mysql> dir have folders bin, data, lib, scripts & tmp.

Does MySQL have the permissions to write at that location? Also, as you seem to know, the file cannot already exist. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 4, 2018 at 13:32
i have amended my answer to show how to alter the ecure_file_priv setting — Clint
– Clint, Commented Aug 7, 2018 at 16:21

Clint · Accepted Answer · 2018-08-07 16:20:37Z

1

remove the \G flag.

select * from tablename INTO OUTFILE 'c:\users\12345\Downloads\some_non_existingfile'

you also need to make sure that The MySQL server is not running with the --secure-file-priv option

to allow for writing output to a folder add or amend your mysql config file (my.ini) by adding/amending the following line.

secure-file-priv = ""

or

secure-file-priv = "FOLDER-PATH-OF-YOUR-CHOICE"

then restart the mysql server using

net stop mysql
net start mysql

little info about the setting

If empty, the variable has no effect.

If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.

If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

edited Aug 7, 2018 at 16:20

answered Aug 4, 2018 at 14:56

Clint

1,0738 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

overexchange Over a year ago

How to verify secure file priv option?

Clint Over a year ago

@overexchange SHOW VARIABLES LIKE "secure_file_priv";

Clint Over a year ago

@overexchange setting most likely in your my.ini file.

overexchange Over a year ago

Can I set and unset this option to export the query output? Db is in production...

Clint Over a year ago

no, i believe it is a startup setting only. if you run SHOW VARIABLES LIKE "secure_file_priv"; you can see what folder you can output too, maybe you have access to the folder?

|

Collectives™ on Stack Overflow

Command line MySQL query export output to file not working in Windows

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related