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
- you must create the dir: mkdir /mnt/another_partition/mysql/var -p
- 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.
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.)
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!
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.
Geert,
How would you approach separating high i/o tables onto faster/separate drive(s) to enhance performance?
Thanks for the comment.
Pingback: Webadmin rövidhÃrek #3 at ‹Webakadémia /›
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.
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
Pretty! This was an extremely wonderful article.
Manyy thanks for supplying this information.
My web page twenty
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!
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!
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;
That’s really thinking of the highest order
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
My spouse and i were happy Albert could round up his reports through the entire ideas he had out of the site. It is now and again perplexing just to continually be giving freely information which people might have been selling. And we see we need the writer to give thanks to for this. All the illustrations you have made, the easy web site menu, the friendships your site help engender – it’s everything fabulous, and it is leading our son in addition to the family reckon that this theme is interesting, and that is pretty mandatory. Many thanks for all the pieces!
I want to express some thanks to this writer just for bailing me out of this particular setting. Because of looking throughout the world wide web and coming across solutions which were not pleasant, I thought my life was well over. Existing minus the strategies to the problems you’ve fixed as a result of your guideline is a serious case, as well as the ones that would have in a negative way affected my career if I had not discovered your web site. Your good talents and kindness in maneuvering every part was helpful. I’m not sure what I would’ve done if I hadn’t come upon such a solution like this. I’m able to at this time look forward to my future. Thanks a lot so much for your impressive and effective guide. I will not hesitate to refer your blog to anybody who ought to have tips on this area.