Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save nasrulhazim/3bfb94e41f29d6fe304403f877ae45d8 to your computer and use it in GitHub Desktop.
Save nasrulhazim/3bfb94e41f29d6fe304403f877ae45d8 to your computer and use it in GitHub Desktop.
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
Copy link

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
Copy link

Excellent thank you!

@zhengmaohujima
Copy link

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
Copy link

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

@javierenciso
Copy link

Hi.
Don't know if this is valid, but I just seem to successfully moved my mysql data directory to an external drive and then just symlinked the resulting new directory back to same place (on a mac)...
It seems to work ;-)

  1. shut down mysql
  2. move my data files directory to external drive
  3. ln -s /Volumes/[external-drive]/mysql /usr/local/var/mysql
  4. start up mysql

just commenting for future use
/best
/javier

@dklinenberg2020
Copy link

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?

@dan-lo
Copy link

dan-lo commented Mar 14, 2022

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.

@LSMLeon
Copy link

LSMLeon commented Sep 9, 2023

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?

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