Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization :: 7.10 Using Symbolic Links :: 7.10.2 Using Symbolic Links for Tables on Unix
7.10.3 Using Symbolic Links for Databases on Windows »
Section Navigation      [Toggle]

7.10.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 do not have a fully operational realpath() call. (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 files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.

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 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.

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

    Note

    Beginning with MySQL 4.1.24, 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 why you should not 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, 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 does not 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 supported up through MySQL 4.1:

  • ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options.

  • BACKUP TABLE and RESTORE TABLE do not 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

    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.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Dave Hull on July 6 2004 1:03pm[Delete] [Edit]

Instead of creating a symlink for the datafiles themselves, we've made the /var/lib/mysql directory itself a symlink to a different mysql directory.

We're using innodb tables as well as myisam tables and haven't run into any of the problems mentioned in this documentation.

This may not work for everyone, but it works great for our situation.

Posted by Graham Frank on April 26 2006 5:42pm[Delete] [Edit]

A quick note for those of you do do symlink tables. I symlink, for example, vBulletin tables, and I haven't had issue with the REPAIR TABLE command. However, there is a note you should be made aware of:

Say that you have the table "posts" and you make a symbolic link to it named "prefix_posts". If "posts" gets an error or corruption, then obviously that error and corruption will be present in the "prefix_posts" table too.

Running a REPAIR TABLE "posts" will fix the table "posts"; however, MySQL would have marked the "prefix_posts" table as corrupt and thus the REPAIR TABLE would not be recognized within "prefix_posts". So be sure to restart MySQL so that the symlinked table can be updated and no longer marked as crashed.

Add your own comment.