How to change the MySQL data default directory

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

You may also like...

44 Responses

  1. TJ says:

    The third step deletes the data which have been just copied. Shouldn’t be “sudo rm /var/lib/mysql” there instead?

  2. Eli Compton says:

    So you copy the existing data directory, and then immediately delete the copy you made!?

  3. Daniel says:

    To the previous two commentors; in addition to copying the data, the copy command he used also copied some extraneous information. Since all relevant information is contained in subdirectories, his -rm command only removes the unneeded data. Note, however, that he makes no mention of deleting the original directories; I am unsure whether or not needed information is contained in them other than the mentioned tables.

  4. Jimmy Ilenloa says:

    Thank you very much. This resource was found useful! It worked.

  5. erop says:

    A month ago I did this procedure on Jaunty. Everything works OK. It keeps working after updating to Karmic. But it fails when I did it on fresh Karmic install. I did it twice but only “Starting MySQL database server mysqld [fail]” πŸ™
    Are there any principal differences between Jaunty and Karmic in this point?

  6. erop says:

    That’s me again with problem on Karmic above. I made the procedure on clean MySQL server instance without any custom databases and everything works fine now! It probably means that there was a problem with my own databases.

  7. thanker says:

    Thanks allot !!

  8. greg says:

    many thanks! πŸ™‚ very useful.

  9. Sk8erPeter says:

    Big-big thanks to the author(s)!
    This worked perfectly!
    That was just what I needed.
    Thanks again! πŸ™‚

    Pete

  10. vejey says:

    This tutorial helped me a lot.
    Now i have my Raid-5 with 4 disks pointing to MySql data. Wow… Amazing. Thank you… very detailed explanation!!!!

  11. Dasy2k1 says:

    on step missing

    you need to

    sudo chown mysql /path/to/new/dir
    sudo chgrp mysql /path/to/new/dir

  12. _LameMInd says:

    don’t use symlinks, apparmor does not appreciate!

  13. Michael says:

    Thanks! I tried it for hours. Than I read your Tip with apparmor, and it worked in minutes.

  14. Carlos Paiva says:

    One more soul saved by your tip. tks a lot

  15. Bryan Niederberger says:

    Thanks for posting this, banging my head against a wall for a couple hours until I saw this posting.

  16. Pete says:

    can it works on a different partition (mounted of course) of the hard disk, with a different file system (like NTFS)?
    I have already tried, but it doesn’t work

  17. Pete says:

    sorry maybe my words was a bit unclear. I use ubuntu 10.04 in a partition of my hard disk, but i want the mysql-server running on ubuntu save the databases in a directory which is in a different partition, formatted with NTFS. The guide above doesn’t work!

  18. Peter says:

    on step:

    sudo /etc/init.d/mysql restart

    give me some error:

    Error 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/mysqld/mysqld.sock

  19. Varad says:

    Hi,
    We have around 10 databases are inside the default dir /var/lib/mysql.Due to space issue in “/”, planning to change the new directory /new/mysql.(/new have more space).exactly, i followed the above procedure.but application are not working.for e.x, zabbix is installed already.when i try to access,i got the following error,
    mysql_connect(): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)[/usr/share/zabbix/include/db.inc.php:58]

    Please let me know the solution for this.

    Regards,
    varad

  20. Syka says:

    before you restart the mysql server be sure that the owner and group of all the database folders and files in the mysql data directory is mysql.myslq

    cd DataDir
    sudo chown -R mysql *
    sudo chgrp -R mysql *

    it use to be the problem of this error:
    Error 2002 (HY000): Can’t connect to local MySQL server through socket β€˜/var/mysqld/mysqld.sock

    and by the way… great tutorial.

  21. Zequez says:

    Thanks! I didn’t imagine the apparmor part jaja

  22. lewee says:

    Hey i tried your method but i get a ERROR 2002 HY000: Cant connect to local mysql server through…Please help.

  23. Isaac says:

    If you follow the instructions here this shouldn’t be a problem for you, but for those who stumble here looking for answers: if you have innodb tables, make sure you copy the ib* files over, specifically ibdata1, ib_logfile0, and ib_logfile1. I tried doing the transfer just copying the database folders, and got errors telling me “Mysql error: table db.tableName” didn’t exist, though it showed up in show tables and the .frm file was where it should be. Copying over the db folders + the aforementioned ib* files made it work.

  24. Ken says:

    Following these instructions exactly in Natty has broken my system, starting with

    Error 2002 (HY000): Can’t connect to local MySQL server through socket β€˜/var/mysqld/mysqld.sock

    2 hours gone trying to fix this. FML.

  25. Valerio says:

    Thanks for your helpful post: it saved my day.

  26. Vivek says:

    Hi,
    I am getting the same error in Ubuntu 11.10.
    Any fix available?

    my home directory is encrypted. I changed the path to inside home directory.

  27. Nirmalya says:

    Very useful post, and was just in time for me. Many thanks to the author(s).

  28. hardik says:

    i want to store mysql files to “/etc/apache2/…” .

    i’ve done all the changes given above, then also
    error comes, like:

    ” ERROR 1 (HY000): Can’t create/write to file ‘/etc/apache2/hardik.txt’ (Errcode: 13) ”

    please guide me

    i’ve change “datadir = /etc/apache2/mysql ”
    in my.cnf file.

    i’ve also change in sudo gedit /etc/apparmor.d/usr.sbin.mysqld file.

  29. Sonia says:

    Very useful post, but reloading apparmor I got this error:

    AppArmor parser error for /etc/apparmor.d/usr.sbin.mysqld in /etc/apparmor.d/usr.sbin.mysqld at line 9: Could not open ‘abstractions/mysql’ [fail]

    Please help me! πŸ™‚

  30. Ben says:

    Worked perfectly for me. I didn’t need to run the third command (rm) because the copy command only copied the mysql subfolder. It’s hard to find linux tutorial posts that are straight to the point like this. Thank you!

Leave a Reply

Your email address will not be published.