I'm using Homebrew to stop the service
brew services stop mysql
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.
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>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
Once you are done with above steps, do start the service.
brew services start mysql
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.
If you're not using homebrew...
Stop the MySQL server
Look in
/Library/LaunchDaemons/
for a file named
com.oracle.oss.mysql.mysqld.plist
Open that file in an editor such as TextEdit or BBEdit and you should see a section with the following:
/usr/local/mysql/bin/mysqld
--user=_mysql
--basedir=/usr/local/mysql
--datadir=/usr/local/mysql/data
--plugin-dir=/usr/local/mysql/lib/plugin
--log-error=/usr/local/mysql/data/mysqld.local.err
--pid-file=/usr/local/mysql/data/mysqld.local.pid
It's best not to move everything, just the data folder. In the Finder, use
Go -> Go to Folder
and enter the location of the enclosing folder of the current data directory
/usr/local/mysql/
Copy the 'data' folder to your new location, e.g. your 'home' folder. It's OK to rename the folder to something user friendly such as MySQLData
Now change this line
--datadir=/usr/local/mysql/data
To reflect the new location, noting there is no trailing '/' after the name of the data folder
--datadir=/Users/Foo/MySQLData
Save
Use the Terminal to reload the plist file
cd /Library/LaunchDaemons
sudo launchctl load -F com.oracle.oss.mysql.mysqld.plist
With your new data folder open so you can see the contents, start the MySQL Server and in that folder you should see a file named "ibtmp1" appear. If you do, you've successfully pointed MySQL to the new location. Stop the MySQL Server and "ibtmp1" should disappear.
Finally, with the MySQL Server stopped, correct the permissions on the new folder by changing the owner and group back to "_mysql"; they should have automatically changed when you copied the folder to inherit the permissions from the enclosing folder which is why you could open it and see the contents.
chown -R _mysql /Users/Foo/MySQLData
chgrp -R _mysql /Users/Foo/MySQLData
Restart the MySQL Server
If you want to be absolutely sure data is being stored properly in the new location, create a new, temporary database using a tool such as phpMyAdmin and give it a unique, recognizable name. Be sure you have the root user enabled on your mac (google that if needed). Open the terminal, switch to the root user (su root), navigate to the new data folder (cd /Users/Foo/MySQLData/), and list the contents with details (ls -l). You should see a folder listing for your new database with the current date and with "_mysql" listed as the owner and group.
Hoping others find this helpful,
Lawrence Dodge