Apple phased out MySQL in favor of PostgreSQL. There are valid reasons why we might prefer MySQL, such as compatibility with legacy code.
Here are my instructions on how to install MySQL as a permanent service on macOS. These instructions work with or without macOS Server installed. For the most part, installation is straightforward, but read this carefully because there are big gotchas where it comes to file permissions.
I’ve tested these instructions several times on macOS 10.11 El Capitan and 10.10 Yosemite. I think these instructions will probably work on 10.8 Mountain Lion and 10.9 Mavericks.
Backup your MySQL databases.
You want to back up your MySQL databases before doing a macOS upgrade. The easiest way is to create a mysqldump file. Databases can also be restored from data files, but this is harder.
$ mysqldump -uroot --all-databases > ~/Desktop/dump_all.sql
Install or upgrade macOS.
Download Xcode from the App store and install the command line tools.
Xcode is a dependency for Homebrew, which uses Xcode’s gcc
compiler to compile everything from source. Once you have Xcode installed, you need to install the command line tools for the gcc
compiler to work. From Terminal:
$ xcode-select --install
Check the systemwide PATH
variable.
Open a Terminal window:
$ cat /etc/paths
Make sure that /usr/local/bin
occurs before /usr/bin.
If they don’t, then you need to change this order. Edit /etc/paths
using vi
or your favorite text editor. I love and use TextWrangler. Close your Terminal window and open a new Terminal window for this change to take effect.
Obtain Homebrew.
Homebrew is a great package manager for macOS that installs everything in /usr/local/bin
and does not require sudo.
It then symlinks to the expected locations so that the packages can find one another. Because the packages are centralized in the Cellar, they are easily updated and removed.
$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Fix any problems that Homebrew detects.
$ brew update $ brew doctor
Follow brew doctor
‘s instructions. brew doctor
usually complains about Xcode. If I’m guessing the error correctly, here is the solution that brew doctor will suggest (assuming macOS 10.10 – note the version since this affects what you will type into Terminal):
$ cd /Applications/Xcode.app/Contents/Developer/Toolchains/ $ sudo ln -s XcodeDefault.xctoolchain OSX10.10.xctoolchain # note the version (10.10) and modify accordingly.
Run brew doctor
again. Continue to follow brew doctor
‘s instructions until it tells you that “Your system is ready to brew.”
Install MySQL.
$ brew install mysql # ignore homebrew's instructions to symlink the .plist file!
Homebrew has just installed MySQL to run under the current user, which is not what we want for our server, but we will get to that in a moment. First, we will test the mysqld
service to see if it launches. In newer versions of MySQL, the command to launch the service is $ mysql.server start
.
$ mysql.server start $ mysql.server stop
Modify the .plist file to improve logging.
Using vi
or your favorite text editor, modify the .plist file.
$ vi /usr/local/opt/mysql/homebrew.mxcl.mysql.plist # use vi or your editor of choice
Add these lines within the <dict>
block:
<key>StandardErrorPath</key> <string>/var/log/homebrew.mxcl.mysql.err</string> <key>StandardOutPath</key> <string>/var/log/homebrew.mxcl.mysql.log</string>
Set up the MySQL service to launch at boot time as part of a server.
Homebrew’s instructions, which I asked you to ignore, would have installed a LaunchAgent for your current user account, so that the mysqld
service would start whenever you logged in. This would work great for a personal development machine, but it’s not not ideal for a server.
For a server, what we want is for mysqld
to start up at boot time by the root
account. So, we need to make two changes:
- The .plist must link into
/Library/LaunchDaemons
and have the appropriate permissions to be launched byroot
. - The mysql database files in
/usr/local/var/mysql
must all be owned by user_mysql
. You might wonder why the owner must be_mysql
, since the server starts up asroot
. This is because whenevermysqld
detects that it is being run asroot
, the process steps down to user_mysql
as a security measure. This is typical behavior for services. The Apache service, for example, starts up as root at boot time but steps down to user_www
.
Open a Terminal window, and enter:
$ sudo ln -sfv /usr/local/opt/mysql/*.plist /Library/LaunchDaemons $ sudo chown root:wheel /usr/local/opt/mysql/*.plist $ sudo chmod 600 /usr/local/opt/mysql/*.plist $ sudo chown -R _mysql:wheel /usr/local/var/mysql
Important: Now that mysql belongs to root
, do not run the $ mysql.server
command again as a regular user! Don’t do it. This will result in an aborted launch with permissions errors. You will need to delete the resulting .pid files in /usr/local/var/mysql
before mysqld
will be able to run again, even as root
. Again, don’t do it.
Verify that the MySQL service starts up at boot time.
We will reboot the server machine. At boot time, launchctl should load our modified .plist and run mysqld
. We will then run the mysql
command from Terminal to interface with the mysqld
service and verify that it’s running.
$ sudo shutdown -r now
After the machine restarts, re-enter Terminal and type:
$ mysql -uroot
If the mysql
command fails, then we know that either the LaunchDaemon didn’t kick in, or that mysqld
failed to start successfully. Again, if this happens it is usually because of a permissions issue. To troubleshoot, check the log files at:
/var/log/homebrew.mxcl.mysql.out /var/log/homebrew.mxcl.mysql.err /usr/local/var/mysql/*.err
Restore your MySQL databases.
$ mysql -uroot < mydumpfile.sql $ mysql_upgrade
A full dump from a recent version of MySQL should successfully restore all databases plus the privilege table, which controls users. If there are problems with the privilege table after restoring, then edit out the privilege table from your mysql dump file, and re-create your users manually using a tool like phpmyadmin
.
I hope this post helped someone. Happy monkeying!