The file system shows there is a user, "_mysql", that I cannot "su" as root. What is basically going on so I can get mysqld running again

The following shell command sequence makes me realize I don't understand something very basic about how user and group accounts works on MacOS:

/tmp$ whoami
jondoe
/tmp$ cd /usr/local/mysql/data
/usr/local/mysql/data$ ls -l | grep mydatabase
drwxrwxrwx 55 _mysql _mysql 1760 Jun 24 2019 mydatabase
/usr/local/mysql/data$ su
Password:
/usr/local/mysql/data# su xxxx
su: unknown login: xxxx
/usr/local/mysql/data# su _mysql
/usr/local/mysql/data# whoami
root
/usr/local/mysql/data#

summary: The file system shows that the mysql installation created a user account and group account both called: "_mysql". Yet, when I am root, and "su" that account, I remain the root user.

why do I care? I ran my database fine using El Capitan. Then, I upgraded to Catalina, and now I'm having trouble (I think permission) with running mysqld. And, as a general "best practice", I think you are suppose to minimize the number of things running as root on your machine, yet _mysql and root sort of look like the same account. Of course, I really just want my db running again and understanding the _mysql could lead to this.

Solution 1:

My mysql database (5.6.23) became unable to start when I upgraded from El Capitan to Catalina (10.15.5). Now it works. This is what I did, but its possible some of this might not be needed or a recommended thing to do:

/tmp$ su
Password:
/tmp# cd /usr/local/mysql/data
/usr/local/mysql/data# chown _mysql:_mysql *
/usr/local/mysql/data# chmod 777 *
/usr/local/mysql/data# exit
/usr/local/mysql/data$ cd /tmp
/tmp$ sudo /usr/local/mysql/support-files/mysql.server start &

One major error was that "/usr/local/mysql/data/JonDoe-MacBook-Pro.local.pid could not be created". And that was resolved by the chown and chmod commands I think. Before the chown, the user was root and group was wheel for files, but not directories, in /usr/local/mysql/data.

(#1) this does not start the server

/tmp$ su
Password:
/tmp# cd /usr/local/mysql/support-files
/usr/local/mysql/support-files# ./mysql.server start &

(#2) this does the server

/tmp$ sudo /usr/local/mysql/support-files/mysql.server start &

My understanding is that option #1 and #2 should start the server in the exact same way. This added to my not understanding.