How to make homebrew-installed mysql and mysql@5.7 work independently?

Question

I installed (because I need them) both mysql (thus 8.0) and mysql@5.7 via homebrew on a MBP Apple M2 w/ Ventura 13.3.1

I properly set up the (first) mysql install;

Now I switched to the mysql@5.7: firstly I noticed it didn't had a proper database installed (or it uses the same as the other formula...?!!) and experienced the infamous ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) after setting up the service via brew services restart mysql@5.7

Problem: it seems like launch files are named differently but some parameters are set the same (datadir and WorkingDirectory); can I safely change these then...? (also I noticed every time I stopped each service via brew services stop xxx the corresponding file was subsequently removed! so it must comes from somewhere...?)

Basically I'd like to have something like /opt/homebrew/var/mysql@5.7 for the 5.7 version; how do I proceed then? (esp. I would need to have a new database for the 5.7 too...)

As I'm quite new to this area, I started browsing various topics here e.g. https://superuser.com/questions/1333504/brew-install-mysql5-7-cant-connect-to-local-mysql-server-through-socket brew start and brew restart wont start service and a few others, and it inevitably ends up with recommandation to completely wipe out everything mysql-related and reinstall only 1 version (this is not what I want).

for mysql:

me@localhost ~ % more ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
<?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</string>
        <key>LimitLoadToSessionType</key>
        <array>
                <string>Aqua</string>
                <string>Background</string>
                <string>LoginWindow</string>
                <string>StandardIO</string>
                <string>System</string>
        </array>
        <key>ProgramArguments</key>
        <array>
                <string>/opt/homebrew/opt/mysql/bin/mysqld_safe</string>
                <string>--datadir=/opt/homebrew/var/mysql</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>WorkingDirectory</key>
        <string>/opt/homebrew/var/mysql</string>
</dict>
</plist>

for mysql@5.7:

me@localhost ~ % more ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
/Users/me/Library/LaunchAgents/homebrew.mxcl.mysql.plist: No such file or directory
me@localhost ~ % more ~/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist 
<?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>LimitLoadToSessionType</key>
        <array>
                <string>Aqua</string>
                <string>Background</string>
                <string>LoginWindow</string>
                <string>StandardIO</string>
                <string>System</string>
        </array>
        <key>ProgramArguments</key>
        <array>
                <string>/opt/homebrew/opt/mysql@5.7/bin/mysqld_safe</string>
                <string>--datadir=/opt/homebrew/var/mysql</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>WorkingDirectory</key>
        <string>/opt/homebrew/var/mysql</string>
</dict>
</plist>

PS: when I modify the aforementioned file, starting the service fails (and btw the file is overwritten...):

me@localhost ~ % brew services start mysql@5.7
Bootstrap failed: 5: Input/output error
Try re-running the command as root for richer errors.
Error: Failure while executing; `/bin/launchctl bootstrap gui/503 /Users/me/Library/LaunchAgents/homebrew.mxcl.mysql@5.7.plist` exited with 5.

score 0 · Answer 1 · answered Apr 23 '23 at 18:32

If you run more than one instance of MySQL on the same OS, each instance must have distinct configuration for:

Data directory
Port
Socket file

The datadir is set in your plist as the MySQL Server is run. You can set the others using options, or in the respective my.cnf file for each instance.

There are a couple of other options to run multiple instances of MySQL Server on the same computer.

One is to run MySQL Server instances in Docker containers, making sure to specify distinct configuration for each. When I have needed multiple versions of MySQL on the same computer, I have used Docker.

Another is to use dbdeployer, a free tool for running ad hoc server instances without using Docker. This is usually meant for development or testing, but it's quite useful if you need to set up replicas and things like that.

Remember that once you have multiple instances on the computer (whether they run in Docker or not), you should specify the port or socket to connect to when you run clients.

How to make homebrew-installed mysql and mysql@5.7 work independently?

1 Answers1