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
use the alternative technique explained in
Section 14.4.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 DIRECTORYoptions to
CREATE TABLE. See Section 13.1.18, “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
INDEX DIRECTORYoptions may not include the MySQL
datadirectory. (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
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
rootor permit system users to have write access to MySQL database directories.
If you rename a table with
ALTER TABLE ... RENAMEor
RENAME TABLEand 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 ... RENAMEor
RENAME TABLEto 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 TABLEignores the
INDEX DIRECTORYtable options.
As indicated previously, only the data and index files can be symbolic links. The
.frmfile 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
db1under the MySQL data directory, a table
tbl1in this database, and in the
db1directory you make a symlink
tbl2that 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.tbl1and another thread updates
The query cache is “fooled” (it has no way of knowing that
tbl1has not been updated, so it returns outdated results).