问题描述
我正在尝试使用脚本中的以下代码将 MySQL 脚本的结果写入文本文件.
SELECT p.title, p.content, c.name FROM post pLEFT JOIN category c ON p.category_id=c.idINTO OUTFILE 'D:\MySql\mysqlTest.txt';
但是,我得到以下内容
ERROR 1290 (HY000): MySQL 服务器正在使用 --secure-file-priv 选项运行,因此它无法执行此语句
我该如何解决这个问题?
Ubuntu 16.04 (EASY):找出你可以写的地方
mysql>选择@@GLOBAL.secure_file_priv;+----------------------------+|@@GLOBAL.secure_file_priv |+----------------------------+|/var/lib/mysql-files/|+----------------------------+1 行(0.00 秒)
然后,就在那里写
mysql>SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';查询正常,992931 行受影响(1.65 秒)mysql>
Mac OSX:通过 MAMP 安装 Mysql
找出你可以写的地方
mysql>选择@@GLOBAL.secure_file_priv;+----------------------------+|@@GLOBAL.secure_file_priv |+----------------------------+|空 |+----------------------------+1 行(0.00 秒)
NULL 意味着你被搞砸了,所以你必须创建文件~/.my.cnf"
为通过 MAMP 安装的 MySQL 启用读/写(在 Mac 上):
- 打开MAMP"使用聚光灯
- 点击停止服务器"
编辑 ~/.my.cnf(使用 vi 或您喜欢的编辑器)并添加以下几行:
$ vi ~/.my.cnf
[mysqld_safe][mysqld]secure_file_priv="/Users/russian_spy/"
- 点击启动服务器"(在 MAMP 窗口中)
现在检查它是否有效:
一个.启动mysql(默认MAMP用户是root,密码也是root)
$/Applications/MAMP/Library/bin/mysql -u root -p
B.在mysql中查看白名单路径
mysql>选择@@GLOBAL.secure_file_priv;+----------------------------+|@@GLOBAL.secure_file_priv |+----------------------------+|/用户/russian_spy/|+----------------------------+1 行(0.00 秒)
c.最后,通过将表 train
导出到 CSV 文件进行测试
mysql>SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';查询正常,992931 行受影响(1.65 秒)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';
However, I am getting the following
How do I solve this?
Ubuntu 16.04 (EASY): Find out where you are allowed to write
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
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"
Enable read/write for MySQL installed via MAMP (on Mac):
- open "MAMP" use spotlight
- click "Stop Servers"
edit ~/.my.cnf (using vi or your favorite editor) and add the following lines:
$ vi ~/.my.cnf
- click "Start Servers" (in MAMP window)
Now check if it works:
a. start mysql (default MAMP user is root, password is also root)
$ /Applications/MAMP/Library/bin/mysql -u root -p
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. 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 选项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!