Multiple MySQL versions on MacOS with Homebrew

HOWTO.md

Update - 4 september 2020

Making multiple MySQL versions work with Homebrew was tricky to say the least. Fortunately there are 2 new easy ways that I learned of to achieve this.

DBngin app

As @4unkur and @henrytirla commented below, there is this extremely easy to use app called DBngin, which lets you setup multiple databases (not only MySQL) simultaneously using different ports:

https://dbngin.com/

Takeout

Another neat solution that launched last month (august 2020) is Takeout. It also supports many different databases running at the same time, but uses docker behind the scenes. So you will need to have docker installed.

It's meant to be paired with a tool like Laravel Valet, but I don't think it is an absolute requirement. It just runs the server on localhost, but inside a container. Your database data is stored on your machine, so you won't lose everything everytime you stop the server.

https://github.com/tightenco/takeout

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!

Contents

• 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 mysql@5.6
brew unlink mysql@5.6
brew uninstall -f mysql@5.6

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 mysql@5.7

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 mysql@5.7

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

cd /usr/local/Cellar/mysql@5.7/5.7.24

Here, open the file homebrew.mxcl.mysql@5.7.plist 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>homebrew.mxcl.mysql@5.7</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql@5.7/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 mysql@5.7

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 mysql@5.7
    brew services stop mysql@5.6
    brew unlink mysql mysql@5.7 mysql@5.6
    brew link --force --overwrite $1
    brew services start $1
}

alias mysql56="mysqlv mysql@5.6"
alias mysql57="mysqlv mysql@5.7"
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!

• Easily switch PHP versions in Laravel Valet
• Multiple MySQL Versions with Homebrew
• Change MySQL Data Directory in OSX

Thank you so much! I learnt a lot from this

Hey @samuraijane, thanks for the feedback!
I guess the brew link command is no longer doing the trick :o
I've been trying to solve the issues and the following seems to be working:

1. brew install mysql@5.7
2. rename /usr/local/var/mysql to /usr/local/var/mysql57
3. create a symlink: ln -s /usr/local/var/mysql57 /usr/local/var/mysql

No need to edit the .plist file now!
Now both the /usr/local/opt/mysql@5.7/bin/mysql.server start and brew services start mysql@5.7 commands work.

To run mysql in the terminal, we can create a symlink as well and add it to the .bash_profile file.

• ln -s /usr/local/opt/mysql@5.7 /usr/local/opt/mysql
• echo 'export PATH="/usr/local/opt/mysql/bin:$PATH"' >> ~/.bash_profile

You do need to remove the /usr/local/var/mysql symlink before installing another version.
And the symlinks can be overwritten when switching versions.

This workflow could easily be wrapped in a function.
I think it's somewhat easier than before.

function mysqlv() {
    service=$1

    if [ "$1" = "mysql@8.0" ]; then
        service="mysql"
    fi

    brew services stop mysql
    brew services stop mysql@5.7
    brew services stop mysql@5.6

    ln -sfn /usr/local/opt/$1 /usr/local/opt/mysql
    ln -sfn /usr/local/var/$1 /usr/local/var/mysql

    brew services start $service
}

alias mysql56="mysqlv mysql@5.6"
alias mysql57="mysqlv mysql@5.7"
alias mysql80="mysqlv mysql@8.0"

Installing a new version would look like this:

# Usage: mysqlinst mysql@5.7
function mysqlinst() {
    service=$1

    if [ "$1" = "mysql@8.0" ]; then
        service="mysql"
    fi

    brew services stop mysql
    brew services stop mysql@5.7
    brew services stop mysql@5.6

    if [ -L /usr/local/opt/mysql ]; then
        rm /usr/local/opt/mysql
    fi

    if [ -L /usr/local/var/mysql ]; then
        rm /usr/local/var/mysql
    fi

    brew install $service

    mv /usr/local/var/mysql /usr/local/var/$1

    ln -sfn /usr/local/opt/$1 /usr/local/opt/mysql
    ln -sfn /usr/local/var/$1 /usr/local/var/mysql

    brew services start $service
}

This seems to work for me...
The only thing I see wrong is when I run brew doctor, I get:

Error: /usr/local/opt/mysql@5.7 is not a valid keg

Can you verify if it works for you too?
Then I'll update the gist.

"ln -sfn $(realpath /usr/local/opt/$1) /usr/local/opt/mysql" replace "ln -sfn /usr/local/opt/$1 /usr/local/opt/mysql" solve this problem

Please tell me Guys, how can i do this on mac m1? I need 5.7 and 8 version on my M1. Unfortunately I don't know how to do this.

Thanks for the doc above it got me part of the way there. But I want to run both 5.7 and 8 at the same time. Now I am, here's how

https://1mikemakuch.com/?p=1457

Thanks for this. DBngin is awesome, however, since I started out with mysql@5.7 already running via Brew on 3306, in the end all I did was install mysql@8.0 via DBngin on 3307 and both work concurrently.

Thanks for this. DBngin is awesome, however, since I started out with mysql@5.7 already running via Brew on 3306, in the end all I did was install mysql@8.0 via DBngin on 3307 and both work concurrently.

This is awesome! Thank you :)