Sep 13, 2018

How to use Symbolic Links for Databases

The way to symlink a database is first to create a directory on the disk where you have free space and then to create a soft link to it from the MySQL data directory.

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

For example, suppose we want to move database `transit` from /var/lib/mysql to /extra/mysql2:

#(The following works on 5.6.16 on CentOS 6, not on 5.5.12 on CentOS 5)
cd    /var/lib/mysql
cp -R transit/ /extra/mysql2/ (You may need to lock the database while copying)
chown -R mysql:mysql /extra/mysql2/transit/
rm -rf transit/
ln -s /extra/mysql2/transit/ .

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.

Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links. Using actual symbolic links has never been supported for InnoDB tables. The DATA DIRECTORY clause of the CREATE TABLE statement is a supported alternative to using symbolic links, which has always been problematic and was never supported for individual InnoDB tables.
Do not symlink tables on systems that do not have a fully operational realpath() call. (Linux and Solaris support realpath()). To determine whether your system supports symbolic links, check the value of the have_symlink system variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';