Multiple MySQL versions using Homebrew on macOS giving Error - Can't connect to local MySQL server through socket

I am trying to set up multiple MySQL versions using Homebrew. I have already been using MySQL 8.*, which is running fine. However, due to some old project requirements today, I have installed another version of MySQL (5.7). Now when I switch to mysql@5.7, it gives me the following error.

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

Please have a look at the following details

Both versions of MySQL are installed using Homebrew.

MySQL 8.* (latest) location

/usr/local/Cellar/mysql/8.0.33/*

MySQL 5.7 location

/usr/local/Cellar/mysql@5.7/5.7.42/*

Their symlinks are in /usr/local/opt/*

I have modified homebrew.mxcl.mysql@5.7.plist as follows.

Original

...
<plist version="1.0">
<dict>
    ...
    <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>

Modified

...
<plist version="1.0">
<dict>
    ...
    <array>
        <string>/usr/local/opt/mysql@5.7/bin/mysqld_safe</string>
        <string>--datadir=/usr/local/var/mysql57</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>WorkingDirectory</key>
    <string>/usr/local/var/mysql57</string>
</dict>
</plist>

To switch between versions, I have set up an alias in ~/.zhsrc

# MySQL Version Switching
mysqlv() {
    brew services stop mysql
    brew services stop mysql@5.7
    brew unlink mysql mysql@5.7
    brew link --force --overwrite $1
    brew services start $1
}

alias mysql57="mysqlv mysql@5.7"
alias mysql80="mysqlv mysql"
export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"

Later I set password for the root user to none with the following query.

SET PASSWORD FOR root@localhost='';

Now when I run the alias mysql57, it switches and links the MySQL version but when I try to login to mysql -uroot it gives below error.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket 

Can anyone help me to fix the issue so I can switch and use any version of MySQL

I think this issue is related to the fact that both mysqld instances seems to be configured to use the same socket file /tmp/mysql.sock. If the socket file is not correctly removed when one of the instances is stopped, it might cause issues when the other instance is started.

There are different options to get around this issue:

• Using TCP to connect to the mysqld server instead of using sockets
• Using docker containers for the mysql server instances. If you're doing web development I highly recommend taking a look at ddev which makes it extremely easy to set up different versions of mysqld.
• Configuring different socket files for the instances by modifying the mysqld config for at least one of the instances:

[mysqld]
socket=/tmp/mysql2.sock