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

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][1], but still can’t find a solution.

I am using MySQL 5.6

[1]:

[To see links please register here]

I had the same problem with 'secure-file-priv'. Commenting in the .ini file didn't work and neither did moving file in directory specified by 'secure-file-priv'.

Finally, as dbc suggested, making 'secure-file-priv' equal to an empty string worked. So if anyone is stuck after trying answers above, hopefully doing this will help.

I had all sorts of problems with this. I was changing my.cnf and all sorts of crazy things that other versions of this problem tried to show.

What worked for me:

The error I was getting

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

I was able to fix it by opening /usr/local/mysql/support-files/mysql.server and changing the following line:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" -- $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

to

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

I had this problem on windows 10. "--secure-file-priv in MySQL" To solve this I did the following.

1. In windows search (bottom left) I typed "powershell".
2. Right clicked on powershell and ran as admin.
3. Navigated to the server bin file. (C:\Program Files\MySQL\MySQL Server 5.6\bin);
4. Typed ./mysqld
5. Hit "enter"

The server started up as expected.

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 = ""

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

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

MySQL use this system variable to control where you can import you files

```
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
```

So problem is how to change system variables such as `secure_file_priv`.

1. shutdown `mysqld`
2. `sudo mysqld_safe --secure_file_priv=""`

now you may see like this:
```
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
```

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

[To see links please register here]

)):

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. For MySQL Server 5.7.16 and above, commenting won't work. You have to set it to an empty string like this one - `secure-file-priv=""`
6. Save the file.
7. Start the MySQL server service by going into `services.msc`.

If you're running on Ubuntu, you may also need to configure Apparmor to allow MySQL to write to your folder, e.g. here's my configuration:


Add this line to file /etc/apparmor.d/usr.sbin.mysqld :

```
/var/lib/mysql-files/* rw
```

Then add these 2 config lines to /etc/mysql/my.cnf sections:

```
[client]
loose-local-infile = 1

[mysqld]
secure-file-priv = ""
```

Here's my SQL:

```
select id from blahs into outfile '/var/lib/mysql-files/blahs';
```

It worked for me. Good luck!

I'm working on MySQL5.7.11 on Debian, the command that worked for me to see the directory is:

mysql> SELECT @@global.secure_file_priv;