How should I tackle --secure-file-priv in MySQL?

Question

I am learning MySQL and tried using a LOAD DATA clause. When I used it as below:

LOAD DATA INFILE "text.txt" INTO table mytable;

I got the following error:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

How do I tackle this error?

I have checked another question on the same error message, but still can’t find a solution.

I am using MySQL 5.6

Answer 1

If the file is local to your machine use the LOCAL in your command

LOAD DATA LOCAL INFILE "text.txt" INTO table mytable;

Answer 2

Here is what worked for me in Windows 7 to disable secure-file-priv (Option #2 from vhu's answer):

1. Stop the MySQL server service by going into services.msc.
2. Go to C:\ProgramData\MySQL\MySQL Server 5.6 (ProgramData was a hidden folder in my case).
3. Open the my.ini file in Notepad.
4. Search for 'secure-file-priv'.
5. Comment the line out by adding '#' at the start of the line.
6. Save the file.
7. Start the MySQL server service by going into services.msc.

For MySQL Server 5.7.16 and above, please refer to dbc's comment below.

Answer 3

On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. To disable it you need to add secure-file-priv = "" to your my.cnf file under the mysqld config group. eg:-

[mysqld]
secure-file-priv = ""

Answer 4

The thing that worked for me:

1. Put your file inside of the folder specified in secure-file-priv.

To find that type:

mysql> show variables like "secure_file_priv";

2. Check if you have local_infile = 1.

Do that typing:

mysql> show variables like "local_infile";

If you get:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

Then set it to one typing:

mysql> set global local_infile = 1;

3. Specify the full path for your file. In my case:

mysql> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.txt" into table test;