Sunday 14 April 2013

Remote Tablespace & Transportable tablespace of MySQL

Before talk about the Remote & transportable tablespace. Lets talk about the MySQL DB tablespace concepts.

MySQL consists of 2 kinds of tablespaces, system tablespace and user tablespace.

The system tablespace works same way as any other databases. one can ADD/DROP as many as DATAFILES from the system tablespace.

But the user tablespace architecture is entire different from other databases. Each user table would associate with one DATAFILE and One Format file, and the datafile will have the extension of .ibd and the format file will have the extension of .frm. The frm file would contain the structure of the table.And these files will be located in the default MySQL DATADIR location.

The following example will clearly explain you about the ibd and frm files.

When user execute the following DDL , it would create 2 files in the default DATADIR location of the DB instance.

CREATE TABLE tab(c1 int) ;
tab.frm -----> This is a format file (definition file)
tab.ibd -----> This is a datafile

Remote tablespace:
When user wants to create a table in different location than the Default location.User will have to mention the DDL with the location (absolute path) .

CREATE TABLE tab(c1 int) DATA DIRECTORY '/x/y';

Transportable tablespace:
Moving or copying the tablespace files (data file)  from one location to other location.

Note : Assuming innodb_file_per_table=ON (Default).

Now lets talk about how flexible these user tablespace files.

* The ibd file can be created either in the Default location or in the remote location on the same machine.
* Move the ibd files from old location to the new location,when the disk is full.
* Move/copy From One Database instance to another database instance when the DB servers are UP and running.
* Supports recovery from media.
* Faster than the traditional backup methods like logical & physical backup methods.
* Alter table Import tablespace.
* Alter table discard tablespace.
* Great flexibility and user friendly for moving/copying the tables.

Conclusion:
Remote tablespace and transportable tablepsace are the one of the greatest features of MySQL, supported in 5.6. I strongly believe that, this feature would give great advantage to the users during DB administration.

Will talk about more on how the remote & transportable tablespace usage  in my next blog.

No comments:

Post a Comment