Sunday, December 28, 2008

MySQL data in /home direcotory


Here is the procedure how to change Mysql default data directory

By default, MySQL’s datadir is placed in the /var/lib/mysql directory.

Create the directory that will be new datadir (ex:-/home/db)

chown the directory to the mysql:mysql user

sudo chown -R mysql:mysql /home/db/*

You need to stop the mysql server using the following command

sudo /etc/init.d/mysql stop

Now you need to edit the /etc/mysql/my.cnf file

sudo vi /etc/mysql/my.cnf

and look for “datadir = /var/lib/mysql” this si where mysql database default data directory here you need to change this one to your new directory

datadir = /home/db

copy the files from the old datadir to the new location. However, make sure that the files named
ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location.

Make sure that the files and directories are owned by mysql user

Make changes in the my.cnf to point the new datadir.

Restart the MySQL database

sudo /etc/init.d/mysql start



You simply edit /etc/apparmor.d/usr.sbin.mysqld. Underneath the two lines authorizing the default MySQL data directories, add two more with your custom directory. Make sure you have a trailing / on the directory name, otherwise it will not work (I had this problem at first). After this change, restart AppArmor:

sudo invoke-rc.d apparmor restart

Now you should be able to set up the new directory as a MySQL data directory and initialize it using mysql_install_db. For more information on this topic, check out the following links:

No comments: