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

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

提问by Mohit Bhasi

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

我正在学习 MySQL 并尝试使用LOAD DATA子句。当我如下使用它时：

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

回答by vhu

It's working as intended. Your MySQL server has been started with --secure-file-privoption 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";to see the directory that has been configured.

您可以使用SHOW VARIABLES LIKE "secure_file_priv";查看已配置的目录。

You have two options:

您有两个选择：

1. Move your file to the directory specified by secure-file-priv.
2. Disable 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.

1. 将您的文件移动到指定的目录secure-file-priv。
2. 禁用secure-file-priv。这必须从启动中删除，不能动态修改。为此，请检查您的 MySQL 启动参数（取决于平台）和 my.ini。

回答by Staza

I had the same issue. I finally solved using the LOCALoption in the command

我遇到过同样的问题。我终于使用LOCAL命令中的选项解决了

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

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

You can find more info here http://dev.mysql.com/doc/refman/5.7/en/load-data.html

你可以在这里找到更多信息http://dev.mysql.com/doc/refman/5.7/en/load-data.html

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

如果指定了 LOCAL，则该文件由客户端主机上的客户端程序读取并发送到服务器。该文件可以作为完整路径名指定其确切位置。如果作为相对路径名给出，则该名称将相对于启动客户端程序的目录进行解释。

回答by Mustafa

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:-

在 Ubuntu 14 和 Mysql 5.5.53 上，这个设置似乎是默认启用的。要禁用它，您需要将其添加secure-file-priv = ""到 mysqld 配置组下的 my.cnf 文件中。例如：-

[mysqld]
secure-file-priv = ""

回答by Carlos Med

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

我正在 Debian 上使用 MySQL5.7.11，让我看到目录的命令是：

mysql> SELECT @@global.secure_file_priv;

回答by Janaaaa

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.
2. Go to C:\ProgramData\MySQL\MySQL Server 5.6(ProgramDatawas a hidden folder in my case).
3. Open the my.inifile 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.

1. 进入services.msc.
2. 转到C:\ProgramData\MySQL\MySQL Server 5.6（ProgramData在我的情况下是一个隐藏文件夹）。
3. my.ini在记事本中打开文件。
4. 搜索“安全文件隐私”。
5. 通过在行首添加“#”来注释该行。对于 MySQL Server 5.7.16 及更高版本，注释不起作用。您必须将其设置为像这样的空字符串-secure-file-priv=""
6. 保存文件。
7. 进入services.msc.

回答by garyrgilbert

If the file is local to your machine use the LOCALin your command

如果文件是您机器的本地文件，请在您的命令中使用LOCAL

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

回答by wolfsshield

@vhu

@vhu

I did the SHOW VARIABLES LIKE "secure_file_priv";and it returned C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\so when I plugged that in, it still didn't work.

我做了SHOW VARIABLES LIKE "secure_file_priv";然后它又回来了，C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\所以当我插入它时，它仍然没有工作。

When I went to the my.ini file directly I discovered that the path is formatted a bit differently: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads

当我直接进入 my.ini 文件时，我发现路径的格式有点不同： C:/ProgramData/MySQL/MySQL Server 8.0/Uploads

Then when I ran it with that, it worked. The only difference being the direction of the slashes.

然后当我用它运行它时，它起作用了。唯一的区别是斜线的方向。

回答by Vinícius Souza

The thing that worked for me:

对我有用的东西：

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

1. 将您的文件放在secure-file-priv.

To find that type:

要找到该类型：

mysql> show variables like "secure_file_priv";

mysql> 显示变量，如“secure_file_priv”；

1. Check if you have local_infile = 1.

2. 检查您是否有local_infile = 1.

Do that typing:

这样做打字：

mysql> show variables like "local_infile";

mysql> 显示变量，如“local_infile”；

If you get:

如果你得到：

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

Then set it to one typing:

然后将其设置为一种类型：

mysql> set global local_infile = 1;

mysql> 设置全局 local_infile = 1;

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

3. 指定文件的完整路径。就我而言：

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

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

回答by Rsc Rsc

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'.

我对“secure-file-priv”有同样的问题。.ini 文件中的注释不起作用，在“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.

最后，正如 dbc 所建议的，使 'secure-file-priv' 等于一个空字符串是可行的。因此，如果有人在尝试上述答案后遇到困难，希望这样做会有所帮助。

回答by Nelles

This thread has been viewed 522ktimes at the time of this post. Honestly when did MySQL become our over protective unreasonable mom? What a time consuming attempt at security - which really only serves to shackle us!

在发布此帖子时，该主题已被查看522k次。老实说，MySQL 什么时候成为我们过度保护不合理的妈妈了？多么耗时的安全尝试 - 这实际上只会束缚我们！

After many searches and many attempts everything failed.
My solution:

经过多次搜索和多次尝试，一切都失败了。
我的解决方案：

1. Import the .csv file via PhpMyAdmin import on older box (if large do at cmd line)
2. generate a .sql file
3. download .sql file
4. import .sql file via MySQL work bench

1. 在旧的机器上通过 PhpMyAdmin 导入导入 .csv 文件（如果在 cmd 行做大）
2. 生成一个 .sql 文件
3. 下载 .sql 文件
4. 通过 MySQL 工作台导入 .sql 文件