On Unix, the way to symlink a database is to 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 doesn't support linking one directory to multiple databases. Replacing a database directory with a symbolic link will work fine as long as you don't 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
Now, for 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, there will be problems.
If you really need to do this, you can change one of the source files. The file to modify depends on your version of MySQL. For MySQL 4.0 and up, look for the following statement in the mysys/my_symlink.c file:
if (!(MyFlags & MY_RESOLVE_LINK) || (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
Before MySQL 4.0, look for this statement in the mysys/mf_format.c file:
if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
Change the statement to this:
On Windows, you can use internal symbolic links to directories by compiling MySQL with -DUSE_SYMDIR. This allows you to put different databases on different disks. See Section 184.108.40.206, "Using Symbolic Links for Databases on Windows."
220.127.116.11 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 will be 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 will probably 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:
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:
18.104.22.168 Using Symbolic Links for Databases on Windows
Beginning with MySQL 3.23.16, the mysqld-max and mysql-max-nt servers for Windows are compiled with the -DUSE_SYMDIR option. This allows 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 symbolic links work on Unix, although the procedure for setting up the link is different.
As of MySQL 4.0, symbolic links are enabled by default. If you don't need them, you can disable them with the skip-symbolic-links option:
Before MySQL 4.0, symbolic links are disabled by default. To enable them, you should put the following entry in your my.cnf or my.ini file:
On Windows, you make 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 like this:
After that, all tables created in the database foo will be created in D:\data\foo. Note that the symbolic link will not be used if a directory with the database name exists in the MySQL data directory.
blog comments powered by Disqus