MySQL: How do you use symlinks with MySQL tables?

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.

13 thoughts on “MySQL: How do you use symlinks with MySQL tables?

  1. Oskar Skoog

    Using innodb with file-per-table and symlinking the .idb-files isn’t a very good idea, even if it works to start with. ALTER TABLE (and thus OPTIMIZE TABLE) creates a new file that it works with during the operation and when it’s done, it overwrites the symlink instead of following it.

    (Symlinking ibdata files doesn’t make much sense, since you can specify path for them.)

  2. Geert Vanderkelen

    If you can, you should always _avoid_ using symbolic links. Even if we support it for MyISAM tables, it’s BAD practice. It might be a ‘quick fix’, but once you have time you should find an other solution than symlinks!

    Talking from experience.. it’s a DBA’s nightmare!

  3. Guillaume Lefranc

    I use symlinks on .ibd files in production, but like the above posters said, there are some caveats. I’d prefer to have something like the ALTER TABLESPACE command for Oracle with stored path for each datafile.

  4. Sunny Walia Post author

    Geert,

    How would you approach separating high i/o tables onto faster/separate drive(s) to enhance performance?

    Thanks for the comment.

  5. Pingback: Webadmin rövidhírek #3 at ‹Webakadémia /›

  6. Manasi

    Hi All, I want to symlink databases from other location to mysql default location. Is it possible will mysql able to read them. I have read somewhere that it is possible but I am facing problem in it.

  7. Internal

    Thanks on youjr marvelous posting! I truly enjoyed reading it, you can be a great author.
    I will alwys bookmark your blog and definitely will come back in the future.
    I want to encourage one to continue your great job,
    have a nice afternoon!

    Alsoo visit my web-site Internal

  8. fitness

    W?at i d? not understood i? in truth h?w y?u
    are no longer actuall? much more smartly-preferred than yoou maay bbe now.
    Y?u a?e sso intelligent. Yoou recognize t?u? ?ignificantly w?th regards to thyis topic, produced m? in my opinion ?elieve it from
    numerous varied angles. ?ts like women and men a?e not fascinated ?ntil it’s sometying to d? ?ith Woman gaga!

    ?ou? individual suffs excellent. ?t all times cate for it up!

  9. http://software.gr.com

    I absolut?ly love your blog.. Excelolent colors & theme.
    ?id you make t?is website ?ourself? Please reply ?ack
    as I’m lo?king to creat? my own personal website ?nd want to learn wheere you
    got this from o? what t?e theme i? named.
    Cheers!

  10. Uuta

    mysql> CREATE TABLE new_table SELECT * FROM old_table;This will not always crtaee a new table with the same structure as the old table. It will crtaee a table which fits the data exactly.For example – you have a field which is a VARCHAR(30) but the longest string in that field is only 20 chars long – the new table will be initialised as a VARCHAR(20).The correct procedure would be to crtaee a new table in the likeness of the old:mysql> CREATE TABLE new_table LIKE old_table;and then copying the data in:mysql> INSERT INTO new_table SELECT * FROM old_table;

  11. 33Catherine

    I must say it was hard to find your website in search results.

    You write interesting posts but you should rank your blog
    higher in search engines. If you don’t know 2017
    seo techniues search on youtube: how to rank a website Marcel’s way

Leave a Reply

Your email address will not be published. Required fields are marked *