How to Move the MySQL data directory in Ubuntu

By Angsuman Chakraborty, Gaea News Network
Tuesday, January 6, 2009

MySQL data directory is the most important and sensitive place for any administrator. How do you move the whole data directory all on a sudden? There is no particular topic in the internet writing about it clearly. So I thought of sharing my own experience in Ubuntu.

Process

  1. Open the terminal
  2. Stop MySQL with the command

    sudo /etc/init.d/mysql stop
  3. Copy the existing data directory (which is located in /var/lib/mysql) using the command

    sudo cp -R -p /var/lib/mysql /path/to/new/datadir

  4. All you need are the data files. Delete the others with the command

    sudo rm /path/to/new/datadir

    (You will get a message about not being able to delete some directories, but do not care about them)

  5. Edit the MySQL configuration file with the command

    gksu gedit /etc/mysql/my.cnf

  6. Find the entry for datadir, change the path to the new data directory.
  7. But there is a trick involved here. Ubuntu uses some security software called AppArmor that specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you’ll never be able to restart MySQL with the new datadir location.
    • In the terminal, enter the command

      sudo gedit /etc/apparmor.d/usr.sbin.mysqld

    • Copy the lines beginning with /var/lib/mysql
    • Comment out the originals with hash marks (#), and paste the lines below the originals.
    • Now change /var/lib/mysql in the two new lines with /path/to/new/datadir. Save and close the file.
    • Reload the AppArmor profiles with the command

      sudo /etc/init.d/apparmor reload

  8. Restart MySQL with the command

    sudo /etc/init.d/mysql restart

  9. MySQL should now start without any errors and your data will be stored in the new location. If  an error finds you do check the process carefully again.

Good Luck!

Discussion
April 8, 2010: 6:02 am

Hi,

Thanks very much for this very useful write up. I think the web and contributors like yourself who make their knowledge available openly and freely is what makes a real difference in our use of technology. My grateful thanks again.

Rgds,
Vijay

March 28, 2010: 12:31 pm

thankz for this tutorial ..

so help me for newbie :)


Davide
February 4, 2010: 2:44 pm

Hello!
Thanks for you great article, but this worked on one installation, failed in another: The service won’t start properly and doesn’t provide any logs…
Do you have any idea of where to check???


Than
July 14, 2009: 5:59 am

Hi.
Just saying thanks.
Your article really helps :)


Than
July 14, 2009: 5:46 am

Hi!
Thanks you very much.
Your article really helps.
Gotcha.

YOUR VIEW POINT
NAME : (REQUIRED)
MAIL : (REQUIRED)
will not be displayed
WEBSITE : (OPTIONAL)
YOUR
COMMENT :