January 23, 2009 · Server · Email This Post

Sponsored Link
MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.

If you want to install Mysql database server in Ubuntu check this tutorial.

What is Mysql Data Directory?

Mysql data directory is important part where all the mysql databases storage location.By default MySQL data default directory located in /var/lib/mysql.If you are running out of space in /var partition you need to move this to some other location.

Note:- This is only for advanced users and before moving default directory make a backup of your mysal databases.

Procedure to follow

Open the terminal

First you need to Stop MySQL using the following command

sudo /etc/init.d/mysql stop

Now Copy the existing data directory (default located in /var/lib/mysql) using the following command

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

All you need are the data files, so delete the others with the command

sudo rm /path/to/new/datadir

Note:- You will get a message about not being able to delete some directories, but that's what you want.

Now edit the MySQL configuration file with the following command

gksu gedit /etc/mysql/my.cnf

Look for the entry for "datadir", and change the path (which should be "/var/lib/mysql") to the new data directory.

Important Note:-From Ubuntu 7.10 (Gutsy Gibbon) forward, 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.

Restart the AppArmor profiles with the command

sudo /etc/init.d/apparmor reload

Restart MySQL with the command

sudo /etc/init.d/mysql restart

Now MySQL should start with no errors, and your data will be stored in the new data directory location.

Sponsored Link

Incoming search terms:

Related posts

43 Comments to “How to change the MySQL data default directory”

  1. Krikke says:

    Thanks, top tip for the apparmor! you saved me an evening of fiddling :)

    [Reply]

  2. hotel says:

    I’m running ubuntu 12.10 with mysql installed (and working) but I do not have a usr.sbin.mysqld inside the directory etc/apparmor.d

    I’m trying to change the data location as described, but mysql fails to restart. I could not locate a file named usr/sbin/mysqld anywhere on the host. Thoughts?

    [Reply]

  3. Mauricio says:

    It looks like you’re removing the new data dir on the rm command

    [Reply]

  4. David says:

    Really helpful — you save me much time and frustration. Thanks!!

    [Reply]

  5. David says:

    er… *saved*

    [Reply]

  6. Zorro says:

    Thanks a lot, dude !

    [Reply]

  7. leandro says:

    hello guys I need do that in a linux debian, so the commands are the same?

    Thanks in advance

    Leandro

    [Reply]

  8. Bjorn says:

    This worked on Ubuntu 12.04 but I also needed to run this command:

    mysql_install_db –user=mysql –ldata=/path/to/new/datadir

    [Reply]

  9. hamiljf says:

    I’m pretty sure I did this right, running 13.04, and it did not work: mysql would not restart and system would not reboot to desktop. Using root console login, backed out changes to my.cnf and usr.sbin.mysqld and the system rebooted successfully. error logs showed the server found tables to be read only, but the files were ok: correct owner (mysql) and permissions (rw). Could try again with Bjorn’s additional command: to be executed at what point during the process ?

    [Reply]

  10. Peter says:

    Thank you, very useful, but be aware running this over an ssh-link (as I did right now) it will mess up the ownership of files and folders.

    CU Peter
    ____________
    DEBIAN Squeeze, GA-P35C, 4GB, 1TB net SW-Raid1

    [Reply]

  11. erik says:

    yeah also no go here on 12.04 (mysql would not restart) quickly undo all changes and hope my desktop will remain…

    [Reply]

  12. Guy Merritt says:

    I was just fiddling with a Linux distro called PinguyOS and wanted to see if some old scripts I use to build MySQL would work succesfully work on that platform. I compile a lot of my own packages – long story. Anyway, I compiled MySQL without any errors (a few warnings) and no significant issues reported in config.log. I use a different structure, totally, than a standard Debian-based (binary) installation. They work on Ubuntu 10, 12, etc. When I would try to start MySQL onPinguyOS, though, I would get an error (can’t connect to socket) and the logs would indicate that MySQL was looking for a data directory at /var/lib/data – even though I had built the thing to point to /usr/local/mysql/data. It also said it couldn’t find some language file, as I recall (again, looking in the wrong directory). It was as if certain paths were hard-coded into the system (somehwere) for mysql. The only MySQL installed binary was libmysqlclient18 (again, as I recall). I can’t test anything right now, but could this strange behavior be the result of AppArmor?

    [Reply]

Leave a Reply