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 box runs Ubuntu Server 7.10 (Gutsy Gibbon), but the following should apply to any recent Debian or Ubuntu distribution.
First stop the mysql service :
[email protected]:~/# /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) :
[email protected]:~/# cd /var/lib/mysql
[email protected]:~/# ls
-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 :
Copy your databases to the new dir and update ownership if needed. Only move the databases dirs, don’t touch the other files.
Then update your my.conf file to make it point to the new dir :
[email protected]:~/# 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
[email protected]:~/# /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.