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.

When I tried the OP's approach, it only created the new database in the old directory, not the new one. I'm working on a Mac with an M1 chip. Any advice?

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?