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

When running MySQL8.0.18 on Mac OS X 10.14.6 Mojave, I tried executing this command:

mysql> SELECT ... INTO OUTFILE file;

I then ran into this error:

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

When I ran this:

mysql> SHOW VARIABLES LIKE 'secure_file_priv';

I got this:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

So I thinking that I just need to change the value assigned to secure_file_priv, I did this:

mysql> set @@secure_file_priv = '';

Then I got this:

ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

This took awhile to find out how to change secure_file_priv but this is what I did. You can set this variable for the server with this file ~/.my.cnf

Go to Terminal and then:

$vi ~/.my.cnf

Then enter this (copy and paste¹ ²)

[mysqld]
secure_file_priv               = ''

You can change the directory to be any directory that you want MySQL to write files to.

Press esc to get out of Editor Mode, then :, and then wq to save and quit vi.

Now QUIT out of your mysql client and restart the MySQL server

$sudo /usr/local/mysql/support-files/mysql.server restart

When you go back into your mysql client, you will see this after running mysql> SHOW VARIABLES LIKE ‘secure_file_priv’;

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+

Once you try mysql> SELECT … INTO OUTFILE file; this should write a new file for you³.

[1] If you don’t put [mysqld] into your .my.cnf file, you might get this:

mysql: [ERROR] Found option without preceding group in config file /Users/username/.my.cnf at line 1.
mysql: [ERROR] Fatal error in defaults handling. Program aborted!

[2] I tried putting this:

[mysqld]
secure_file_priv=“”

I then QUIT the mysql client, restarted the mysql server, and then relaunched the client. When I did SHOW VARIABLES LIKE ‘secure_file_priv’; it still showed NULL.

I found this article https://geodatawrangler.lazym8.com/blog/2017/02/16/secure-file-priv

which showed that there is some spacing between the variable and the expression. After doing just copying and pasting, it worked.

[3] You may run into this issue when trying to mysql> SELECT … INTO OUTFILE file

ERROR 1 (HY000): Can't create/write to file '/path/to/file' (OS errno 13 - Permission denied)

You will just need to change the permission of the directory where you want MySQL to write files to with Terminal:

chmod 1777 /path/to/file