Prerequisites

Mac

This has been tested only on ARM (Apple Silicon) Macs. YMMV on x86. I don't see why it wouldn't work, but you may need to change some directory paths.

MySQL

You'll need to have MySQL 5.7 and 8 installed. The former is somewhat tricky, because the formula is disabled. To install it, you can edit the formula (brew edit [email protected]), remove that line, and then install it with

HOMEBREW_NO_INSTALL_FROM_API=1 brew install [email protected].

Brew may set a root password – either note it for use, or change it by logging in and using ALTER USER.

Instructions

Stop both versions if running with brew stop mysql@$VERSION
Create individual configuration files for each version:

export base_dir="/opt/homebrew/etc/my.cnf.d"
mkdir -p "$base_dir"

# NOTE: you can change the buffer pool settings as desired
# this would reserve a total of 8 GiB of RAM for the two instances
# if you don't need that, consider lowering it – use this script for exact values
# https://gist.github.com/stephanGarland/cd6a5c667e8406cf9cfb661a12399ddd

cat << EOF > "${base_dir}/mysql"{57,80}.cnf
[mysqld]
bind-address = 127.0.0.1
port = 330XX
socket = /tmp/mysqlYY.sock
innodb_adaptive_hash_index = 0
innodb_monitor_enable = all
innodb_buffer_pool_chunk_size = 134217728  # 128 MiB
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 4294967296  # 4.0 GiB
innodb_print_all_deadlocks = on
innodb_status_output = on
innodb_status_output_locks = on
innodb_sync_array_size = 16
secure_file_priv = ""
EOF

# edit the files however you'd like to edit XX and YY
# suggested ports are 3306 and 3307
# suggested sockets are /tmp/mysql{57,80}.sock

Edit the .service and .plist files for both versions. I wrote a script to accomplish this as well, but the manual steps are here for understanding. NOTE: the script currently does not initialize the data directories. I wrote it to automate making the changes after a minor version upgrade for 8.0, as Brew resets the configuration files. Initialization could be added to the script if desired.

Edit the service file for each version:

# nominally located at
# /opt/homebrew/Cellar/[email protected]/X.Y.Z_P/[email protected]

# in each, change `ExecStart` and `WorkingDirectory` under `[Service]`, example:
ExecStart=/opt/homebrew/opt/[email protected]/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
# would change to
ExecStart=/opt/homebrew/opt/[email protected]/bin/mysqld_safe --defaults-file\=/opt/homebrew/etc/my.cnf.d/mysql57.cnf --datadir\=/opt/homebrew/var/mysql57

# NOTE: --defaults-file MUST be the first argument

WorkingDirectory=/opt/homebrew/var/mysql
# would change to
WorkingDirectory=/opt/homebrew/var/mysql57

Edit the Property List file for each version:

# nominally located at
# /opt/homebrew/Cellar/[email protected]/X.Y.Z_P/[email protected]

# in each, find the block that looks like this:
  <key>ProgramArguments</key>
  <array>
    <string>/opt/homebrew/opt/[email protected]/bin/mysqld_safe</string>
    <string>--datadir=/opt/homebrew/var</string>
  </array>

# would change to:
  <key>ProgramArguments</key>
  <array>
    <string>/opt/homebrew/opt/[email protected]/bin/mysqld_safe</string>
    <string>--defaults-file=/opt/homebrew/etc/my.cnf.d/mysql57.cnf</string>
    <string>--datadir=/opt/homebrew/var/mysql57</string>
  </array>

# NOTE: --defaults-file MUST be the first argument

# then, do the same for this block:
  <key>WorkingDirectory</key>
  <string>/opt/homebrew/var/mysql</string>

# would change to:
  <key>WorkingDirectory</key>
  <string>/opt/homebrew/var/mysql80</string>

Initalize the new data directories

/opt/homebrew/opt/[email protected]/bin/mysqld --datadir=/opt/homebrew/var/mysqlXY --initialize
# NOTE: if you get an error that the directory isn't empty, it probably means
# you attempted to create the directory on your own – delete it, and try again

Manually start each MySQL instance one at a time, and verify you can login to each one. This will run in the foreground, so have a second terminal window open.

/opt/homebrew/opt/[email protected]/bin/mysql.server start --datadir=/opt/homebrew/var/mysqlXY
# NOTE: this should default to 3306, since you haven't specified a config file
# but in case of failure, try -P XXXX for the specified port

Manually stop MySQL (you may have to issue kill against its PID)
After ensuring that MySQL isn't running (ps -ef | grep mysqld), have Brew restart the service (not start) – this accomplishes the equivalent of systemctl daemon-reload in Linux.

brew services restart [email protected]

Usage

To run an instance on-demand, use brew services run [email protected]
To stop a running instance, use brew services stop [email protected]
To have an instance automatically start on boot, use brew services start [email protected]

Optionally alias the two versions, e.g. alias mysql80="mysql -S /tmp/mysql80.sock -uroot -pLOL"

Or, for a more secure installation (other than running as root – it is assumed you can fix that), use mysql_config_editor:

mysql_config_editor set --login-path=mysql80 -S /tmp/mysql80.sock -uroot -p
# enter your password when prompted
# then, to use this, you'd run:
mysql --login-path=mysql80
# which you could _also_ wrap into a shell alias like 'mysql80' if you'd like

Also, it's recommended that you include --default-character-set=utf8mb4 in your mysql alias

stephanGarland/macos_local_mysql_multiple_versions.md

Prerequisites

Mac

MySQL

Instructions

Usage