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.
InnoDB tables, use the
DIRECTORY clause on the
TABLE statement instead of symbolic links, as
explained in Section 14.5.5, “Creating a File-Per-Table Tablespace Outside the Data Directory”. This new
feature is a supported, cross-platform technique.
The recommended way to do this is to symlink entire database
directories to a different disk. Symlink
MyISAM tables only as a last resort.
To determine the location of your data directory, use this statement:
SHOW VARIABLES LIKE 'datadir';
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 soft link to it from the MySQL data directory.
ln -s /dr1/databases/test
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
ln -s db1 db2
The result is that, or any table
db1, there also appears to be a table
db2. If one
db1.tbl_a and another
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. For
tables, use the alternative technique explained in
Section 14.5.5, “Creating a File-Per-Table Tablespace Outside the Data Directory” instead.
Do not symlink tables on systems that do not have a fully
realpath() call. (Linux and
realpath()). To determine
whether your system supports symbolic links, check the value
variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
The handling of symbolic links for
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
INDEX DIRECTORY options to
CREATE TABLE. See
Section 13.1.14, “CREATE TABLE Syntax”. Alternatively, if
mysqld is not running, symlinking can
be accomplished manually using ln -s
from the command line.
The path used with either or both of the
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
OPTIMIZE TABLE, and
REPAIR TABLE statements.
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
... RENAME or
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
... RENAME or
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.
These table symlink operations are not supported:
ALTER TABLE ignores the
DATA DIRECTORY and
DIRECTORY table options.
As indicated previously, only the data and index files
can be symbolic links. The
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
under the MySQL data directory, a table
tbl1 in this database, and in the
db1 directory you make a symlink
tbl2 that points to
ln -s tbl1.frm tbl2.frmshell>
ln -s tbl1.MYD tbl2.MYDshell>
ln -s tbl1.MYI tbl2.MYI
Problems result if one thread reads
db1.tbl1 and another thread updates
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
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
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
mydb in the data
directory, move it to
Otherwise, the symbolic link will be ineffective. To avoid
problems, make sure that the server is not running when you
move the database directory.
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:
In the data directory, create a symlink named
mydb that points to the location of
the database directory:
mklink /d mydb D:\data\mydb
After this, all tables created in the database
mydb are created in