Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Change MySQL Data Directory in OSX

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.

@RedDwarph

This comment has been minimized.

Copy link

@RedDwarph RedDwarph commented May 24, 2018

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

@edwardsotelojr

This comment has been minimized.

Copy link

@edwardsotelojr edwardsotelojr commented Aug 3, 2018

Excellent thank you!

@zhengmaohujima

This comment has been minimized.

Copy link

@zhengmaohujima zhengmaohujima commented Nov 15, 2018

On mac,If /usr/local/mysql is soft connected,you can stop mysql, "cp -R mysql-5.** newDir", " ln -snf newDir /usr/local/mysql ","chown -R _mysql:_mysql newDir/data " ,start mysql.

@ozcarnguyen

This comment has been minimized.

Copy link

@ozcarnguyen ozcarnguyen commented Jul 25, 2019

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

--> We dont need to edit file com.oracle.oss.mysql.mysqld.plist if we use MySQLWorkbench:

  • Firstly, copy the 'data' folder to your new location, then rename (eg: MySQLData --> new location path: /Users/Foo/MySQLData)
  • Secondly, stop MySQL server, then open MySQLWorkbench, connect to server that we want to change datadir location (eg: Local instance 3306), ignore the notifications.
  • Then we open menu "Options File" (in my Mac OS 10.11 is Server/Options File), in "General" tab/"Directories" section, tick "datadir" and type new location path (eg: /Users/Foo/MySQLData) --> Apply to create/change file my.cnf (on my Mac, it was located in /private/etc/my.cnf). We may need to quit MySQLWorkbench after this change.
  • Start MySQL server and follow the perfect guidance below.
    Thank Lawrence for your details! Sorry if my opinion was inserted in your guidance :)

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

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