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.
My comp runs debian unstable, but the following should apply to any recent Debian or Ubuntu distribution.
First stop the mysql service :
root@comp:~/# /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@comp:~/# cd /var/lib/mysql
root@comp:~/# 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@comp:~/# mkdir /var/www/mysql_datadir
root@comp:~/# 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@comp:~/# cp -r mysql /var/www/mysql_datadir/
root@comp:~/# cp -r wpdb /var/www/mysql_datadir/
root@comp:~/# chown -R mysql:mysql /var/www/mysql_datadir/*
Then update your my.conf file to make it point to the new dir :
root@comp:~/# vim /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@comp:~/# /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 dir. Voilà !