How to move mysql database to another drive or partition

So you need to physically move around your mysql databases, typically because you want to put them on a another partition or hard drive, or on some network device ? This is how you can do it.

Debian or Ubuntu distribution.

First stop the mysql service :

root@box:~/# /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]

Then go to your current mysql data directory, by default in Debian / Ubuntu it should be /var/lib/mysql. Check that your databases are there (in this example I have 2 bases - the default ‘mysql’ base and a user-created ‘wpdb’ base) :

root@box:~/# cd /var/lib/mysql
root@box:~/# ls
total 21M
-rw-rw---- 1 mysql 10M 2008-05-01 14:39 ibdata1
-rw-rw---- 1 mysql 5.0M 2008-05-01 14:39 ib_logfile0
-rw-rw---- 1 mysql 5.0M 2008-04-27 20:57 ib_logfile1
drwxr-xr-x 2 mysql 4.0K 2008-04-27 20:57 mysql
-rw------- 1 root 6 2008-04-27 20:57 mysql_upgrade_info
drwx------ 2 mysql 4.0K 2008-04-28 19:28 wpdb

Create a new directory for your data (in this example, the /var/www directory which is located on another partition) and give ownership on it to the mysql user :

root@box:~/# mkdir /var/www/mysql_datadir
root@box:~/# chown -R mysql:mysql /var/www/mysql_datadir

Copy your databases to the new dir and update ownership if needed. Only move the databases dirs, don’t touch the other files.

root@box:~/# cp -r mysql /var/www/mysql_datadir/
root@box:~/# cp -r wpdb /var/www/mysql_datadir/
root@box:~/# chown -R mysql:mysql /var/www/mysql_datadir/*


Then update your my.conf file to make it point to the new dir :

root@box:~/# nano /etc/mysql/my.conf

Find the following statement :

datadir = /var/lib/mysql

and update with the new location :

datadir = /var/www/mysql_datadir

And finally restart the mysql service

root@box:~/# /etc/init.d/mysql start
* Starting MySQL database server mysqld [ OK ]


When restarting, mysql re-created files ibdata1, ib_logfile0, etc. in the new data dir.
If everything went OK, you can now remove the original directory.