Change MySQL Data Directory in OSX

change-mysql-data-directory-in-osx.md

Stop the MySQL Service

I'm using Homebrew to stop the service

brew services stop mysql

Locate MySQL Data Directory

If you're using Homebrew, it should be located at /usr/local/Cellar/mysql/[your-version]

Go to the directory and make a backup for homebrew.mxcl.mysql.plist and open homebrew.mxcl.mysql.plist - either using nano or Sublime Text, doesn't matter.

Update Your Data Directory Path

Following are the default setting. All you need to do is to update the /usr/local/var/mysql to the new path. Save the file.

<?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>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql/bin/mysqld_safe</string>
    <string>--bind-address=127.0.0.1</string>
    <string>--datadir=/usr/local/var/mysql</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql</string>
</dict>
</plist>

Copy the Old Databases

Make sure to copy all files under /usr/local/var/mysql to new directory. You may use the following command to copy the files recursively.

cp -R /usr/local/var/mysql /your/new/path

Start the MySQL Service

Once you are done with above steps, do start the service.

brew services start mysql

Verification

Try to connect to the database, create a new database - see either the new database created in old directory /usr/local/var/mysql or you new path.

Thanks for the instructions. To save other people some of the pain I went through, here is some further info that may be helpful (I was doing this with Monterey & MySQL 8):

On editing the homebrew.mxcl.mysql.plist file - I found it necessary to change both the datadir parameter and the WorkingDirectory key to the external drive location. Be aware that changing this file will only affect the data directory when MySQL is launched through HomeBrew - eg brew services start mysql. MySQL will instead run with the default data directory when started directly - eg with mysql.server start.

I was unsuccessful in setting an external data directory through homebrew.mxcl.mysql.plist. Trying to launch MySQL through brew when an external drive was set as data directory produced some kind of error - I think permissions related - that I could not resolve. It only seemed to work when another internal location on the mac drive was set as data directory.

When MySQL is launched without brew (e.g. with mysql.server start) the data directory is not set by the .plist file at all. It is instead set by the my.cnf file. The my.cnf file possible locations can be found using this command: mysql --help or mysql --help | grep my.cnf. In my case there was an existing my.cnf file at /usr/local/etc/my.cnf. I edited this file by adding a data directory location:

[mysqld]
datadir=/Volumes/my/directory/path/

After this change I am able to successfully launch MySQL with mysql.server start, with an external drive data directory (unlike with brew, setting an external drive does not produce any errors).

Homebrew, however reads the datadir from the .plist file so, so if mysql is launched with brew services start mysql the my.cnf changes have no effect. It is annoying I could not get this set up to work with brew (and in general - it's confusing for configuration to be in different places depending on how mysql is launched), but I do at least now understand what's going on and have a working set up. Hope this gives some help to others.

Trying to it gave me this error on the terminal /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist: Path had bad ownership/permissions
Load failed: 122: Path had bad ownership/permissions

Any hints to what to do?

To everyone who is still strugging on this matter, I experimented & moved data directory to an external HDD disk and here is how.

Environment

• Mac Mini 2025 (M4 chip) + HDD external drive formatted with APFS (defaut: case insensitive).
• MySQL version v8.0.42.

Problem

Internal disk is too small to host the MySQL databases. Had to move data to an external HDD.

Failed Attempts

I tried GUI panel (Mac Settings > MySQL > Configuration Tab > Data Directory). Once you hit start the server button it went green then quickly turned red.

It turns out that any time you edited (sudo) the /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist, it got reflected on the GUI and vice-versa. But it doesn't improve the situation and can't view the log file of failed launch. I can't determine if there was a wrong config caused problem that didn't get updated on lauch or simply the error on the permission of folders.

What was Really Wrong with Mac OS

Mac OS by default doesn't set file/folder owner on external disks for maximum compatibility. However it can cause a problem since our MySQL setup requires data folder to be of permission user:_mysql and group:_mysql. Each time i execute:

sudo chown -R _mysql:_mysql folder_name it doesn't yield error, but also doesn't work at all.

You can verify if your external disk supports owner by (Disk Utility > your-disk-name > look for "Owners")

It turns out you need to enable it in the GUI.

(Finder > Right Click on disk > Get Info > scroll down > Uncheck "ignore ownership on this device"), you may click the lock icon to allow changes, and also review who can "Read and Write" the disk.

Now do the sudo chown -R _mysql:_mysql folder_name again and it works.

Move Data

Move the data directory into the HDD disk, I used following:

sudo mv /usr/local/mysql/data /Volumes/your-disk-name/your-location

It worked. Upon double check the original data folder and moved one, the ownership _mysql:_mysql kept.

Launch MySQL

I tried GUI again, fill in the changed three variables accordingly: the --datadir, --log-error and --pid-file.

It doesnt work. The indicator turned green then went red.

So instead, I directly launched it via terminal.

sudo /usr/local/mysql/bin/mysqld \
  --datadir=/Volumes/HDD-disk/.../data \
  --plugin-dir=/usr/local/mysql/lib/plugin \
  --early-plugin-load=keyring_file=keyring_file.so \
  --keyring-file-data=/usr/local/mysql/keyring/keyring \
  --log-error=/Volumes/HDD-disk/.../data/mysqld.local.err \
  --pid-file=/Volumes/HDD-disk/.../data/mysqld.local.pid \
  --user=_mysql

Notice I have correspondingly changed any mysql/data location to the newly moved location for three options, the --datadir, --log-error and --pid-file and leave others untouched.

This time it works without error. I connected it via mysql -u root -p.

When I go back to check on the GUI panel after several minutes, the indicator is green, too.

:)