This is something I have tried to do in the past but never figured out, I knew it was a permissions thing, a bit of back and forth with ChatGPT and I finally figured it out. I did this as part of the migration from a Hackintosh over to a Mac Studio, so some notes on that too:

The help documents available from the current version of MailSteward Pro recommend using MySQL 5.6 and provide a link to that. Possibly a bad idea if you are on an Apple Silicon Mac. I installed 8.0.42. When you install you will be required to enter a MySQL root password. If you let the install fire up, and maybe even if you don’t, all the MySQL stuff is in /usr/local/mysql. There is a data folder in that directory, which is where databases will land, but it is not as simple as just copying it over to an external drive. Default permissions will not let you access the MySQL data folder, and when you create a folder on an external drive the Mac ownership permissions do not allow you to set the permissions you need to set.

Make sure MySQL is not running (you can quit it from the control panel, if it is), and change the path for the Data Directory to /Volumes/ExternalDriveName/DataFolderName (or the path to wherever you want the data to go). You could also update the paths for Error Log and PID File. Hit Apply.

Then go to Terminal and create the data folder:

sudo mkdir -p /Volumes/ExternalDriveName/DataFolderName

sudo chown -R _mysql:_mysql /Volumes/ExternalDriveName/DataFolderName

Then initialize the new data directory:

sudo /usr/local/mysql/bin/mysqld –initialize –datadir=/Volumes/ExternalDriveName/DataFolderName –user=_mysql

There will be some output in terminal, including a new MySQL root password, I wrote that down.

Then you need to edit or create /etc/my.cnf, putting in:

[mysqld]
# Location of your MySQL data
datadir=/Volumes/ExternalDrive/Folder

# Socket file for local connections
socket=/tmp/mysql.sock

# Error log and PID file
log-error=/Volumes/ExternalDrive/Folder/mysql.err
pid-file=/Volumes/ExternalDrive/Folder/mysql.pid

# Character set defaults
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

# Enable secure connections
ssl-ca=/usr/local/mysql/share/ca.pem
ssl-cert=/usr/local/mysql/share/server-cert.pem
ssl-key=/usr/local/mysql/share/server-key.pem

Finally,  if you want to change the MySQL root password:

mysql -u root -p

Enter your password, then you can change it:

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newpassword’;

FLUSH PRIVILEGES;

Using the MySQL preference pane to start and stop the server is fiddly, I think it is better to do this from Terminal with:

sudo /usr/local/mysql/support-files/mysql.server start

sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/support-files/mysql.server restart

If you want to automate startup of MySQL with system startup that can be done with a launchd service (creating a .plist file). I have not done that yet, but probably will do, I think server overhead is low.