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.
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.
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/uninstall)"
Next I followed the one-step instructions at brew.sh
/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 roll1 with MySQL. Or was I?
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:
create database sailwx_info_shiptracks;
At terminal prompt:
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.
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!
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.
The Solution
I found the secret! Just ask mysql where it was looking for my.cnf.
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:
mysql.server restart
In mysql:
mysql> select * from reportSources into outfile '/Users/paulmccombs/reportSources.txt';
Query OK, 12 rows affected (0.04 sec)
Success!!!
1 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.