8.11.3.1 Using Symbolic Links

You can move databases or tables from the database directory
to other locations and replace them with symbolic links to the
new locations. You might want to do this, for example, to move
a database to a file system with more free space or increase
the speed of your system by spreading your tables to different
disks.

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.

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';

The handling of symbolic links for MyISAM
tables works as follows:

In the data directory, you always have the table format
(.frm) file, the data
(.MYD) file, and the index
(.MYI) file. The data file and
index file can be moved elsewhere and replaced in the
data directory by symlinks. The format file cannot.

You can symlink the data file and the index file
independently to different directories.

To instruct a running MySQL server to perform the
symlinking, use the DATA DIRECTORY
and INDEX DIRECTORY options to
CREATE TABLE. See
Section 13.1.17, “CREATE TABLE Syntax”. Alternatively, if
mysqld is not running, symlinking can
be accomplished manually using ln -s
from the command line.

Note

The path used with either or both of the DATA
DIRECTORY and INDEX
DIRECTORY options may not include the MySQL
data directory. (Bug #32167)

myisamchk does not replace a symlink
with the data file or index file. It works directly on
the file to which the symlink points. Any temporary
files are created in the directory where the data file
or index file is located. The same is true for the
ALTER TABLE,
OPTIMIZE TABLE, and
REPAIR TABLE statements.

Note

When you drop a table that is using symlinks,
both the symlink and the file to which the
symlink points are dropped. This is an
extremely good reason not to run
mysqld as the system
root or permit system users to have
write access to MySQL database directories.

If you rename a table with
ALTER TABLE
... RENAME or RENAME
TABLE and you do not move the table to another
database, the symlinks in the database directory are
renamed to the new names and the data file and index
file are renamed accordingly.

If you use
ALTER TABLE
... RENAME or RENAME
TABLE to move a table to another database, the
table is moved to the other database directory. If the
table name changed, the symlinks in the new database
directory are renamed to the new names and the data file
and index file are renamed accordingly.

If you are not using symlinks, start
mysqld with the
--skip-symbolic-links
option to ensure that no one can use
mysqld to drop or rename a file
outside of the data directory.

As indicated previously, only the data and index files
can be symbolic links. The .frm
file must never be a symbolic link.
Attempting to do this (for example, to make one table
name a synonym for another) produces incorrect results.
Suppose that you have a database db1
under the MySQL data directory, a table
tbl1 in this database, and in the
db1 directory you make a symlink
tbl2 that points to
tbl1:

Problems result if one thread reads
db1.tbl1 and another thread updates
db1.tbl2:

The query cache is “fooled” (it has no
way of knowing that tbl1 has not
been updated, so it returns outdated results).

ALTER statements on
tbl2 fail.

8.11.3.1.3 Using Symbolic Links for Databases on Windows

On Windows, symbolic links can be used for database
directories. This enables you to put a database directory at
a different location (for example, on a different disk) by
setting up a symbolic link to it. Use of database symlinks
on Windows is similar to their use on Unix, although the
procedure for setting up the link differs.

Suppose that you want to place the database directory for a
database named mydb at
D:\data\mydb. To do this, create a
symbolic link in the MySQL data directory that points to
D:\data\mydb. However, before creating
the symbolic link, make sure that the
D:\data\mydb directory exists by
creating it if necessary. If you already have a database
directory named mydb in the data
directory, move it to D:\data.
Otherwise, the symbolic link will be ineffective. To avoid
problems, make sure that the server is not running when you
move the database directory.

The procedure for creating the database symbolic link
depends on your version of Windows.

Windows Vista, Windows Server 2008, or newer have native
symbolic link support, so you can create a symlink using the
mklink command. This command requires
administrative privileges.

Change location into the data directory:

C:\> cd \path\to\datadir

In the data directory, create a symlink named
mydb that points to the location of
the database directory:

C:\> mklink /d mydb D:\data\mydb

After this, all tables created in the database
mydb are created in
D:\data\mydb.

Alternatively, on any version of Windows supported by MySQL,
you can create a symbolic link to a MySQL database by
creating a .sym file in the data
directory that contains the path to the destination
directory. The file should be named
db_name.sym,
where db_name is the database
name.

Support for database symbolic links on Windows using
.sym files is enabled by default. If
you do not need .sym file symbolic
links, you can disable support for them by starting
mysqld with the
--skip-symbolic-links
option. To determine whether your system supports
.sym file symbolic links, check the
value of the have_symlink
system variable using this statement:

SHOW VARIABLES LIKE 'have_symlink';

To create a .sym file symlink, use this
procedure:

Change location into the data directory:

C:\> cd \path\to\datadir

In the data directory, create a text file named
mydb.sym that contains this path
name: D:\data\mydb\

Note

The path name to the new database and tables should be
absolute. If you specify a relative path, the location
will be relative to the mydb.sym
file.

After this, all tables created in the database
mydb are created in
D:\data\mydb.

Note

Because support for .sym files is
redundant with native symlink support available using
mklink, use of
.sym files is deprecated as of MySQL
5.6.9 and support for them will be removed in a future
MySQL release.

The following limitations apply to the use of
.sym files for database symbolic
linking on Windows. These limitations do not apply for
symlinks created using mklink.

The symbolic link is not used if a directory with the
same name as the database exists in the MySQL data
directory.