7.11.3.1. Using Symbolic Links
Prev 7.11.3. Optimizing Disk I/O Next

7.11.3.1. Using Symbolic Links

7.11.3.1.1. Using Symbolic Links for Databases on Unix
7.11.3.1.2. Using Symbolic Links for Tables on Unix
7.11.3.1.3. Using Symbolic Links for Databases on Windows

You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or increase the speed of your system by spreading your tables to different disk.

The recommended way to do this is simply to symlink databases to a different disk. Symlink tables only as a last resort.

7.11.3.1.1. Using Symbolic Links for Databases on Unix

On Unix, to symlink a database, first create a directory on some disk where you have free space and then create a symlink to it from the MySQL data directory. 

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

MySQL does not support linking one directory to multiple databases. Replacing a database directory with a symbolic link works as long as you do not make a symbolic link between databases. Suppose that you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1: 

shell> cd /path/to/datadir
shell> ln -s db1 db2

The result is that, or any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one client updates db1.tbl_a and another client updates db2.tbl_a, problems are likely to occur.

However, if you really need to do this, it is possible by altering the source file mysys/my_symlink.c. Look for the following statement: 

if (!(MyFlags & MY_RESOLVE_LINK) ||
    (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

Change the statement to this: 

if (1)
7.11.3.1.2. Using Symbolic Links for Tables on Unix

Do not symlink tables on systems that do not have a fully operational realpath() call. (Linux and Solaris support realpath()). Check whether your system supports symbolic links by issuing a SHOW VARIABLES LIKE 'have_symlink' statement.

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

  • 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.14, “CREATE TABLE Syntax”. Alternatively, symlinking can be accomplished manually from the command line using ln -s if mysqld is not running.

    Note

    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 not to 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, 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.

Table symlink operations that are not yet supported:

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

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

7.11.3.1.3. Using Symbolic Links for Databases on Windows

Symbolic links are enabled by default for all Windows servers. This enables you to put a database directory on a different disk by setting up a symbolic link to it. This is similar to the way that database symbolic links work on Unix, although the procedure for setting up the link is different. If you do not need symbolic links, you can disable them using the --skip-symbolic-links option.

On Windows, create a symbolic link to a MySQL database by creating a file in the data directory that contains the path to the destination directory. The file should be named db_name.sym, where db_name is the database name.

Suppose that the MySQL data directory is C:\mysql\data and you want to have database foo located at D:\data\foo. Set up a symlink using this procedure

  1. Make sure that the D:\data\foo directory exists by creating it if necessary. If you already have a database directory named foo in the data directory, move it to D:\data. Otherwise, the symbolic link will be ineffective. To avoid problems, make sure that the server is not running when you move the database directory.

  2. Create a text file C:\mysql\data\foo.sym that contains the path name D:\data\foo\.

    Note

    The path name to the new database and tables should be absolute. If you specify a relative path, the location will be relative to the foo.sym file.

After this, all tables created in the database foo are created in D:\data\foo.

The following limitations apply to the use of .sym files for database symbolic linking on Windows:

  • The symbolic link is not used if a directory with the same name as the database exists in the MySQL data directory.

  • The --innodb_file_per_table option cannot be used.

  • If you run mysqld as a service, you cannot use a mapped drive to a remote server as the destination of the symbolic link. As a workaround, you can use the full path (\\servername\path\).

Copyright © 1997, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Up Next
7.11.3. Optimizing Disk I/O Home 7.11.4. Optimizing Memory Use
Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout