# MySQL: What in the Hell is the --secure-file-priv option?: Original Post - 2017-02-16

## Task: Retrieve Data From a Dump File

A coworker had some MySQL dump files† but no longer had a MySQL installation. I thought it would be a quick job to convert the dump files into something more portable.

I could do a quick install of MySQL on my Mac, bring in the dump file and export it to a portable format. I'd just use Homebrew‡ to install it and I'd be rolling. Wait! Homebrew is hosed on my computer. So first a detour to get Homebrew working.

=> https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html † Dumping Data in SQL Format with mysqldump (web link)
=> https://brew.sh/ ‡ Homebrew: The Package Manager for Everywhere (web link)

## Installing MySQL on a Mac with Homebrew

The directory permissions were changed when I upgraded to OSX 10.11 (El Capitan) I hadn't had cause to figure out the fix.§ In the process, I also upgraded to macOS 10.12 (Sierra).

When Homebrew is working it's a dream. Open source software is installed just like it would be in Linux with apt¶  or yum.# After a bit of googling I found instructions to uninstall Homebrew.Δ

```bash
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/uninstall)"
```

Next I followed the one-step instructions from Homebrew.

```bash
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
```

and I was back in Homebrew action.

A not-to-quick

```
brew install mysql
mysql.server start
```

and I was ready to roll‖ with MySQL. Or was I?

=> https://digitizor.com/fix-homebrew-permissions-osx-el-capitan/ § How to fix permission issues on Homebrew in OS X El Capitan? (web link)
=> https://wiki.debian.org/Apt ¶ Advanced Package Tool (APT) (web link)
=> https://en.wikipedia.org/wiki/Yellowdog_Updater,_Modified # Yellowdog Updater Modified (YUM) (web link)
=> http://superuser.com/questions/203707/how-to-uninstall-homebrew-mac-os-x-package-manager Δ How to uninstall Homebrew? (web link)
‖ Note here I am starting MySQL manually because I don't intend to have the server running on my computer all of the time. Just for the one project. So there is no need for me to set it up to auto start.

## What in the Hell is the --secure-file-priv option?

With many thanks to Stack Overflow◊ the dump files were loaded into my brand new MySQL server:

In mysql:

```sql
create database sailwx_info_shiptracks;
```

At terminal prompt:

```bash
mysql -u root -h localhost sailwx_info_shiptracks < duwamish1.sql
mysql -u root -h localhost sailwx_info_shiptracks < duwamish2.sql
mysql -u root -h localhost sailwx_info_shiptracks < duwamish3.sql
```

After some poking around I found this "SELECT ... INTO OUTFILE"↓ syntax for MySQL. The more portable alternative to the dump files I'd started out with. I thought my job was complete, but then found an error I didn't understand.

```sql
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
```

=> http://stackoverflow.com/questions/17666249/how-to-import-an-sql-file-using-the-command-line-in-mysql ◊ Stack Overflow: How can I import an SQL file using the command line in MySQL? (web link)
=> https://dev.mysql.com/doc/refman/5.7/en/select-into.html ↓ MySQL: SELECT ... INTO Statement (web link)

## Oh, That's What it Means!

I googled my heart out and read many pieces of advice across several different forum postings. No one had a complete answer though, hence I've been inspired to share my struggle and triumphant solution. The first key to removing confusion was learning this a change at MySQL version 5.7.6.☞

>"secure_file_priv can be set to NULL to disable all import and export operations.
>
>The server checks the value of secure_file_priv at startup and writes a warning to the error log if the value is insecure. 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. If secure_file_priv is set to a nonexistent path, the server writes an error message to the error log and exits.
>
>Previously, the secure_file_priv system variable was empty by default. Now the default value is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table."

Many of the forum posts I found were written before this change and were horribly confusing. 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. To change secure_file_priv I first had to find the configuration file. According to my research it was named my.cnf and located in one of dozens of different places depending on who you believe. The forum posts I found were split between many versions of MySQL and various operating systems.

=> https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html ☞ Changes in MySQL 5.7.6 (2015-03-09, Milestone 16) (web link)

## The Solution

I found the secret!⌘ Just ask mysql where it was looking for my.cnf.

```bash
mysql --help | more
```

Which returned in part the following list of locations:

>"Default options are read from the following files in the given order:
>/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf"

The Homebrew install does not create a my.cnf by default, so I had to make one of my own. I decided to put the configuration file in my user directory and found this helpful tip⎈ on what to put in it.

After creating ~/.my.cnf with the following contents, I was ready to try creating my outfile again.

```
[mysqld]
secure_file_priv               = ''
```

At terminal prompt:

```bash
mysql.server restart
```

In mysql:

```sql
mysql> select * from reportSources into outfile '/Users/paulmccombs/reportSources.txt';
Query OK, 12 rows affected (0.04 sec)
```

Success!!!

=> http://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf ⌘ Stack Overflow: For homebrew mysql installs, where's my.cnf?
=> https://github.com/matomo-org/matomo/issues/9528 ⎈ GitHub: Mysql 5.7: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement: LOAD DATA INFILE #9528 (web link)

## Ported from the World Wide Web

This post was originally published on my web log.* If you would like to know more about why I've ported this, read my gemlog post about it.† The post has been reviewed and may include corrections and clarifications.

=> https://geodatawrangler.lazym8.com/ * Tales of Wrangling Geodata (web link)
=> gemini://sdf.org/paulmccombs/gemlog/introduction-to-geminispace.gmi † Paul's introduction to Geminispace

Ported 2026-10-15

© 2017, 2026 Paul McCombs 
This text is available under the Creative Commons Attribution-ShareAlike 4.0 License
=> https://en.wikipedia.org/wiki/Wikipedia:Text_of_the_Creative_Commons_Attribution-ShareAlike_4.0_International_License CC BY-SA 4.0