Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Multiple MySQL Versions with Homebrew

Multiple MySQL Versions for Development

Options included below:

  • Using Docker docker-compose
  • Using Homebrew brew

Using Docker (recommended)

This gist was originally created for Homebrew before the rise of Docker, yet it may be best to avoid installing mysql via brew any longer. Instead consider adding a barebones docker-compose.yml for each project and run docker-compose up to start each project's mysql service.

docker-compose.yml

version: "3.7"
services:

  db:
    image: mysql:5.6
    restart: always
    ports:
      - "3306:3306"
    volumes:
      - "db_data:/var/lib/mysql"

volumes:
  db_data: null

Please visit https://hub.docker.com/_/mysql for environment variables that you can optionally set (e.g. username, password, etc.)

To start the your mysql service.

docker-compose up

To stop your mysql service.

docker-compose down  # or ctrl-c if service is running in foreground

Using Homebrew

Disclaimer: Your milage may vary, I've updated this gist based on comments below but haven't tested it myself since switching to Docker. Please see comments below and/or gist history for additional details.

I was using homebrew version 0.9.5 when writing this gist.

brew -v # => Homebrew 0.9.5

Install the current version of mysql.

# Install current mysql version
brew install mysql

# Start agent for current version of mysql
brew services start mysql

Install the older version of mysql.

# Install older mysql version
brew install mysql@5.6

# Start agent for older version of mysql
brew services stop mysql
brew services start mysql56

Then to switch to the older version.

# Unlink current mysql version
brew unlink mysql 

# Check older mysql version
ls /usr/local/Cellar/mysql56 # => 5.6.27

# Link the older version
brew switch mysql@5.6 5.6.27

And to switch back to the current version.

# Unlink older mysql version
brew unlink mysql56 

# Check current mysql version
ls /usr/local/Cellar/mysql # => 5.7.10

# Link the current version
brew switch mysql 5.7.10

To verify which mysql version you're on at any time.

# Check which version of mysql is currently symlinked
ls -l /usr/local/bin/mysql # => /usr/local/bin/mysql@ -> ../Cellar/mysql56/5.6.27/bin/mysql

# Or using the mysql command
mysql --version
@dorelljames

This comment has been minimized.

Copy link

commented Jan 24, 2016

This really helped me get version 5.6 running correctly. Thanks +1

@serundeputy

This comment has been minimized.

Copy link

commented Feb 7, 2016

👍 greatness.

@jcampoy

This comment has been minimized.

Copy link

commented Feb 14, 2016

thank you very much!

@anphu7492

This comment has been minimized.

Copy link

commented Feb 22, 2016

For me, it works well with mysql version >=5.6, but with version 5.5 it throws me this error:
anphu$ mysql --version
-bash: /usr/local/bin/mysql: No such file or directory
What could be the reason? My OS is El Capitan

@serundeputy

This comment has been minimized.

Copy link

commented Mar 4, 2016

When switching back to mysql55 I needed to additionally do:

brew link mysql55 --force

@sylvaiin

This comment has been minimized.

Copy link

commented Mar 8, 2016

Thank you for this. Really helpfull
Although if anyone wants to downgrade from 5.7 to 5.6 good luck

I finally had to

@LouisRitchie

This comment has been minimized.

Copy link

commented Jul 25, 2016

Thanks a lot, this is exactly what i was looking for. Very helpful 👍

@BlakeBurnette

This comment has been minimized.

Copy link

commented Oct 7, 2016

This seems to create weird behaviors @@Version pulls up 5.7, but mysql --version pulls 5.5 which produces errors with Strict Dates.

@TheCorp

This comment has been minimized.

Copy link

commented Oct 22, 2016

If for some reason your mysql won't load after swapping versions you might have this issue - http://stackoverflow.com/questions/10610875/mysql-pid-ended-cannot-start-mysql/26343751#26343751

Remove the .err file from the data dir and then it should load smoothly

@noahpatterson

This comment has been minimized.

Copy link

commented Nov 22, 2016

Just FYI with I had to remove the *.err files /usr/local/var/mysql and the ib_logfile* files per http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-procedure-inplace

@LucasArruda

This comment has been minimized.

Copy link

commented Dec 1, 2016

@sylvaiin thanks for the tips. Migrating for me was not so easy. Even after removing 5.7 from brew, and doing all those steps from the article you linked, somehow mysql command was refusing to connect to mysql 5.6 service.

What worked for me was removing both versions from brew (uninstalling them), like totally wiping mysql (both mysql and mysql56) from my system and then installing mysql 5.6 with brew. That worked!

@travisdetert

This comment has been minimized.

Copy link

commented Feb 1, 2017

What is the point of this, exactly? Why can I not just install mysql56 only and have this work as expected? (My google fu is not finding a reliable way of NOT installing 5.7 at this point and having 5.6 work reliably)

@huiwzh

This comment has been minimized.

Copy link

commented Mar 21, 2017

I find in my brew searching, the mysql@5.5 instead of mysql55, so should: brew install mysql@5.5

@LoranceChen

This comment has been minimized.

Copy link

commented Mar 24, 2017

There should be use brew install mysql@5.6 , see the tips:
Ξ ~ → brew install homebrew/versions/mysql56
Updating Homebrew...
^CWarning: Use mysql@5.6 instead of deprecated homebrew/versions/mysql56
Warning: mysql@5.6 is a keg-only and another version is linked to opt.
Use brew install --force if you want to install this version

Ξ ~ → brew search mysql
automysqlbackup mysql-connector-c mysql-utilities
mysql ✔ mysql-connector-c++ mysql@5.5
mysql++ mysql-sandbox mysql@5.6 ✔
mysql-cluster mysql-search-replace mysqltuner

