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
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
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
[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"
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>