I wanted to change the location of my SQL databases in order for them to be in my user’s home folder (that way, my databases would be portable as I travel with my user’s home folder). By default, when you install mySQL on OS X, everything is located at /usr/local/mysql and the data folder (the one containing the databases) goes to /usr/local/mysql/data.

The location of the data can be set in the my.cnf file BUT, don’t be alarmed, this file doesn’t exist on OS X by default. The server works without it and the file is for you to create if you need to change the server’s default behavior…

Steps :

  1. Download and install mysql for OS X
  2. Install the preference pane. Start your server to make sure it works. Stop it again as we need to alter the configuration.
  3. Move your data folder where you wish to have it i.e.
    1
    mv /usr/local/mysql/data /home/<user>/Applications/mysql/data
  4. Copy the file my-small.cnf in /etc and name it my.cnf
    1
    sudo cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf
  5. Edit this configuration file
    1
    sudo vi /etc/my.cnf
  6. Change the data path : datadir=/home/<user>/…./data
    Note: if you use innodb, change the innodb data location directive as well
  7. Launch your mysql server again, it should start normally, if not, review the steps above as something must have gone wrong
Filed under: Computing, Mac OS X

Tagged with: , , , ,

Liked this page?

Tweet it

Subscribe to the RSS feed or sign up for the newsletter now.

Posts related to Move your mySQL data on OS X

7 Comments to "Move your mySQL data on OS X"

  1. Comment by WouterDS on October 29, 2011 at 5:14 pm Reply

    Hello,

    This doesn’t work for OS X Lion, can you help me?
    I’m really stuck.

    Best Regards,

    • Comment by shalfon on October 31, 2011 at 10:53 am Reply

      Hello, It is actually on OS X Lion that I’ve done this. It worked fine here. Do you have an error message or information on your problem ?

  2. Comment by shake10861 on January 7, 2012 at 8:36 am Reply

    Tried this on OS X Lion… no worky…

    There is no error message for me, however, the mySQL service will not start now. Had to reverse everything… 🙁

  3. […] Cet article est inspiré (pour ne pas dire traduit) de l’article présent sur World Gone Web […]

  4. Comment by Gaudemer on December 17, 2012 at 5:51 pm Reply

    In fact you need to add two line in my.cnf.
    The both lines are not present in the original file copied in step 4 (my-small.cnf)
    The two lines are :
    datadir=/CHEMIN/QUE/VOUS/VOULEZ
    pid-file=/CHEMIN/QUE/VOUS/VOULEZ/localhost.pid

    ps : i made a french “translation” of your article tell me if it’s a problem for you.
    http://eticweb.info/2012/12/17/comment-changer-lemplacement-du-dossier-data-de-mysql/

  5. Trackback on Changing datadir for mysql on Mac osx error - How-To Video on March 29, 2013 at 2:10 am

    […] on Mac osx errorReviewed by on Mar 29Rating: I’ve referred to numerous guides (including http://www.worldgoneweb.com/2011/move&#8230; and rajshekhar.net/blog/archives/9… to try and change the location of data for mysql on my […]

  6. Comment by hrivis on June 4, 2013 at 9:49 pm Reply

    … and chown mysql that data directory!

Name (required)

Email (required)

Website

Leave a Reply

  • About Me


    photo of Samantha Halfon Samantha Halfon
    Software Engineer
    blueKiwi software
    Paris, France
    I enjoy playing with my computer(s), listening to Bob Dylan (and related artists) and watching movies (especially if they were directed by Martin Scorsese or John Cassavetes). Sometimes, I play a little guitar... If not doing any of the above, I am either riding a small red bike around Paris, or, making videos. About my videomaking please check out World Wide Angle and its blog.