5.6.1.2 Using Symbolic Links for Tables

Before MySQL 4.0 you should not symlink tables, if you are not very careful with them. The problem is that if you run ALTER TABLE, REPAIR TABLE or OPTIMIZE TABLE on a symlinked table, the symlinks will be removed and replaced by the original files. This happens because the above command works by creating a temporary file in the database directory and when the command is complete, replace the original file with the temporary file.

You should not symlink tables on systems that don't have a fully working realpath() call. (At least Linux and Solaris support realpath())

In MySQL 4.0 symlinks are fully supported only for MyISAM tables. For other table types you will probably get strange problems when doing any of the above mentioned commands.

The handling of symbolic links in MySQL 4.0 works the following way (this is mostly relevant only for MyISAM tables).

• In the data directory you will always have the table definition file and the data and index files.
• You can symlink the index file and the datafile to different directories independently of the other.
• The symlinking can be done from the operating system (if mysqld is not running) or with the INDEX/DATA DIRECTORY="path-to-dir" command in CREATE TABLE. See section 6.5.3 CREATE TABLE Syntax.
• myisamchk will not replace a symlink with the data or index file but work directly on the file the symlink points to. Any temporary files will be created in the same directory where the data 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 to why you should not run mysqld as root or allow persons 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 will be renamed to the new names and the data and index files will be renamed accordingly.
• If you use ALTER TABLE RENAME to move a table to another database, the table will be moved to the other database directory and the old symlinks and the files they pointed to will be deleted. (In other words, the new table will not be symlinked.)
• If you are not using symlinks, you should use the --skip-symlink option to mysqld to ensure that no one can drop or rename a file outside of the mysqld data directory.

Things that are not yet supported:

• ALTER TABLE ignores all INDEX/DATA DIRECTORY="path" options.
• CREATE TABLE doesn't report if the table has symbolic links.
• mysqldump doesn't include the symbolic link information in the output.
• BACKUP TABLE and RESTORE TABLE don't respect symbolic links.

User Comments

Add your own comment.