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

问题描述

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

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

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

但是，我得到以下信息

我该如何解决?

推荐答案

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)

然后，在那里写

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:通过MAMP安装Mysql

找出允许您写的地方

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

NULL表示您已不知所措，因此必须创建文件〜/.my.cnf"

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

为通过MAMP(在Mac上)安装的MySQL启用读/写:

1. 打开"MAMP"使用聚光灯
2. 点击停止服务器"

3. 编辑〜/.my.cnf(使用vi或您喜欢的编辑器)并添加以下几行:

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

$ vi ~/.my.cnf

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

1. 单击启动服务器"(在MAMP窗口中)

现在检查是否有效:

a.启动mysql(默认的MAMP用户是root，密码也是root)

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

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

b.在mysql中查看列入白名单的路径

b. in mysql look at the white-listed paths

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

c.最后，通过将表train导出到CSV文件中进行测试

c. Finally, test by exporting a table train into a CSV file

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

mysql>

这篇关于MySQL错误1290(HY000)--secure-file-priv选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！