禁用安全 priv 以在 MySQL 上加载数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37596163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Disable secure priv for data loading on MySQL
提问by dataelephant
I'm running MySQL 5.7 on a Windows 10 machine. I've read through all the SO threads on this topic and still haven't figured out how to get my data to load and get past this error:
我在 Windows 10 机器上运行 MySQL 5.7。我已经阅读了关于这个主题的所有 SO 线程，但仍然没有想出如何让我的数据加载并克服这个错误：
Error Code: 1290. The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement
I have 1) checked the settings to change them to be able to load from the directory in which I've saved my dataset, 2) opened up MySQL as administrator and checked the command line and have confirmed that the secure file does indeed point to my directory, 3) and confirmed in the init file that it's pointing to the correct directory containing my file. I tried changing the location of the dataset so it would be in a new folder and confirmed it had been moved there with the above methods, and it still did not work.
我已经 1) 检查了设置以更改它们以便能够从我保存数据集的目录加载，2) 以管理员身份打开 MySQL 并检查命令行并确认安全文件确实指向我的目录，3) 并在 init 文件中确认它指向包含我的文件的正确目录。我尝试更改数据集的位置，使其位于一个新文件夹中，并确认已使用上述方法将其移动到那里，但仍然无法正常工作。
Any and all help would be welcome, thank you.
欢迎任何和所有帮助，谢谢。
采纳答案by wchiquito
I can't reproduce the problem.
我无法重现该问题。
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13 |
+-----------+
1 row in set (0,00 sec)
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0,00 sec)
-- USE ...
mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
-> INTO TABLE `test_files`
-> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement
Change file: /etc/mysql/my.cnf
更改文件： /etc/mysql/my.cnf
[mysqld]
.
.
.
secure_file_priv=/var/lib/mysql-files/
.
.
.
Restart MySQL.
重启 MySQL。
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/ |
+---------------------------+
1 row in set (0,00 sec)
mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
-> INTO TABLE `test_files`
-> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0,00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
回答by ShQ
On MACOSX add .my.cnf to your home directory with content:
在 MACOSX 上，将 .my.cnf 添加到您的主目录，其中包含以下内容：
[mysqld_safe]
[mysqld]
secure_file_priv=""
回答by Galuoises
This works in MacOs Sierra 10.12.6:
这适用于 MacOs Sierra 10.12.6：
use the command
使用命令
mysql --help | more
and look for a line where it is written:
并寻找一行写着：
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf
~/.my.cnf
In my case I tried to create a my.cnf in the home directory but it did not work. The only solution I found is to create the file in the folder etc with
就我而言，我尝试在主目录中创建一个 my.cnf，但没有成功。我找到的唯一解决方案是在文件夹等中创建文件
sudo vim /etc/my.cnf
and put inside it
并放入其中
[mysqld_safe]
[mysqld]
secure-file-priv = ""
Then you can check that everything works with
然后你可以检查一切是否正常
select @@GLOBAL.secure_file_priv;
inside mysql and check that the value is empty and different from NULL.
在 mysql 中并检查该值是否为空且与 NULL 不同。
Finally save files in the directory /tmp and then move them in the directory you want. Alternatively (but possibly unsafe) use
最后将文件保存在目录/tmp 中，然后将它们移动到您想要的目录中。或者（但可能不安全）使用
chmod 1777 dir
where dir is the name of the directory in which you are writing the files.
其中 dir 是您写入文件的目录的名称。
回答by Morey
Note that under Windows setting the 'secure_file_priv' to a different path or disabling it altogether by setting it to:
请注意，在 Windows 下将“secure_file_priv”设置为不同的路径或通过将其设置为完全禁用它：
secure_file_priv=""
may not work if the MySQL service is running on a low-privilege account (default 5.7 installation). You can change that by selecting the "Local System account" in the Services under Properties -> Log On.
如果 MySQL 服务在低权限帐户上运行（默认 5.7 安装），则可能无法工作。您可以通过在“属性”->“登录”下的“服务”中选择“本地系统帐户”来更改它。
回答by MAS9000
- Check OS privileges on directory you are importing from.
- When trying to import your data via "CVS using LOAD DATA" select use local option.
- 检查您要从中导入的目录的操作系统权限。
- 当尝试通过“CVS using LOAD DATA”导入数据时，选择使用本地选项。
