Wednesday, December 11, 2013

XAMPP : Change the Default mysql data directory

XAMPP : Change the Path of mysql source directory

Steps :

 XAMPP's default mysql source directory is  /opt/lampp/var/mysql
    Here it stores all databases in source file format.

If you want to change the path of this default directory, the most common reason should be that you do not have enough space in your filesystem and your large size databases are making it worse. :)

Now here is the solution.

Solution : 1
1) Stop the xampp
# sudo /opt/lampp/lampp stop
2) Copy database directory using command line. If you use command line, it stores the owner and permissions. Now suppose new path is in /home.
# sudo cp -pr /opt/lampp/var/mysql /home
3) Rename the original database directory for back-up.
# sudo mv /opt/lampp/var/mysql /opt/lampp/var/mysql_old
4) Make symbolic link from new mysql directory to old mysql directory
# sudo ln -s /home/mysql /opt/lampp/var
5) Start xampp
# sudo /opt/lampp/lampp start
6) If every thing is fine, you can delete the backup directory
# sudo rm -r /opt/lampp/var/mysql_old

Note :
If you copy the mysql directory using nautilus (Right click, copy and paste), you can face problem while starting mysql because the owner and permissions are not always same while copying using nautilus that's why you need to use command line.


Solution : 2 (If above solution is not working for you)
1) Create the new directory new-dir in other partition.

2) Now stop XAMPP and change the following attribute in the file /opt/lampp/etc/my.cnf under [mysqld]
datadir         =  /path-of-other-partition/new-dir
[mysqld] user = nobody port = 3306 socket = /opt/lampp/var/mysql/mysql.sock datadir = /media/DGHI-KJHL-023/mysql
If it is not there under [mysqld], just add it yourself
so it will look like this.

3) Copy the directory /opt/lampp/var/mysql/mysql
and /opt/lampp/var/mysql/phpmyadmin and paste into /path-of-other-partition/new-dir

Most important step
4) Now set owner and group both mysql for this new directory.
# sudo chown -R mysql:mysql /path/of/other/partition/new-dir

5) Set the permission 755(maximum open) for the new directory, you can set little less according to security of your network.
# sudo chmod -R 755 /path/of/other/partition/new-dir

Wrong permission can be cause of the error "couldn't start mysql"

6) Start XAMPP.
# sudo /opt/lampp/lampp start
Now new databases will be created in this new directory.

7) If you want to shift old databases in new place, just copy the directory of the database from /opt/lampp/var/mysql and paste into
/path-of-other-partition/new-dir

Make sure about step-4 & step-5.

8) If you face problem while starting and stopping XAMPP, Use 'Kill mysqld' to kill all mysqld processes.
killall mysqld
Now start XAMPP, you will not face this problem.

3 comments:

  1. Thanks for the instructions. A few corrections, however...

    There should be an additional step after Step 3, but before Step 4.

    The additional step should be:
    3(b) Copy the original database directory to the new location:
    sudo cp -pr /opt/lampp/var/mysql_old /home/mysql

    Step 4 should be:
    4) ln -s /home/mysql /opt/lampp/var/mysql

    Used the above commands and everything works.

    ReplyDelete
  2. did what you told ... but phpmyadmin experiences problem with db and shows no db when opened in browser.
    i amde symlink and all was good butphpmadmin doesn't shw db

    also do i need to change the sock line in my.cnf?

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete