In one of the real production scenario, I have faced a situation where the disk of underlying data directory was used upto 90 % and this disk size cannot be increased. Hence the only option left was to add the new disk but in mysql (before 8) we really don’t have any option to create multiple data directories and can only be one at that moment. Challenges of this approach has been mentioned in the last of this article.

Data file of an innodb table in background has been represented by .ibd file when innodb file per table is enabled. If it is not enabled then either data of this table will reside in system tablespace i.e. ibdata01 or in user defined table space which can be created by ‘create tablespace’.

In this paper, I am considering that innodb file per table is enabled. In Mysql, we define data directory in cnf file which remains default for every table until we don’t specify data directory parameter while creating the table which is like below :

create table table_name(table def) data directory =‘new one’;

When this table get created I see a .isl file of this newly created table (say test.isl ) in the default data directory which contains path of ibd file (say test.ibd ) of this newly created table. So this is typically like a symbolic link of linux.

So, this is done and we can put our new table to another location apart from default one. But problem started when I wanted to move my ***existing table*** to the new location.

Simple option is to re create this table with new data directory and take dump and restore which is a time taking approach.

Now to solve this, I made use of this “isl” file which can be easily understand by mysql. On creation of normal innodb table we have ibd and frm file only.

I have a table test and data file as test.ibd, and test.frm as definition which resides in /default_data/data mount point. As per requirement, i want to move this table in new mount point i.e. /new_data/data .

So, firstly i make a isl file like this :

echo ‘/new_data/data/test.ibd’ > /default_data/data/test.isl

(you can also create this isl file in some other data directory at this step & later copy it into data dir)

and then create a folder /new_data/data

Now, I have to shutdown my database cleanly and for that disable innodb fast shutdown variable. At this time it is also best to take the buffer pool dump with the help of innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup and yes pct value should be defined as per the requirement of the page required post start up.

Once it is down move the test.ibd file to the new location

mv test.ibd /new_data/data/

if you have put isl file in different location then move isl file to default data directory :

mv test.isl /default_data/data/

make ensure that owner of isl file is mysql and if it is not then mysql cannot recognize this table and will throw error that table space of this table is missing.

Now restart the mysql and check if table is accessible. If in case your table is not accessible , then it is purely permission and ownership related issue.

This method is the best possible to reduce downtime when you are in production.

Challenges with this approach

During the time of alter table, this might create some issues because the location of the ibd file in frm and ibdata is registered as the default data directory. Hence this should be taken into account while implementing this approach.

About Ankit Kapoor

Ankit Kapoor is a database geek and enjoy playing with open source databases like MySQL and PG.
Connect with me on linkedin