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 [email protected], 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/[email protected]/5.7.42/*

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

I have modified [email protected] as follows.

Original

...
<plist version="1.0">
<dict>
    ...
    <array>
        <string>/usr/local/opt/[email protected]/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/[email protected]/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 [email protected]
    brew unlink mysql [email protected]
    brew link --force --overwrite $1
    brew services start $1
}

alias mysql57="mysqlv [email protected]"
alias mysql80="mysqlv mysql"
export PATH="/usr/local/opt/[email protected]/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