MySQL 错误 1290 (HY000) --secure-file-priv 选项

MySQL ERROR 1290 (HY000) --secure-file-priv option

提问by Adhil

I am trying to write the results of MySQL script to a text file using the following code in my script.

我正在尝试使用脚本中的以下代码将 MySQL 脚本的结果写入文本文件。

SELECT p.title, p.content, c.name FROM post p
LEFT JOIN category c ON p.category_id=c.id
INTO OUTFILE 'D:\MySql\mysqlTest.txt';

However, I am getting the following

但是，我得到以下信息

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

错误 1290 (HY000)：MySQL 服务器正在使用 --secure-file-priv 选项运行，因此无法执行此语句

How do I solve this?

我该如何解决这个问题？

回答by russian_spy

Ubuntu 16.04 (EASY): Find out where you are allowed to write

Ubuntu 16.04 (EASY)：找出你可以写的地方

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

Then, just write there

然后，就在那里写

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Mac OSX: Mysql installed via MAMP

Mac OSX：通过 MAMP 安装 Mysql

Find out where you are allowed to write

找出你被允许写作的地方

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

NULL means you're screwed so you have to create the file "~/.my.cnf"

NULL 意味着你被搞砸了，所以你必须创建文件“~/.my.cnf”

Enable read/write for MySQL installed via MAMP (on Mac):

为通过 MAMP 安装的 MySQL 启用读/写（在 Mac 上）：

1. open "MAMP" use spotlight
2. click "Stop Servers"

3. edit ~/.my.cnf (using vi or your favorite editor) and add the following lines:

   $ vi ~/.my.cnf

1. 打开“MAMP”使用聚光灯
2. 单击“停止服务器”

3. 编辑 ~/.my.cnf（使用 vi 或您喜欢的编辑器）并添加以下行：

   $ vi ~/.my.cnf

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"

1. click "Start Servers" (in MAMP window)

4. 单击“启动服务器”（在 MAMP 窗口中）

Now check if it works:

现在检查它是否有效：

a. start mysql (default MAMP user is root, password is also root)

一种。启动mysql（默认MAMP用户是root，密码也是root）

$ /Applications/MAMP/Library/bin/mysql -u root -p 

b. in mysql look at the white-listed paths

湾 在mysql中查看白名单路径

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /Users/russian_spy/          |
+---------------------------+
1 row in set (0.00 sec)

c. Finally, test by exporting a table traininto a CSV file

C。最后，通过将表导出train到 CSV 文件进行测试

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

回答by Kanchan Waikar

1. Edit the (/etc/my.cnf file for CentOS) or (my.ini file for Windows)
2. Add secure-file-priv = ""line at the end
3. Stop mysql service using systemctl stop mysqld
4. Restart it using systemctl start mysqld

1. 编辑（CentOS 的 /etc/my.cnf 文件）或（Windows 的 my.ini 文件）
2. secure-file-priv = ""在最后添加 一行
3. 停止 mysql 服务使用 systemctl stop mysqld
4. 使用重新启动它 systemctl start mysqld

It will now allow you to import and export the data.

它现在允许您导入和导出数据。

回答by zgormez

Replace "\"to "/"in your file path.

在您的文件路径中替换"\"为"/"。

Like this:

像这样：

INTO OUTFILE 'D:/MySql/mysqlTest.txt';

回答by Naruto

You cannot export data as it is configured in mysql config files. Open my.cnf config file and check.

您无法导出数据，因为它是在 mysql 配置文件中配置的。打开 my.cnf 配置文件并检查。

Quote from MySQL doc

引自 MySQL文档

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATAand SELECT ... INTO OUTFILEstatements and the LOAD_FILE()function. These operations are permitted only to users who have the FILEprivilege.

secure_file_privmay be set as follows:

• If empty, the variable has no effect.

• If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.

• If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

该变量用于限制数据导入导出操作的效果，例如LOAD DATAandSELECT ... INTO OUTFILE语句和LOAD_FILE()函数执行的操作。这些操作只允许有FILE权限的用户使用。

secure_file_priv可以设置如下：

• 如果为空，则该变量无效。

