0
SELECT *
FROM contacts
INTO OUTFILE 'contacts.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

I am running this query in sequel pro to dump a table in csv file. But it is giving me the error

"The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

As far as i know, there is no my.cnf file in mac os sierra. it justs runs with the default value. Can you please suggest me how to overcome this problem?

sad_man
  • 25
  • 5

2 Answers2

1

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 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 the table contacts into a CSV file

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

mysql>
russian_spy
  • 6,465
  • 4
  • 30
  • 26
0

MySQL Export To Dump Project Folder (CMD command)

By default, there’s no my.cnf file in OS X, and MySQL just uses the default values. To set up your own my.cnf, you could just create a file straight in /etc.

An example of the configuration file can be found here >> /usr/local/mysql/support-files/