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

@ivanvermeyen, thank you for writing this up. It has helped me learn a lot about managing multiple versions of MySQL on a Mac. However, using Homebrew to manage more than one MySQL installation needs some clarification. My observations below apply only to installing MySQL for the first time using Homebrew.

Because installing MySQL with Homebrew is keg-only, symlinks must be configured manually by the user. On installation, we see this in Terminal:

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have mysql@5.7 first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

This means that Terminal will not recognize any command beginning with mysql until I have added it to PATH in .bash_profile. However, even when this is properly configured, I must update the path it points to when I want to use a different version. While this may seem obvious, those unfamiliar with how .bash_profile works might be confused.

For example, let's say I do the following steps (remember, this is my first time installing MySQL)

1. brew install mysql@5.7
2. I add 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH" to .bash_profile so I can use mysql in Terminal later
3. Following your steps above, I open homebrew.mxcl.mysql@5.7.plist inside of /usr/local/var/Cellar/mysql@5.7/5.7.25/ and update the two instances of /usr/local/var/mysql to /usr/local/var/mysql57
4. I then rename /usr/local/var/mysql to /usr/local/var/mysql57 so that the changes I made immediately above point to the correct directory
5. I incorporate the changes made to .bash_profile by firing source ~/.bash_profile in Terminal
6. And finally, I verify that all is well with mysql -V and see that version 5.7 is installed

Up to this point, it appears that everything is working as expected. However, if I fire mysql.server start, I get this error:

./usr/local/Cellar/mysql@5.7/5.7.25/bin/mysqld_safe: line 647: /usr/local/var/mysql/Somenames-MacBook-Air.local.err: No such file or directory
Logging to '/usr/local/var/mysql/Eleanors-MacBook-Air.local.err'.
/usr/local/Cellar/mysql@5.7/5.7.25/bin/mysqld_safe: line 144: /usr/local/var/mysql/Somenames-MacBook-Air.local.err: No such file or directory
/usr/local/Cellar/mysql@5.7/5.7.25/bin/mysqld_safe: line 198: /usr/local/var/mysql/Somenames-MacBook-Air.local.err: No such file or directory
/usr/local/Cellar/mysql@5.7/5.7.25/bin/mysqld_safe: line 906: /usr/local/var/mysql/Somenames-MacBook-Air.local.err: No such file or directory
/usr/local/Cellar/mysql@5.7/5.7.25/bin/mysqld_safe: line 144: /usr/local/var/mysql/Somenames-MacBook-Air.local.err: No such file or directory
 ERROR! The server quit without updating PID file (/usr/local/var/mysql/Somenames-MacBook-Air.local.pid).

Here you can see that MySQL is looking for files inside of the directory whose name I changed in Step 4. But since /usr/local/var/mysql/ no longer exists, it crashes.

Your statement that The basic idea here is that you need to install all MySQL versions one at a time and assign each its own data directory seems to be on point with regards to managing data with different versions.

However, the executable files (i.e. the mysql commands) contained within any given version of MySQL installed using Homebrew all point to /usr/local/var/mysql/.

I have yet to experiment with databases created by different versions but in my tests, if /usr/local/var/mysql/ does not exist and you are trying to use a specific version like 5.7, it will not work.

I agree with @samuraijane

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.

@ivanvermeyen I googled a lot about this issue and not found any working solution. I tried you solution, installed 5.7, switched to 5.7 and tried to login to mysql in the console mysql -uroot -p and got an error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'

Meanwhile, I'm using this tool: https://dbngin.com/ for MySQL 5.7. MySQL 8 is installed via brew

I use DBengine it works great you should try it

…

On Fri, 4 Oct 2019 at 20:08 Daiyrbek Artelov ***@***.***> wrote: @ivanvermeyen <https://github.com/ivanvermeyen> I googled a lot about this issue and not found any working solution. I tried you solution, installed 5.7, switched to 5.7 and tried to login to mysql in the console mysql -uroot -p and got an error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' I have some legacy projects which doesn't work properly on new MySQL 8. Would be fine to find a solution on how to have multiple mysql versions on macOS. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/c2dfb8ad55a4fb699c5913a09422c1d9?email_source=notifications&email_token=AEOI3VLCHT74HTV4RDR2HRDQM6BDJA5CNFSM4H5PR6FKYY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFZ45Q#gistcomment-3045848>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AEOI3VLSCMS3RN7MGDK6L43QM6BDJANCNFSM4H5PR6FA> .

@4unkur, @henrytirla Wow that looks like a very handy app!
Thanks for the tip!

You are welcome

…

On Sat, 5 Oct 2019 at 10:45 Ivan Vermeyen ***@***.***> wrote: @4unkur <https://github.com/4unkur>, @henrytirla <https://github.com/henrytirla> Wow that looks like a very handy app! Thanks for the tip! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/c2dfb8ad55a4fb699c5913a09422c1d9?email_source=notifications&email_token=AEOI3VM6ZTGR4OEKM7R3TH3QNBH3LA5CNFSM4H5PR6FKYY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFZ5WI#gistcomment-3046244>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AEOI3VMXJ7TR6OGR2XOHCLDQNBH3LANCNFSM4H5PR6FA> .

@4unkur @ivanvermeyen @henrytirla wow just installed DBngin - so easy!

You are welcome!

…

On Fri, 4 Sep 2020 at 07:36, bhu Boue vidya ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @4unkur <https://github.com/4unkur> @ivanvermeyen <https://github.com/ivanvermeyen> @henrytirla <https://github.com/henrytirla> wow just installed DBngin - so easy! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/c2dfb8ad55a4fb699c5913a09422c1d9#gistcomment-3441810>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEOI3VPCE4HLXJYE5IT4RWTSEB4GTANCNFSM4H5PR6FA> .

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 :)