Skip to content

Instantly share code, notes, and snippets.

@nasrulhazim

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

Created April 8, 2017 10:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nasrulhazim/3bfb94e41f29d6fe304403f877ae45d8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nasrulhazim/3bfb94e41f29d6fe304403f877ae45d8 to your computer and use it in GitHub Desktop.
Download ZIP
Change MySQL Data Directory in OSX
Raw
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.

@laalaguer
Copy link

laalaguer commented Jun 13, 2025 •
edited
Loading

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.

:)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment