You can move databases or tables 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 disks.
The recommended way to do this is to symlink entire database
directories to a different disk. Symlink
MyISAM tables only as a last resort.
To determine the location of your data directory, use this statement:
SHOW VARIABLES LIKE 'datadir';
On Unix, the way to symlink a database is first to create a directory on some disk where you have free space and then to create a soft link to it from the MySQL data directory.
shell>mkdir /dr1/databases/testshell>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>cdshell>/path/to/datadirln -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.
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.
Do not symlink tables on systems that do not have a fully
operational realpath() call. (Linux and
Solaris support realpath()). To determine
whether your system supports symbolic links, check the value
of the have_symlink system
variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
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.
To instruct a running MySQL server to perform the symlinking, use the
DATA DIRECTORYandINDEX DIRECTORYoptions toCREATE TABLE. See Section 13.1.17, “CREATE TABLESyntax”. Alternatively, if mysqld is not running, symlinking can be accomplished manually using ln -s from the command line.NoteThe path used with either or both of the
DATA DIRECTORYandINDEX DIRECTORYoptions may not include the MySQLdatadirectory. (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, andREPAIR TABLEstatements.-
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
rootor permit system users to have write access to MySQL database directories. If you rename a table with
ALTER TABLE ... RENAMEorRENAME 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 ... RENAMEorRENAME 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.If you are not using symlinks, start mysqld with the
--skip-symbolic-linksoption to ensure that no one can use mysqld to drop or rename a file outside of the data directory.
These table symlink operations are not supported:
ALTER TABLEignores theDATA DIRECTORYandINDEX 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 databasedb1under the MySQL data directory, a tabletbl1in this database, and in thedb1directory you make a symlinktbl2that points totbl1:shell>
cdshell>/path/to/datadir/db1ln -s tbl1.frm tbl2.frmshell>ln -s tbl1.MYD tbl2.MYDshell>ln -s tbl1.MYI tbl2.MYIProblems result if one thread reads
db1.tbl1and another thread updatesdb1.tbl2:The query cache is “fooled” (it has no way of knowing that
tbl1has not been updated, so it returns outdated results).ALTERstatements ontbl2fail.
On Windows, symbolic links can be used for database directories. This enables you to put a database directory at a different location (for example, on a different disk) by setting up a symbolic link to it. Use of database symlinks on Windows is similar to their use on Unix, although the procedure for setting up the link differs.
Suppose that you want to place the database directory for a
database named mydb at
D:\data\mydb. To do this, create a
symbolic link in the MySQL data directory that points to
D:\data\mydb. However, before creating
the symbolic link, make sure that the
D:\data\mydb directory exists by
creating it if necessary. If you already have a database
directory named mydb 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.
The procedure for creating the database symbolic link depends on your version of Windows.
Windows Vista, Windows Server 2008, or newer have native symbolic link support, so you can create a symlink using the mklink command. This command requires administrative privileges.
Change location into the data directory:
C:\>
cd\path\to\datadirIn the data directory, create a symlink named
mydbthat points to the location of the database directory:C:\>
mklink /d mydb D:\data\mydb
After this, all tables created in the database
mydb are created in
D:\data\mydb.
Alternatively, on any version of Windows supported by MySQL,
you can create a symbolic link to a MySQL database by
creating a .sym file in the data
directory that contains the path to the destination
directory. The file should be named
db_name.symdb_name is the database
name.
Support for database symbolic links on Windows using
.sym files is enabled by default. If
you do not need .sym file symbolic
links, you can disable support for them by starting
mysqld with the
--skip-symbolic-links
option. To determine whether your system supports
.sym file symbolic links, check the
value of the have_symlink
system variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
To create a .sym file symlink, use this
procedure:
Change location into the data directory:
C:\>
cd\path\to\datadirIn the data directory, create a text file named
mydb.symthat contains this path name:D:\data\mydb\NoteThe path name to the new database and tables should be absolute. If you specify a relative path, the location will be relative to the
mydb.symfile.
After this, all tables created in the database
mydb are created in
D:\data\mydb.
The following limitations apply to the use of
.sym files for database symbolic
linking on Windows. These limitations do not apply for
symlinks created using mklink.
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_tableoption 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\).
User Comments
Add your own comment.