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

by

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):

  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

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"
  1. 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>

More Related Contents:

  1. How to use IN & NOT IN same query [closed]
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. MySQL: Large VARCHAR vs. TEXT?
  4. Check overlap of date ranges in MySQL
  5. MySQL offset infinite rows
  6. Multiple-column foreign key in MySQL?
  7. What is the question mark’s significance in MySQL at “WHERE column = ?”?
  8. How do write IF ELSE statement in a MySQL query
  9. mysql – making a mechanism similar to Oracle’s seqences
  10. How do you uninstall MySQL from Mac OS X?
  11. Get the latest date from grouped MySQL data
  12. UUID performance in MySQL?
  13. MySQL: NULL vs “”
  14. MySQL update a joined table
  15. Optimal MySQL settings for queries that deliver large amounts of data?
  16. mysql configuration stops at “starting server”
  17. MySQL Error 1264: out of range value for column
  18. Like Case Sensitive in MySQL
  19. What is the best way to delete old rows from MySQL on a rolling basis?
  20. What are the known ways to store a tree structure in a relational DB? [closed]
  21. Illegal mix of collations error in MySql
  22. how to use DISTINCT ON with mysql using ActiveRecord
  23. use a variable for table name in mysql sproc
  24. What is the best way to do incremental backups in MySQL?
  25. WooCommerce: Finding the products in database
  26. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  27. Mysql performance on 6 million row table
  28. How can I “select *” from a table in MySQL but omit certain columns?
  29. MySQL Select Multiple VALUES
  30. Convert SQL Server query to MySQL [duplicate]

Leave a Comment