我应该如何处理MySQL中的--secure-file-priv？

本文翻译自：How should I tackle --secure-file-priv in MySQL?

I am learning MySQL and tried using a LOAD DATA clause. 我正在学习MySQL，并尝试使用LOAD DATA子句。 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 MySQL服务器正在使用--secure-file-priv选项运行，因此它无法执行此语句

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 我正在使用MySQL 5.6

---

#1楼

参考：https://stackoom.com/question/2DMVq/我应该如何处理MySQL中的-secure-file-priv

---

#2楼

It's working as intended. 它按预期工作。 Your MySQL server has been started with --secure-file-priv option which basically limits from which directories you can load files using LOAD DATA INFILE . 您的MySQL服务器已使用--secure-file-priv选项启动， 该选项基本上限制了您可以使用LOAD DATA INFILE从哪个目录加载文件。

You may use SHOW VARIABLES LIKE "secure_file_priv"; 您可以使用SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured. 查看已配置的目录。

You have two options: 您有两种选择：

1. Move your file to the directory specified by secure-file-priv . 将文件移动到secure-file-priv指定的目录。
2. Disable secure-file-priv . 禁用secure-file-priv 。 This must be removed from startup and cannot be modified dynamically. 必须从启动中将其删除，并且不能动态修改。 To do this check your MySQL start up parameters (depending on platform) and my.ini. 为此，请检查您的MySQL启动参数（取决于平台）和my.ini。

---

#3楼

我正在Debian上使用MySQL5.7.11，对我来说查看目录的命令是：

mysql> SELECT @@global.secure_file_priv;

---

#4楼

Here is what worked for me in Windows 7 to disable secure-file-priv (Option #2 from vhu's answer ): 这是我在Windows 7中禁用secure-file-priv （从vhu的答案中选择了选项2）：

1. Stop the MySQL server service by going into services.msc . 通过进入services.msc停止MySQL服务器服务。
2. Go to C:\\ProgramData\\MySQL\\MySQL Server 5.6 ( ProgramData was a hidden folder in my case). 转到C:\\ProgramData\\MySQL\\MySQL Server 5.6 （对于我而言， ProgramData是隐藏的文件夹）。
3. Open the my.ini file in Notepad. 在记事本中打开my.ini文件。
4. Search for 'secure-file-priv'. 搜索“ 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. 对于MySQL Server 5.7.16及更高版本，注释将不起作用。 You have to set it to an empty string like this one - secure-file-priv="" 您必须将其设置为像这样的空字符串secure-file-priv=""
6. Save the file. 保存文件。
7. Start the MySQL server service by going into services.msc . 通过进入services.msc启动MySQL服务器服务。

---

#5楼

I had the same problem with 'secure-file-priv'. 我对“ secure-file-priv”有同样的问题。 Commenting in the .ini file didn't work and neither did moving file in directory specified by 'secure-file-priv'. .ini文件中的注释不起作用，在“ secure-file-priv”指定的目录中移动文件也不起作用。

Finally, as dbc suggested, making 'secure-file-priv' equal to an empty string worked. 最后，如dbc所建议的那样，使“ secure-file-priv”等于一个空字符串是可行的。 So if anyone is stuck after trying answers above, hopefully doing this will help. 因此，如果任何人在尝试完上述答案后都陷入困境，希望这样做会有所帮助。

---

#6楼

On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. 在Ubuntu 14和Mysql 5.5.53上，默认情况下似乎启用了此设置。 To disable it you need to add secure-file-priv = "" to your my.cnf file under the mysqld config group. 要禁用它，您需要在mysqld配置组下的my.cnf文件中添加secure-file-priv = "" 。 eg:- 例如：-

[mysqld]
secure-file-priv = ""