By Ded Ryzing: MySQL is a great product and may be the #1 database engine on the Internet today.  It’s fast, flexible, powerful and best of all, free.  By default, on Debian and Ubuntu servers, the data directory is located at /var/lib/mysql.  This may be fine, but I like to keep the database files seperated into it’s own location.  As such, here is how to move the MySQL data directory in Ubuntu and Debian:

1. Create the directory that will be the new datadir. ( example: /home/db )
2. Change ownership of new folder to the mysql user and group: sudo chown -R mysql:mysql /home/db
3. Stop the MySQL database server: sudo /etc/init.d/mysql stop
4. Now you need to edit the /etc/mysql/my.cnf file
1. sudo nano /etc/mysql/my.cnf
2. look for “datadir = /var/lib/mysql” and change it to datadir = /home/db
5. 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.
6. Make sure that the files and directories are owned by mysql user.  If not: sudo chown -R mysql:mysql /home/db/*
7. Restart the MySQL database server: sudo /etc/init.d/mysql start

Hope you find this helpful.

Tags: howto, mysql, debian, ubuntu