Remote Tablespace:
In my previous blog ,we talked about the how to create a Remote tablespace . You may be thinking that, how this feature is really helpful for the user, just by mentioning the absolute path along with Create statement.
Assume a situation like, You had already created a table and it was grown to so big enough, now you wanted move that tablespace to some other location from the default location. due to various reasons like no disk space or high performance devices like SDD.
steps to move the tablespace file:
In the following example the default and new location are assumed as follows.
Default MySQL DATADIR is : /work/default/mysql/db1/
New location is : '/work/new/mysql/test'
1) stop the DB server
2) create an isl file (text file) with the same name as table and with extension called 'isl' (tab1.isl), mention the new location of the tab1.ibd file as
'/work/new/mysql/test/tab1.ibd'
Note: Put this file in the same location where the tab1.frm file lies.(default location).
3) move the tab1.ibd file to new location /work/new/mysql/test/ , from the old location
4) Restart the DB server
5) Now the table is ready for DML operations.
Note: Move the tablespace file tab1.ibd file cautiously as it is user responsibility of placing the right version of the file.
Conclusion:
Many DB's architectures are like that , it will not allow user to move the user tablespace files alone, freely to new location once the DB instance and tablespace files have been created, because, the biggest problem is recovery,
and also the tablespace files are tightly coupled with DB instance.Even if the files are moved, DBA's has to modify the system tables accordingly.which is not advisable at all.
will talk about the usage of transportable tablespace later.......
In my previous blog ,we talked about the how to create a Remote tablespace . You may be thinking that, how this feature is really helpful for the user, just by mentioning the absolute path along with Create statement.
Assume a situation like, You had already created a table and it was grown to so big enough, now you wanted move that tablespace to some other location from the default location. due to various reasons like no disk space or high performance devices like SDD.
steps to move the tablespace file:
In the following example the default and new location are assumed as follows.
Default MySQL DATADIR is : /work/default/mysql/db1/
New location is : '/work/new/mysql/test'
1) stop the DB server
2) create an isl file (text file) with the same name as table and with extension called 'isl' (tab1.isl), mention the new location of the tab1.ibd file as
'/work/new/mysql/test/tab1.ibd'
Note: Put this file in the same location where the tab1.frm file lies.(default location).
3) move the tab1.ibd file to new location /work/new/mysql/test/ , from the old location
4) Restart the DB server
5) Now the table is ready for DML operations.
Note: Move the tablespace file tab1.ibd file cautiously as it is user responsibility of placing the right version of the file.
Conclusion:
Many DB's architectures are like that , it will not allow user to move the user tablespace files alone, freely to new location once the DB instance and tablespace files have been created, because, the biggest problem is recovery,
and also the tablespace files are tightly coupled with DB instance.Even if the files are moved, DBA's has to modify the system tables accordingly.which is not advisable at all.
will talk about the usage of transportable tablespace later.......
No comments:
Post a Comment