Creating table symlinks in MySQL is very easy and it is probably one of the features a lot of MySQL users overlook or never think about. You may ask, why would I want to use symlinks? Some of the reasons you would want symlinks is if you are running low on disk space on the partition where your data generally is and/or you want to move one (or more) table(s) on to a different disk/partition for performance reasons.

One of the things worth mentioning is that MySQL documentation states: “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.” Keeping that in mind, if you have a innodb table and would like to create symlinks for, you should change the engine type to myisam before symlinking. Although, I have heard and personally used symlinks for innodb databases with no side affects.

First let us find out if your MySQL configuration allows symlinks:

mysql> SHOW VARIABLES LIKE 'have_symlink'\G
*************************** 1. row ***************************
Variable_name: have_symlink
Value: YES

I am going to make following assumptions:

• MySQL installation is in /usr/local/mysql.
• You want to create new tables under /mnt/another_partition/mysql/var
  1. you must create the dir: mkdir /mnt/another_partition/mysql/var -p
  2. Make it owned by mysql user: chown mysql /mnt/another_partition/mysql/var

We will create some test data. Following are the steps:

mysql> create database db1;
Query OK, 1 row affected (0.05 sec)
mysql> use db1;
Database changed
mysql> CREATE TABLE `users` ( `id` int(11) default NULL, `name` varchar(255) default NULL ) ENGINE=myisam, data directory="/mnt/another_partition/mysql/var", index directory="/mnt/another_partition/mysql/var";
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+

Drop back to command prompt and “ls” your db1 directory as shown below. Here you can see that users table’s data file (users.MYD) is a symbolic link to the location we specified. You can see that users index file (users.MYI) is symlinked to the location we specified as well.

# ls -al /usr/local/mysql/var/db1/
total 24K
drwx------ 2 mysql mysql 4.0K May 2 00:03 ./
drwx------ 23 mysql mysql 4.0K May 1 23:59 ../
-rw-rw---- 1 mysql mysql 65 May 1 09:33 db.opt
-rw-rw---- 1 mysql mysql 8.4K May 2 00:03 users.frm
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYD -> /mnt/another_partition/mysql/var/users.MYD
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYI -> /mnt/another_partition/mysql/var/users.MYI

Here are the real files which above symlinks are pointing to:

# ls -al /mnt/another_partition/mysql/var/
total 12K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 0 May 2 00:03 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:03 users.MYI

If you go back in to mysql prompt and insert couple rows, you can see the size of users.MYD increase:

mysql> insert into db1.users values (1,"test1");
Query OK, 1 row affected (0.01 sec)
mysql> insert into db1.users values (2,"test2");
Query OK, 1 row affected (0.01 sec)
# ls -al /mnt/another_partition/mysql/var/
total 16K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 40 May 2 00:08 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:08 users.MYI

If you already have data file and index file you would like to move, I recommend shutting down your MySQL server and moving the files manually and create symbolic links by using “ln -s” command. You can read more about ln by doing man ls

NOTE: Even though MySQL documentation says this is only supported 100% for myisam tables, I have read few posts where people say they have done this with innodb tables without any problems. If anybody else can comment on this, I would greatly appreciate it.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.