Multiple MySQL versions on MacOS with Homebrew

At the time of writing (december 2018), there aren’t any up-to-date and easy to apply guides on how to switch between different MySQL version on a Mac using Homebrew . Or at least, I didn’t find any.

So I picked up a few things here and there and finally managed to connect all the pieces of the puzzle. I hope this guide can help you and the future me. If anyone knows of a better way to accomplish this, I do hope they will share their insight :)

The basic idea here is that you need to install all MySQL versions one at a time and assign each its own data directory.

I am using Homebrew 1.8.5. (brew -v)

Let’s get started!

Cleanup
Install MySQL with Homebrew
Move the default data directory
Switch MySQL versions
Sequel Pro can not log in to MySQL 8.0 on localhost
Thanks!

Cleanup

Without Homebrew

If you already have MySQL installed without Homebrew, you should backup your databases and stop any running services. Do a quick search to find out how to do this with your setup.

With Homebrew

Find out which versions you have installed with Homebrew:

brew list

If you only have one MySQL version installed, you can proceed to move the default data directory.

If you were tinkering around and have multiple MySQL versions installed, the data directory will still be from the original installation after you start up a different version. So even if mysql -V tells you the correct version, when you login to the database it will still show the original version.

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24 Homebrew

Find out which version you have and uninstall all other versions.

brew services stop [email protected]
brew unlink [email protected]
brew uninstall -f [email protected]

I’m adding -f to also remove all minor versions that might be installed.
You can find these in /usr/local/Cellar/<mysql@version>.

Now you should only have one MySQL version installed.

Proceed to move the default data directory.

Install MySQL with Homebrew

If you don’t have MySQL installed or you wish to install an additional version, the procedure is simple.

Make sure that the directory /usr/local/var/mysql does not exist. It will be created when you install MySQL. If it does exist, you should read the cleanup chapter.

Install one MySQL version:

brew install [email protected]

Proceed to move the default data directory.

Move the default data directory

First, make sure that the MySQL version you want to move is not running. (I’m using 5.7 as an example)

brew services list
brew services stop [email protected]

Then, find the MySQL install directory in /usr/local/Cellar and go into the subdirectory of its latest minor update.

cd /usr/local/Cellar/[email protected]/5.7.24

Here, open the file [email protected] with a text editor. It looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>[email protected]</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/[email protected]/bin/mysqld_safe</string>
    <string>--datadir=/usr/local/var/mysql</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql</string>
</dict>
</plist>

Update both references to /usr/local/var/mysql to a unique directory, For example, /usr/local/var/mysql57.

You probably need to update this file every time you install an update of MySQL as each minor version has its own directory.

Next, go to /usr/local/var and you should find a directory called mysql. This contains all data of your installed database. Rename the directory to match the above reference (mysql57).

Done.

You can now start the service back up or switch version.

brew services start [email protected]

Switch MySQL versions

Once you have a few versions installed, you can create a little helper function and some aliases in your ~/.bash_profile or equivalent.

mysqlv() {
    brew services stop mysql
    brew services stop [email protected]
    brew services stop [email protected]
    brew unlink mysql [email protected] [email protected]
    brew link --force --overwrite $1
    brew services start $1
}

alias mysql56="mysqlv [email protected]"
alias mysql57="mysqlv [email protected]"
alias mysql80="mysqlv mysql"

Add or remove any versions that you need.

Sequel Pro can not log in to MySQL 8.0 on localhost

As mentioned on Stack Overflow , this is because Sequel Pro is not ready yet for a new kind of user login.

I got Sequel Pro to login by logging into MySQL from the console:

mysql -uroot

And then resetting the (empty) password for user root:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

If you want a password, put it between the last quotes obviously. :)

Thanks!

Thumbs up for the work of these people that put me on the right track!

rkasigi/HOWTO.md