G’day guys!
Have you ever tried to start MySQL as a service, either on MacOS or Windows - and the service inexplicably starts, then stops? Chances are, it’s encountering a file permissions issue behind the scenes, but is neglecting to tell you. Well, let’s look at how you might be able to identify the problem, and take some steps to resolve it.
Why is it stopping!?
Let’s get this out of the way first. To figure out why the service is stopping, I recommend you try starting the service on the command line, where you’ll hopefully get some more output as to what’s going wrong. On MacOS, that’ll probably look like: /usr/local/mysql/support-files/mysql.server start.
Note that this file location assumes that you’ve installed MySQL using the official installer. If you’ve installed MySQL using another means (eg: homebrew), then the path might look a bit different. Regardless though, you can find your mysql install directory (so long as mysql is on your path) by running which mysql. Once you’ve done that, you should be able to adjust the previous command so that /support-files/mysql.server start is relative to your mysql install directory.
In my case, I got back something that looked like this:
Starting MySQL
./usr/local/mysql/bin/mysqld_safe: line 653: /usr/local/mysql/data/Jasons-iMac.hub.err: Permission denied
Great! How do I fix it?
Provided that you got a similar-looking error as the one shown above, then fundamentally, this is a permissions issue. You’ll need to make sure that the user account assumed by the MySQL service has permissions to access the directories and files that it requires. You could try this using the GUI (yuck!) or here’s some command-line magic to do it for you:
sudo chown -R _mysql:admin <your mysql directory>
For a thorough breakdown of the chown command, you can check out the man page. In short though, we’re changing the owner of the directory (eg: /usr/local/mysql) to the _mysql user and admin group. The _mysql user is a special user account created by the MySQL installer, which is the user assumed by the mysql service when it runs. See this link for more info on how that happens.
After running that, you should also run:
sudo chmod -R u+rwX,g+rwX,o-rwx <your mysql directory>
Once again, you can visit the man page for a breakdown of the command, but long story short, we want to recursively set read (r), write (w) and execute (x) permissions on files in the directory for both the user who owns the file (u+), the group associated with the file (g+) and other users o-. After running the previous command, the files should be owned by the _mysql user, and associated with the admin group.
If you want to verify the permissions after running the above two commands (or you’re still getting errors), you could try navigating to your mysql directory and running ls -l ./ to display all of the files in the directory, including their owners, groups and permissions.
Hope somebody finds this helpful someday - quite possibly future me!
Happy querying! Catch ya!