禁用安全特权以在MySQL上加载数据 [英] Disable secure priv for data loading on MySQL

问题描述

我在Windows 10计算机上运行MySQL 5.7.我已经阅读了有关此主题的所有SO线程，但仍然没有弄清楚如何加载我的数据并克服此错误:

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:

Error Code: 1290. The MySQL server is running with the --secure-file-priv 
option so it cannot execute this statement

我有1)检查设置以将其更改为可以从保存数据集的目录中加载的内容，2)以管理员身份打开MySQL并检查了命令行，并确认安全文件确实可以确实指向我的目录，3)并在init文件中确认它指向包含我的文件的正确目录.我尝试更改数据集的位置，以便将其放置在新文件夹中，并确认已使用上述方法将其移动到该文件夹​​中，但仍然无法正常工作.

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.

任何人都将受到欢迎，谢谢.

Any and all help would be welcome, thank you.

推荐答案

我无法重现该问题.

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

更改文件:/etc/mysql/my.cnf

[mysqld]
.
.
.
secure_file_priv=/var/lib/mysql-files/
.
.
.

重新启动MySQL.

Restart 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

请参见 6.1.4服务器系统变量: :secure_file_priv

这篇关于禁用安全特权以在MySQL上加载数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！