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!
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.
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
-fto 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.
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.
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
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.
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. :)
Thumbs up for the work of these people that put me on the right track!