Using Symbolic Links | Mastering MySQL 4

You can easily utilize symbolic links (symlinks) to improve performance of your database and reduce disk latency. The idea is that instead of storing all your data and indexes on one disk, you create a symlink from that one disk to another disk, which actually stores the data. Currently, you can only symlink MyISAM tables and databases. Earlier versions of MySQL had fundamental problems symlinking tables (they would revert to their original location when certain operations were performed on them), but version 4 has taken care of many of the problems.

Symbolic Linking of Databases

Create the database directory on the new location.
Make sure the permissions and ownership are correct (700 and mysql.mysql).
Create a symlink in the data directory pointing to the new location.

o create a symlink for a MyISAM database, follow these steps:

Let's test the creation of a new database, s_db, which you're going to symlink. Assuming you already have a directory, disk2, which is the secondary disk you want to place the database on, create the directories where you're going to store the data (/disk2/mysql/data/ s_db) and change the permissions and ownership, first on a Unix system, as follows:

% cd /disk2 % mkdir mysql % mkdir mysql/data % mkdir mysql/data/s_db % chown mysql /disk2/mysql/data/s_db/ % chgrp mysql /disk2/mysql/data/s_db/ % chmod 700 /disk2/mysql/data/s_db/

Now, back in the data directory, create the symlink:

% cd /usr/local/mysql/data % ln -s /disk2/mysql/data/s_db s_db

Once you've done this, your database will be created (remember MyISAM databases are just subdirectories in the data directory). You can confirm this by connecting to MySQL:

% /usr/local/mysql/bin/mysql -uroot -pg00r002b Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6202 to server version: 4.0.2-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES LIKE 's_db'; +-----------------+ | Database (s_db) | +-----------------+ | s_db            | +-----------------+ 1 row in set (0.00 sec)

Now, to see that data is actually placed on the second disk, create and populate a small table, as follows:

mysql> USE s_db Database changed mysql> CREATE TABLE s1( f1 INT); Query OK, 0 rows affected (0.23 sec) mysql> INSERT INTO s1 VALUES(1); Query OK, 1 row affected (0.03 sec)

Check that the new data has been created on the secondary disk:

mysql> exit Bye % ls -l /disk2/mysql/data/s_db/ total 14 -rw-rw----  1 mysql  mysql     5 Jul  8 02:26 s1.MYD -rw-rw----  1 mysql  mysql  1024 Jul  8 02:26 s1.MYI -rw-rw----  1 mysql  mysql  8550 Jul  8 02:25 s1.frm

To create a symlink for a database on a Windows system, there are a number of differences. Permissions are simpler, and to create a symlink, instead of using ln -s, you just create a text file with the extension .sym. First, create a file called s2_db.sym in your data directory containing the following text:

D:\s_db

Because this is inside your data directory, it will appear on the same level as all your other MyISAM databases (in this case, firstdb, mysql, and test), and when you connect to MySQL, you'll see it as an existing database:

C:\mysql\bin>dir ..\data\  ... Directory of C:\mysql\data 09/03/2002  08:58p      <DIR>          . 09/03/2002  08:58p      <DIR>          .. 09/03/2002  08:31p      <DIR>          firstdb 09/03/2002  08:22p      <DIR>          mysql 09/03/2002  08:23p               4,342 mysql.err 09/03/2002  08:57p                   7 s2_db.sym 09/03/2002  08:22p      <DIR>          test                2 File(s)          4,349 bytes ... C:\mysql\bin>mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.0.3-beta-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES; +----------+ | Database | +----------+ | firstdb  | | mysql    | | s2_db    | | test     | +----------+ 4 rows in set (0.00 sec) mysql> USE s2_db Database changed mysql> CREATE TABLE s1(f1 INT); Query OK, 0 rows affected (0.23 sec) mysql> INSERT INTO s1 VALUES(1); Query OK, 1 row affected (0.03 sec) C:\mysql\bin>dir d:\s2_db ... Directory of d:\s2_db 09/03/2002  09:38p      <DIR>          . 09/03/2002  09:38p      <DIR>          .. 09/03/2002  09:38p               8,550 s1.frm 09/03/2002  09:38p                   5 s1.MYD 09/03/2002  09:38p               1,024 s1.MYI                3 File(s)          9,579 bytes ...

