MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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 InnoDB
tables,
use the alternative technique explained in
Section 14.6.1.2, “Creating Tables Externally” instead.
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.18, “CREATE TABLE Statement”. 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 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.
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 root
operating system user or permit operating 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.
These table symlink operations are not supported:
ALTER TABLE
ignores the
DATA DIRECTORY
and INDEX
DIRECTORY
table options.
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
:
shell>cd
shell>/path/to/datadir
/db1ln -s tbl1.frm tbl2.frm
shell>ln -s tbl1.MYD tbl2.MYD
shell>ln -s tbl1.MYI tbl2.MYI
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.