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:
Sunday, December 28, 2008