Note

If the previous code doesn't work, you may be running an earlier version of MySQL (in which case you have to add --use-symbolic-links to a line in your my.ini configuration file). It's also possible your version of MySQL was not compiled with -DUSE_SYMDIR, in which case symlinks will not work at all. Usually, mysql-max and mysql-max-nt servers are compiled with this option; however, you should check the latest documentation.

Symbolic Linking of Tables

Using symbolic tables on individual tables is not recommended because there are few features that do not work properly with symlinked tables (though check your latest documentation, as this will likely change soon).

Features that don't yet work with symlinked tables include the following:

BACKUP TABLE and RESTORE TABLE (the symlinks will be lost).
mysqldump does not store symlink information in the dump.
ALTER TABLE (it ignores INDEX/DATA DIRECTORY="path" CREATE TABLE options).

To symlink a table when you create it, you should use the INDEX or DATA DIRECTORY PATH option. DATA DIRECTORY creates a symlink for the .MYD file, and INDEX DIRECTORY places the .MYI file. The following example places the data file of a new table in the firstdb database in the new directory you created earlier.

mysql> USE firstdb; Database changed mysql> CREATE TABLE s_table (a int) DATA DIRECTORY =  '/disk2/mysql/data/s_db'; Query OK, 0 rows affected (0.20 sec) mysql> INSERT INTO s_table VALUES(1); Query OK, 1 row affected (0.01 sec)

You can see that the .frm file (containing the structure) is in the usual data directory, and the .MYD data file is in the new location:

% cd /usr/local/mysql/data/firstdb/ % ls -l /disk2/mysql/data/s_db/ total 16 -rw-rw----  1 mysql  mysql     5 Jul  8 02:26 s1.MYD -rw-rw----  1 mysql  mysql  1024 Jul  8 02:26 s1.MYI -rw-rw----  1 mysql  mysql  8550 Jul  8 02:25 s1.frm -rw-rw----  1 mysql  mysql     5 Jul  8 05:35 s_table.MYD % ls -l s* lrwxrwx--x  1 mysql  mysql    34 Jul  8 05:34 s_table.MYD ->  /disk2/mysql/data/s_db/s_table.MYD -rw-rw----  1 mysql  mysql  1024 Jul  8 05:35 s_table.MYI -rw-rw----  1 mysql  mysql  8548 Jul  8 05:34 s_table.frm

MySQL has created the symlink for the table. You could also have explicitly created this symlink yourself (taking the same care with permissions as you did with creating the database symlink).

The data and index files can be created in different locations as this example demonstrates, assuming the existence of the directory /disk3/mysql/data/indexes:

mysql> CREATE TABLE s2_table (a int) DATA DIRECTORY =  '/disk2/mysql/data/s_db' INDEX DIRECTORY =  '/disk3/mysql/data/indexes'; Query OK, 0 rows affected (0.04 sec)

View the files in their new locations:

% ls -l /disk3/mysql/data/indexes/ total 2 -rw-rw----  1 mysql  mysql  1024 Jul  8 06:01 s2_table.MYI % ls -l /disk2/mysql/data/s_db/ ... -rw-rw----  1 mysql  mysql  0 Jul  8 06:01 s2_table.MYD ... % ls -l /usr/local/mysql/data/firstdb/ ... lrwxrwx--x  1 mysql  mysql    35 Jul  8 06:01 s2_table.MYD ->  /disk2/mysql/data/s_db/s2_table.MYD lrwxrwx--x  1 mysql  mysql    38 Jul  8 06:01 s2_table.MYI ->  /disk3/mysql/data/indexes/s2_table.MYI -rw-rw----  1 mysql  mysql  8548 Jul  8 06:01 s2_table.frm ...

Note

The INDEX DIRECTORY and DATA DIRECTORY options do not work when running MySQL on Windows (although check your latest documentation).