• 如果设置为目录的名称，服务器会将导入和导出操作限制为仅处理该目录中的文件。目录必须存在；服务器不会创建它。

• 如果设置为NULL，服务器将禁用导入和导出操作。从 MySQL 5.7.6 开始允许使用此值。

(An empty value is the default, or it can be explicitly specified in my.cnf as secure_file_priv="". A NULLvalue can be set with secure_file_priv=NULL.)

（空值是默认值，也可以在 my.cnf 中明确指定为secure_file_priv=""。NULL可以使用 设置值secure_file_priv=NULL。）

So, if you want to export data, then you need to comment this option and restart mysql server. Then you will be able to export.

所以，如果你想导出数据，那么你需要注释这个选项并重启mysql服务器。然后你就可以导出了。

回答by Krutikaa Jawanjal

FOR MAC OS, if installed via HOMEBREW:

对于 MAC 操作系统，如果通过 HOMEBREW 安装：

Edit my.cnf PATH: /usr/local/etc/my.cnf

编辑 my.cnf 路径：/usr/local/etc/my.cnf

COPY this:

复制这个：

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 0.0.0.0
secure-file-priv = ''

SAVE

节省

Explanation: Secure_file_prive = NULL -- Limit mysqld not allowed to import and export Secure_file_priv = '/tmp/' -- Limit mysqld import and export can only occur in /tmp/ directory Secure_file_priv = '' -- does not restrict the import of mysqld

说明：Secure_file_prive = NULL -- 限制mysqld不允许导入导出 Secure_file_priv = '/tmp/' -- 限制mysqld导入导出只能发生在/tmp/目录下 Secure_file_priv = '' -- 不限制mysqld导入

回答by erovere

This is the example of my SQL that worked perfectly in WAMP SERVER (windows):

这是我在 WAMP SERVER (windows) 中完美运行的 SQL 示例：

SELECT display_name,user_email,user_registered FROM test_wp_users
ORDER BY user_registered ASC
INTO OUTFILE 'C:/wamp64/tmp/usersbyemail.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

PS: Note that bars in path should be left-to-right to work perfectly in MYSQL.

PS：请注意，路径中的条应该从左到右才能在 MYSQL 中完美运行。

回答by meisaa

In my my.ini I only had

在我的 my.ini 我只有

# Secure File Priv.

and I tried to put:

我试着把：

# Secure File Priv.
secure-file-priv = ""

and

和

# Secure File Priv.
secure-file-priv = NULL

without making it work.

不让它工作。

I finally deleted the line and left alone:

我终于删除了该行并独自留下：

secure-file-priv = ""

Working correctly.

工作正常。

回答by koleS

Just create a file /etc/my.cnfwith the following content

只需创建一个/etc/my.cnf包含以下内容的文件

[mysqld]
secure_file_priv            = ''

You can use this oneliner:

你可以使用这个oneliner：

echo "[mysqld]\nsecure_file_priv\t\t= ''\n" | sudo tee /etc/my.cnf

And then restart mysql. If brew was used to install the mysql run the following command:

然后重启mysql。如果使用 brew 安装 mysql，请运行以下命令：

brew services restart mysql

回答by imoll

That's because secure_file_privis set to NULL

那是因为secure_file_priv设置为 NULL

mysql> show variables like secure_file_priv;

mysql> 显示变量如secure_file_priv;

| secure_file_priv | NULL  |

You must stop mysql server

你必须停止mysql服务器

shell>/usr/local/mysql/support-files/mysql.server stop

Then restart mysql with the option defining where you want your files to be written to, for example to /tmp

然后使用定义文件写入位置的选项重新启动 mysql，例如 /tmp

shell>/usr/local/mysql/support-files/mysql.server start --secure-file-priv=/tmp

Then in mysql terminal you should see where your files can now be written to

然后在 mysql 终端中，您应该看到现在可以将文件写入的位置

mysql> show variables like secure_file_priv;

mysql> 显示变量如secure_file_priv;

| secure_file_priv | /private/tmp/ |

On Mac you can find this folder by using Go To Folder /private/tmpin Finder

在 Mac 上，您可以使用/private/tmpFinder 中的“转到文件夹”来找到此文件夹