Map MySQL Databases Outside /var/lib/mysql

Written by Tony Bhimani
June 22, 2006

Requirements
Linux & MySQL

This guide illustrates how to map or store MySQL databases outside /var/lib/mysql and should apply to all versions of MySQL from version 3.53 and up on any flavor of Linux. CentOS 4.3 was used for this tutorial with the default MySQL RPM packages installed. Why would you want to map MySQL databases to another directory? Consider the following scenarios. You're hosting several web sites and want each database stored in the sites' directory path. Backups become much easier and if you apply a quota to the home directory then the database usage space is also factored in. Another example is you're running out of disk space and need to move some or all of the databases to another drive. You could map the larger databases to this new drive to free up disk space on the one containing /var/lib/mysql. Mapping databases is useful whatever the case. I've used this technique on RedHat 7.3 & 9.0 and CentOS 4.2 and up. It only works with MySQL MyISAM tables because those are stored as files inside the database directory. InnoDB tables, regardless of the database, are all stored in a single file. Mapping won't work unless you map the entire /var/lib/mysql directory somewhere else which defeats the purpose of this tutorial.

The scenario for this tutorial is I have a database called cis375 in the default /var/lib/mysql directory. Now I want move this database to my web site home directory located at /web/tony. I've created a subdirectory called databases in /web/tony specifically for storing all my databases related to my web site.

Warning: Backup your database before you attempt any of these steps. If you screw up and lose your database you'll have only yourself to blame.

Under the assumption that people may be visiting my site and the database is in use, I'll need to temporarily shut down MySQL to avoid any integrity errors.

/etc/init.d/mysqld stop

Take note that the init.d script for my MySQL installation is called mysqld. If the mysqld gives a command not recognized then try it without the d so the command line is /etc/init.d/mysql stop.

The next step would be to move my database cis375 from /var/lib/mysql to my new destination directory. In this case it'll be /web/tony/databases.

mv /var/lib/mysql/cis375 /web/tony/databases

Now here is the magic trick that makes this all possible. Ready? Use ln -s to create a symbolic link between the physical directory and a virtual one that resides in /var/lib/mysql. It makes me wonder why I am even posting this if it's that simple.

Now I'll create the link between the physical directory at /web/tony/databases/cis375/ to /var/lib/mysql/cis375.

ln -s /web/tony/databases/cis375/ /var/lib/mysql/cis375

Now that the link is in place I'll log in to MySQL and verify I can access my database, but before I do that I'll need to start the MySQL service.

/etc/init.d/mysqld start

mysql -u root -p
[enter mysql root password]
show databases;
use cis375;
show tables;

So far so good. Next I'll check to see if I can select data from the counter table.

SELECT * FROM `counter`;

The database is working like a charm. Exit MySQL by typing quit.

If your attempt didn't work you may be experiencing a problem I encountered when I first tried mapping databases. Make sure the entire directory path up to where the database will be stored has the everyone execute bit set. The mysql user (or whatever system account you have MySQL run as) needs to be able to access the directory otherwise your database will not be accessible by MySQL. Check all directory permissions up to the final destination and use chmod to add the execute bit on everyone. For example, here is my directory structure with permissions and I changed the databases directory to not have execute set.

With execute not set if I go in to MySQL and show the databases, cis375 has now vanished.

Enabling the execute bit for everyone else will allow the mysql user access to the destination directory of the database. Use chmod to change the permissions of the directory.

chmod 751 /web/tony/databases/

I chose to use 751 which will allow anyone in the directory but effectively hides the contents from ls.

cd /web/tony/databases/
ls -la

If you don't care about hiding the directory contents then use chmod 755 against the database parent directory.

In summary, now you'll be able to map databases anywhere on your system and MySQL will still be able to access them as if they're still in /var/lib/mysql.

How would you rate the usefulness of this content? Poor 1 2 3 4 5 6 7 8 9 Outstanding Optional: Tell us why you rated the content this way. Characters remaining: 1024

Average rating: 3.75 out of 9. 1 2 3 4 5 6 7 8 9 8 people have rated this content.