Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization :: 7.6 Disk Issues :: 7.6.1 Using Symbolic Links :: 7.6.1.1 Using Symbolic Links for Databases on Unix
7.6.1.2 Using Symbolic Links for Tables on Unix »
Section Navigation      [Toggle]

7.6.1.1. Using Symbolic Links for Databases on Unix

On Unix, the way to symlink a database is first to create a directory on some disk where you have free space and then to create a symlink to it from the MySQL data directory. 

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

MySQL does not support linking one directory to multiple databases. Replacing a database directory with a symbolic link works as long as you do not make a symbolic link between databases. Suppose that you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1: 

shell> cd /path/to/datadir
shell> ln -s db1 db2

The result is that, or any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one client updates db1.tbl_a and another client updates db2.tbl_a, problems are likely to occur.

If you really need to do this, you can change one of the source files. The file to modify depends on your version of MySQL. For MySQL 4.0 and up, look for the following statement in the mysys/my_symlink.c file: 

if (!(MyFlags & MY_RESOLVE_LINK) ||
    (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

Before MySQL 4.0, look for this statement in the mysys/mf_format.c file: 

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

Change the statement to this: 

if (1)

User Comments

Posted by [name withheld] on March 4 2003 5:25am[Delete] [Edit]

When writing symbolic link files from VB, don't use the Open for Input/Write statements, as that puts quotation marks around the path string, which MySQL rejects. The Open for Binary/Put statements do work.

Posted by [name withheld] on March 4 2003 5:34am[Delete] [Edit]

A clarification, which should be obvious: Open for Output/Write doesn't work for symbolic links. I said Open for Input. It's early.

Posted by Paul Mothersdill on September 8 2003 12:21am[Delete] [Edit]

Just in case anybody else is stupid enough to try reading the above late at night:

Create the directory and symlink (with appropriate permissions) _before_ doing anything in MySQL. All "create database" does is to create the directory -- creating the directory and symlink outside MySQL are the equivalent of a "create database" SQL command.

Posted by Nilesh G on January 26 2008 4:29pm[Delete] [Edit]

If you want quotas for users, setup system quotas for the group in which the user is, then create a separate databases directory in his home directory with permission mysql:usergroup and permission 0755

Then move the databases to that directory and create symbolic links to the original mysql datadir.

Posted by Marcus Bointon on September 8 2008 7:30pm[Delete] [Edit]

If you're symlinking to databases outside /var/lib/mysql and you're getting unexpected "(errno: 13)" (permission denied) errors, check your MySQL settings for AppArmor (particularly on Ubuntu as of 8.04) or SELinux. Specifically, make sure your new path is listed in /etc/apparmor.d/usr.sbin.mysqld then restart apparmor.

Add your own comment.