and then link with brew switch mysql@5.6 5.6.35
finally start server: brew services start mysql56

Hope that will be some help.

@mikeghen

This comment has been minimized.

Copy link

commented Apr 18, 2017

@LoranceChen, Thanks! 👍

@geryit

This comment has been minimized.

Copy link

commented Apr 19, 2017

To switch to older mysql server version, I had to stop current mysql service also :

brew services stop mysql
brew services start mysql56
@jpmelguizo

This comment has been minimized.

Copy link

commented Apr 21, 2017

Went with mysql@5.6 and I'm getting: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Although brew services list says mysql@5.6 is started, there's no mysqld process running and no mysql.sock file anywhere. Any ideas on how to fix it? should I just go with the deprecated mysql56 instead?

@jsweeney

This comment has been minimized.

Copy link

commented Apr 24, 2017

@jpmelguizo I also had your problem. My issue was that the mysql@5.6 was trying to boot the mysql 5.7 database (installed in the default /usr/local/var folder). If you want to run both in parallel on their own distinct databases, there are a few more steps to do.

  1. Create a new (separate) data directory and database for the 5.6 version
    Run /usr/local/Cellar/mysql@5.6/5.6.36/bin/mysql_install_db --user=root --datadir=/usr/local/var/mysql56

  2. Change the configuration to start the 5.6 version pointing to this database
    Edit /usr/local/Cellar/mysql@5.6/5.6.36/homebrew.mxcl.mysql@5.6.plist to change both /usr/local/var/mysql instances to /usr/local/var/mysql56 (I didn't put the @ in the folder name just in case it would cause issues).

<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.mysql@5.6</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql@5.6/bin/mysqld_safe</string>
    <string>--bind-address=127.0.0.1</string>
    <string>--datadir=/usr/local/var/mysql56</string> <!-- <<<<<<<<<<<<<<<<<< HERE -->
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql56</string> <!-- <<<<<<<<<<<<<<<<<< AND HERE -->
</dict>
</plist>
  1. Change the port of the 5.6 server - if ever you want to start both at the same time.
    Edit /usr/local/Cellar/mysql@5.6/5.6.36/my.cnf, uncomment port and change it to some other value than 3306.
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
port = 3356
# server_id = .....
# socket = .....

NOTE A little caveat about connecting to the MySQL instances after such a setup. Using any kind of driver will work out of the box if you set the correct port. However, at the command line, mysql ignores the --port value by default, so you need something like this
mysql -uroot --port=3356 --protocol=TCP
See this article for more details: https://dev.mysql.com/doc/refman/5.7/en/connecting.html

@jpmelguizo

This comment has been minimized.

Copy link

commented Apr 26, 2017

@JSweeny your solution worked like a charm! now I can switch from one to the other using aliases and without having to dump/import anything. Really helpful when you have to work with Craft2 (has problems with MySQL >5.7.5) Thank you so much!

@gerard-morera

This comment has been minimized.

Copy link

commented May 4, 2017

Love u

@maijaz01

This comment has been minimized.

Copy link

commented Sep 23, 2017

while giving command > mysql

ERROR 1045 (28000): Access denied for user 'sheikhaijaz'@'localhost' (using password: NO)
can you please help on this

@unlessbamboo

This comment has been minimized.

Copy link

commented Jan 4, 2018

Good.

@Quberik

This comment has been minimized.

Copy link

commented Jun 21, 2018

@jsweeney thanks, your solution is awesome. But before run the first, you must go to /usr/local/Cellar/mysql@5.6/5.6.36 dir, otherwise you will see mysql_install_db not found error.

cd /usr/local/Cellar/mysql@5.6/5.6.36

And it works only for mysql 5.6. for mysql 5.7 will be

/usr/local/Cellar/mysql@5.7/5.7.22/bin/mysqld --initialize --user=root --datadir=/usr/local/var/mysql57

And for mysql 5.5

cd /usr/local/Cellar/mysql@5.5/5.5.60
/usr/local/Cellar/mysql@5.5/5.5.60/scripts/mysql_install_db --user=root --datadir=/usr/local/var/mysql55
@rdp

This comment has been minimized.

Copy link

commented Jul 12, 2018

these days it's more like brew install mysql@5.7 but got me going on the right track, thanks!

@ivange94

This comment has been minimized.

Copy link

commented Jul 16, 2018

I followed this now I'm getting mysql command not found.

This helped me instead https://gist.github.com/6temes/3c52f8a472f61d9676e7218a98812286

@ricketybridge

This comment has been minimized.

Copy link

commented Jul 25, 2018

Why is it necessary to have the agents running before doing the relinking and such?

At any rate, this is giving me a "file not found" error:

ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
@esirK

This comment has been minimized.

Copy link

commented Feb 19, 2019

Received the error
Error: homebrew/versions was deprecated. This tap is now empty as all its formulae were migrated.
You need to use
brew install mysql@5.6 instead.

@ryanomor

This comment has been minimized.

Copy link

commented Jun 10, 2019

I had the same issue @esirK. I think this needs to be updated to reflect that change

@benlinton

This comment has been minimized.

Copy link
Owner Author

commented Jun 11, 2019

@LoranceChen @geryit @ryanomor Thanks for you feedback! I edited the gist to apply your recommendations.

For everyone else, I did not test these changes out since I now use Docker to solve this problem. So your milage may vary.

@bpolster

This comment has been minimized.

Copy link

commented Jun 21, 2019

When switching back to mysql55 I needed to additionally do:

brew link mysql55 --force

Similar for me. brew link mysql@5.6 --force. Before, was getting mysql No such file or directory.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.