I found the secret! Just ask mysql where it was looking for my.cnf. The forum posts I found were split between many versions of MySQL and various operating systems. According to my research it was named my.cnf and located in one of dozens of different places depending on who you believe. To change secure_file_priv I first had to find the configuration file.
The default value of secure_file_priv for the Homebrew install of MySQL 5.7.17 was NULL, which prevented me from writing with outfile entirely. Many of the forum posts I found were written before this change and were horribly confusing. Stack Exchange network consists of 178 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Now the default value is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table.” Previously, the secure_file_priv system variable was empty by default. The server (instead of sending a resultset or a status) sends a special request (0xFB) together with the file name of the file the client is supposed to read and send. In our case this is LOAD DATA LOCAL INFILE datafile/data.txt.
If secure_file_priv is set to a nonexistent path, the server writes an error message to the error log and exits. The client sends the COMQUERY to execute the SQL command as it usually does. A non-NULL value is considered insecure if it is empty, or the value is the data directory or a subdirectory of it, or a directory that is accessible by all users. The server checks the value of secure_file_priv at startup and writes a warning to the error log if the value is insecure.
“ secure_file_priv can be set to NULL to disable all import and export operations. The first key to removing confusion was learning this a change at MySQL version 5.7.6 No one had a complete answer though, hence I’ve been inspired to share my struggle and triumphant solution. I googled my heart out and read many pieces of advice across several different forum postings. Select * from reportSources into outfile '/Users/paulmccombs/reportSources.txt' ERROR 1290 ( HY000 ): The MySQL server is running with the -secure-file-priv option so it cannot execute this statement Oh, That’s What it Means!