MySQL Reference Manual :: 7.6.1.2 Using Symbolic Links for Tables on Unix

Search the MySQL manual:

MySQL Reference Manual

7.6.1 Using Symbolic Links
7.6.1.1 Using Symbolic Links for Databases on Unix
7.6.1.2 Using Symbolic Links for Tables on Unix
7.6.1.3 Using Symbolic Links for Databases on Windows

Additional languages

Additional formats

Previous / Next / Up / Table of Contents

7.6.1.2. Using Symbolic Links for Tables on Unix

Before MySQL 4.0, you should not symlink tables unless you are very careful with them. The problem is that if you run ALTER TABLE, REPAIR TABLE, or OPTIMIZE TABLE on a symlinked table, the symlinks are removed and replaced by the original files. This happens because these statements work by creating a temporary file in the database directory and replacing the original file with the temporary file when the statement operation is complete.

You should not symlink tables on systems that don't have a fully working realpath() call. (At least Linux and Solaris support realpath()). You can check whether your system supports symbolic links by issuing a SHOW VARIABLES LIKE 'have_symlink' statement.

In MySQL 4.0, symlinks are fully supported only for MyISAM tables. For other table types, you may get strange problems if you try to use symbolic links on files in the operating system with any of the preceding statements.

The handling of symbolic links for MyISAM tables in MySQL 4.0 works the following way:

In the data directory, you always have the table definition file, the data file, and the index file. The data file and index file can be moved elsewhere and replaced in the data directory by symlinks. The definition file cannot.
You can symlink the data file and the index file independently to different directories.
The symlinking can be done manually from the command line with ln -s if mysqld is not running. With SQL, you can instruct the server to perform the symlinking by using the DATA DIRECTORY and INDEX DIRECTORY options to CREATE TABLE. See Section 13.1.5, “CREATE TABLE Syntax”.
myisamchk does not replace a symlink with the data file or index file. It works directly on the file a symlink points to. Any temporary files are created in the directory where the data file or index file is located.
When you drop a table that is using symlinks, both the symlink and the file the symlink points to are dropped. This is a good reason why you should not run mysqld as root or allow users to have write access to the MySQL database directories.
If you rename a table with ALTER TABLE ... RENAME and you don't 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 to move a table to another database, the table is moved to the other database directory. The old symlinks and the files to which they pointed are deleted. In other words, the new table is not symlinked.
If you are not using symlinks, you should use the --skip-symbolic-links option to mysqld to ensure that no one can use mysqld to drop or rename a file outside of the data directory.

SHOW CREATE TABLE doesn't report if a table has symbolic links prior to MySQL 4.0.15. This is also true for mysqldump, which uses SHOW CREATE TABLE to generate CREATE TABLE statements.

Table symlink operations that are not yet supported:

ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options.
BACKUP TABLE and RESTORE TABLE don't respect symbolic links.
The .frm file must never be a symbolic link (as indicated previously, only the data and index files can be symbolic links). Attempting to do this (for example, to make synonyms) 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 /path/to/datadir/db1
shell> ln -s tbl1.frm tbl2.frm
shell> ln -s tbl1.MYD tbl2.MYD
shell> ln -s tbl1.MYI tbl2.MYI
```
There are problems if one thread reads db1.tbl1 and another thread updates db1.tbl2:
- The query cache is fooled (it believes tbl1 has not been updated so it returns outdated results).
- ALTER statements on tbl2 also fail.

Previous / Next / Up / Table of Contents

User Comments

Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245

Add your own comment.

Top / Previous / Next / Up / Table of Contents