How to change the MySQL data default directory
Sponsored Link
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.
The third step deletes the data which have been just copied. Shouldn’t be “sudo rm /var/lib/mysql” there instead?
So you copy the existing data directory, and then immediately delete the copy you made!?
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.
Thank you very much. This resource was found useful! It worked.
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?
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.
Thanks allot !!
many thanks! 🙂 very useful.
Big-big thanks to the author(s)!
This worked perfectly!
That was just what I needed.
Thanks again! 🙂
Pete
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!!!!
on step missing
you need to
sudo chown mysql /path/to/new/dir
sudo chgrp mysql /path/to/new/dir
don’t use symlinks, apparmor does not appreciate!
Thanks! I tried it for hours. Than I read your Tip with apparmor, and it worked in minutes.
One more soul saved by your tip. tks a lot
Thanks for posting this, banging my head against a wall for a couple hours until I saw this posting.
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
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!
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
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
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.
Thanks! I didn’t imagine the apparmor part jaja
Hey i tried your method but i get a ERROR 2002 HY000: Cant connect to local mysql server through…Please help.
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.
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.
Thanks for your helpful post: it saved my day.
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.
Very useful post, and was just in time for me. Many thanks to the author(s).
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.
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! 🙂
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!