essay writing on helping others
buying college papers unethical business
resume services des moines ia craigslist
advanced custom fields thesis
custom rolling papers uk
help emmett serve papers to arthur mcfly
essay a time i helped someone
executive curriculum vitae help wanted
cover letter services
professional essay writers australia weather
argumentative essay helpful words
essay on service learning project
where to buy a paper mache snowman head
where to buy a research paper urgently meaning
resume writers kingwood texas
how much to write my dissertation
professional resume writing services in ga
resume writing services brampton on
essay writers needed craigslist seattle
dissertation help in new york
writers at work the essays
where can i buy a paper fireplace
dissertation service learning
yorku essay help
professional resume services in portland oregon
resume writing service portland oregon
resume writing services vancouver wa
essay writing doctors orders
database assignment help
resume writing services in uk keyboard
how to write cover letter customer service
online chat rooms for homework help
weed helps me do homework
essay on multiple personality disorder
order of essay writing
cover letter sales and customer service
purchase custom essays uk
usda forest service research paper
manage quality customer service assignment
when did langston hughes write his essay my american
paper mario 64 helpers of gods precious infants
need help filling out divorce papers
essay about buying a new car
online paper writing service reviews
mba dissertation help
writing services for essays
cheap rolling papers uk
essay on service learning experience
vic uni assignment help
write my business paper
need help with your homework projects
resume services ottawa
history homework helper
suffolk county library homework help
good custom essay websites
cover letter for customer service and receptionists
professional resume writers rochester mn restaurants
dissertation proposal service
how to write my master thesis bound
writers block essay due tomorrow do tomorrow
buy paper shredder gears
buy home work
online essay help writing a cover
biographical essay questbridge help me
help on writing an essay paper
descriptive essay helper paragraph
dissertation help chennai express
cover letter customer service uk virgin
curriculum vitae help me lyrics
resume writing services reno nv news
cheap research paper writing under 50
resume writing services stamford ct map
homework help center columbus library dublin
help write a descriptive essay
homework help writing a story with spelling
buy asda paper shredder
letra de la cancion paperback writer en espaol
paperback writer video location
help kids with homework
how to help your child focus on homework
company custom essay help
best american essay writers
essay on customer driven marketing strategy
paperback writer bass tab guitar pro 4
urgent custom essays review
professional resume writing service milwaukee wi
resume writing services in rochester ny junk
personal statement help lawsuit
argumentative essay about cheating helps students learn
get to tha choppa assignment help
personal statement law school service
calculus homework helper
homework help on geography
essay about myself personality disorders
cover letter help nursing student
tnpl a4 paper online purchase
legal cover letter service rep
home help in essay writing lin tan wedding
write my philosophy education paper airplanes
resume writers eugene or
essay writer software online
dissertation help introduction to logic harry
personal statement help gt travel
resume writing services eagan mn
professional resume writing services in rochester ny
cover letter customer service resume
the order of learning essays on the contemporary university
essay on buying fashionable clothes is wasteful
resume writing services in st. louis mo
essay paper in civil services of pakistan
resume writing services ottawa canada shooting
assignment writers in mumbai india
executive resume writing service uk map
cover letter for management position retail sales
primary homework help britain html
essay on helping the less fortunate quotes
tonight i made the decision to ask alice to help me with my homework
how to write my law school personal statement
algebra 1 homework help and answers
what can i write my report on
virtual homework helper
primary homework help egyptians pictures
buy essays papers
help with personal statement for graduate school
academic essay writers reviews sarah
domestic paper shredder best buy
famous english essay writers scam
marketing assignment help
resume cover letter for insurance customer service
homework services inc
geometry homework help answers
calculus homework help chat
resume services york region ontario
buy a term paper college banners
where can i buy paper confetti
writer 39 s block essay definition
customs and traditions essay
sfpl live homework help
resume writing services salt lake city
buy stamp papers online
custom made paper plates
professional resume writing service sydney australia
cheap resume writing services toronto maple
noteshelf custom paper coffee
top curriculum vitae writing services australia
resume writing services in overland park ks
what can i write my psychology paper on a movie
resume writing services tempe az hotels
get writing paragraphs and essays 2nd edition by mark connelly
where can i buy paper wristbands in stores
resume writing services concord nh newspaper
essay writing help argumentative paragraph
pay someone to write my research paper
thesis writers services uk
resume writers in salem oregon
personal statement scholarship help you meet
custom essay writing service cheap
write my persuasive paper
dissertation editing help review
age essay henry ii middle new reign twelve writers almanac
professional resume writers bay area ca
personal essays famous writers of the 1920s
cheap essay writers ukraine
homework live help
purchase custom essays for sale
buy thesis paper online
best places to buy essays for cheap
argument essay vegetarian diet
answers for algebra 1 homework help
thesis writer responsibilities
cheap blotting papers
write my assignment for me australia news
park ridge library homework help
electrician helper resume cover letter
retail sales associate resume no experience
my favourite english writer essay
cheap resume writing services indianapolis
resume writing services frederick md hotels
homework help cincinnati library downtown
i need help with writing a essay
resume writers sydney
essay help proscan
the help kathryn stockett essays on leadership
homework help for students parents rights
custom writing personal statements
professional resume writers organizations in washington
common methods of ordering material in preparation for essay writing
homework help english poetry images
thesis writing service india lge
help with personal statement for teaching assistant
homework help reducing fractions powerpoint
paper buyers in dallas
buying paper for acrylic paints
paper lions ghost writers
college assignment help writing
samson when i write my masters thesis lyrics
research proposal essay writing help
custom college essay
targetdate series research paper 2011 industry survey
resume services upland ca
 
 
Home MySQL Blogs Portable Tablespace in InnoDB I test it PART2
Portable Tablespace in InnoDB I test it PART2 PDF Stampa E-mail
Scritto da Marco Tusa   
Lunedì 07 Maggio 2012 00:59

 

Overview

After testing ... in my previous article (Portable Tablespace in InnoDB I test it!)
I was thinking...
But how much that will really cost?
How long it will take porting the table space?
What about partitions?
Finally (after Sunny told me it is possible) can I import a table space on a different table (with code modification)?
Here is the work I have done today while having some spare time, after a loooong ride on bicycle with the family.

Question 1: how much it will cost?

I have created 3 tables
  • First 10MB data,
  • Second 100 MB data,
  • Third 1GB data.
Not too much but should be enought to identify a possible delta between tables.
Let's go:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SHOW CREATE TABLE tbtest_XX\G
(root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_10MB\G
*************************** 1. row ***************************
TABLE: tbtest_10MB
CREATE TABLE: CREATE TABLE `tbtest_10MB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_100MB\G
*************************** 1. row ***************************
TABLE: tbtest_100MB
CREATE TABLE: CREATE TABLE `tbtest_100MB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>SHOW CREATE TABLE tbtest_1GB\G
*************************** 1. row ***************************
TABLE: tbtest_1GB
CREATE TABLE: CREATE TABLE `tbtest_1GB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)
 

 

Now let us take the tablespace snapshot

1
2
3
4
(root@localhost) [test_tablespace1]>FLUSH TABLE tbtest_10MB, tbtest_100MB,tbtest_1GB WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test_tablespace1]>
 

 

drwxr-xr-x  2 root  root    4096 2012-04-27 15:31 ./
drwxrwxr-- 30 mysql mysql   4096 2012-04-27 14:59 ../
-rw-r-----  1 root  root     442 2012-04-27 15:26 tbtest3.cfg
-rw-r-----  1 root  root  360448 2012-04-27 15:26 tbtest3.ibd
-rw-r-----  1 root  root   98304 2012-04-27 15:31 tbtest3.ibt
-rw-r-----  1 root  root     440 2012-04-27 14:59 tbtest.cfg
-rw-r-----  1 root  root    8606 2012-04-27 14:59 tbtest.frm
-rw-r-----  1 root  root  360448 2012-04-27 14:59 tbtest.ibd
-rw-r-----  1 root  root   98304 2012-04-27 15:17 tbtest.ibt
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_1*.*  /home/mysql/backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll /home/mysql/backup
total 1276880
drwxr-xr-x  2 root  root        4096 2012-05-05 18:59 ./
drwxrwxr-- 30 mysql mysql       4096 2012-04-27 14:59 ../
-rw-r-----  1 root  root         476 2012-05-05 18:59 tbtest_100MB.cfg
-rw-r-----  1 root  root        8606 2012-05-05 18:59 tbtest_100MB.frm
-rw-r-----  1 root  root    92274688 2012-05-05 18:59 tbtest_100MB.ibd
-rw-r-----  1 root  root         475 2012-05-05 18:59 tbtest_10MB.cfg
-rw-r-----  1 root  root        8606 2012-05-05 18:59 tbtest_10MB.frm
-rw-r-----  1 root  root    10485760 2012-05-05 18:59 tbtest_10MB.ibd
-rw-r-----  1 root  root         474 2012-05-05 18:59 tbtest_1GB.cfg
-rw-r-----  1 root  root        8606 2012-05-05 18:59 tbtest_1GB.frm
-rw-r-----  1 root  root  1203765248 2012-05-05 18:59 tbtest_1GB.ibd
-rw-r-----  1 root  root         442 2012-04-27 15:26 tbtest3.cfg
-rw-r-----  1 root  root      360448 2012-04-27 15:26 tbtest3.ibd
-rw-r-----  1 root  root       98304 2012-04-27 15:31 tbtest3.ibt
-rw-r-----  1 root  root         440 2012-04-27 14:59 tbtest.cfg
-rw-r-----  1 root  root        8606 2012-04-27 14:59 tbtest.frm
-rw-r-----  1 root  root      360448 2012-04-27 14:59 tbtest.ibd
-rw-r-----  1 root  root       98304 2012-04-27 15:17 tbtest.ibt
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
 

 

Do the drop 

1
2
3
4
5
(root@localhost) [test_tablespace1]>drop TABLE tbtest_10MB; DROP TABLE tbtest_100MB;drop TABLE tbtest_1GB;
Query OK, 0 rows affected (0.22 sec)
Query OK, 0 rows affected (0.14 sec)
Query OK, 0 rows affected (0.26 sec)
 

Will create the fake tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
(root@localhost) [test_tablespace1]>
CREATE TABLE `tbtest_10MB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `tbtest_100MB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbtest_1GB` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Discard table space to prepare the tables for the IMPORT
1
2
3
4
5
6
7
8
 
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_10MB DISCARD TABLESPACE;
Query OK, 0 rows affected (0.07 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_100MB DISCARD TABLESPACE;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_1GB DISCARD TABLESPACE;
Query OK, 0 rows affected (0.07 sec)
 

 

Move back all the good tablespaces from backup

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_1*.* .
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 1329248
ddrwx------ 2 mysql mysql       4096 2012-05-05 19:06 ./
drwxr-xr-x 4 mysql mysql       4096 2012-05-03 15:40 ../
-rw-rw---- 1 mysql mysql         65 2012-04-27 14:28 db.opt
-rw-r----- 1 mysql mysql        476 2012-05-05 19:06 tbtest_100MB.cfg
-rw-r----- 1 mysql mysql       8606 2012-05-05 19:06 tbtest_100MB.frm
-rw-r----- 1 mysql mysql   92274688 2012-05-05 19:06 tbtest_100MB.ibd
-rw-r----- 1 mysql mysql        475 2012-05-05 19:06 tbtest_10MB.cfg
-rw-r----- 1 mysql mysql       8606 2012-05-05 19:06 tbtest_10MB.frm
-rw-r----- 1 mysql mysql   10485760 2012-05-05 19:06 tbtest_10MB.ibd
-rw-r----- 1 mysql mysql        474 2012-05-05 19:06 tbtest_1GB.cfg
-rw-r----- 1 mysql mysql       8606 2012-05-05 19:06 tbtest_1GB.frm
-rw-r----- 1 mysql mysql 1203765248 2012-05-05 19:06 tbtest_1GB.ibd
-rw-rw---- 1 mysql mysql       8636 2012-05-04 12:11 tbtest_part.frm
-rw-rw---- 1 mysql mysql         44 2012-05-04 12:11 tbtest_part.par
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partB.ibd
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
 

 

Re-attach the tablespaces to the tables
1
2
3
4
5
6
7
root@localhost) [test_tablespace1]>ALTER TABLE tbtest_10MB IMPORT TABLESPACE;
Query OK, 0 rows affected (3.11 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_100MB IMPORT TABLESPACE;
Query OK, 0 rows affected (10.17 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_1GB IMPORT TABLESPACE;
Query OK, 0 rows affected (8 min 53.98 sec)
 

 

All done and time is increasing a lot with the size of the tablespace.

Question 2: What about partitions?

Create a different directory to store my backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# mkdir /home/mysql/backup1
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_*.* /home/mysql/backup1/
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# rm -f tbtest_1*.*
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 53292
drwx------ 2 mysql mysql     4096 2012-05-05 19:05 ./
drwxr-xr-x 4 mysql mysql     4096 2012-05-03 15:40 ../
-rw-rw---- 1 mysql mysql       65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql     8636 2012-05-04 12:11 tbtest_part.frm
-rw-rw---- 1 mysql mysql       44 2012-05-04 12:11 tbtest_part.par
-rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd
-rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partB.ibd
-rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd
-rw-rw---- 1 mysql mysql 13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#
 

 

Preparing the table with partitions 

1
2
3
(root@localhost) [test_tablespace1]>FLUSH TABLE tbtest_part WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 

 

Copy files

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest_part*.* /home/mysql/backup1/

 

Recreate the fake tale with partitions
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `tbtest_part` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(3) DEFAULT 'AAA',
`dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`part` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (part)
(PARTITION partA VALUES IN (1) ENGINE = InnoDB,
PARTITION partB VALUES IN (2) ENGINE = InnoDB,
PARTITION partC VALUES IN (3) ENGINE = InnoDB,
PARTITION partD VALUES IN (4) ENGINE = InnoDB) */
 

 

I would like to perform discard on only ONE partition, but htere is no OPTION to do it,
I have to TRY to discard all of them the partition B.
1
2
3
4
5
6
7
-rw-rw---- 1 mysql mysql       8636 2012-05-04 12:11 tbtest_part.frm
-rw-rw---- 1 mysql mysql         44 2012-05-04 12:11 tbtest_part.par
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partA.ibd
-rw-rw---- 1 mysql mysql      98304 2012-05-05 20:36 tbtest_part#P#partB.ibd <----------------
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partC.ibd
-rw-rw---- 1 mysql mysql   13631488 2012-05-04 12:12 tbtest_part#P#partD.ibd
 

 

1
2
3
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part DISCARD TABLESPACE;
ERROR 1031 (HY000): TABLE storage engine FOR 'tbtest_part' doesn't have this option <------------ oops is not possible

 

But it is not working.
Too bad, I would love to have it!!!

Question 3:  Finally can I import a table space on a different table (with code modification)?

Fist of all I have change the code.
Create a new table, but I will use the old tablespace coming from tbtest_10MB
1
2
3
4
5
(root@localhost) [test_tablespace1]>CREATE TABLE `tbtest_IAMNEW` (   `a` int(11) NOT NULL DEFAULT '0',   `b` char(3) 
DEFAULT 'AAA',   `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.24 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_IAMNEW DISCARD TABLESPACE;
Query OK, 0 rows affected (0.08 sec)
 

 

Here the files as usual
-rw-rw---- 1 mysql mysql       8606 2012-05-06 18:28 tbtest_IAMNEW.frm
-rw-rw---- 1 mysql mysql      98304 2012-05-06 18:28 tbtest_IAMNEW.ibt
 

 

And now copy the old tablespace files in the new place
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.frm ./tbtest_IAMNEW.frm
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.ibd ./tbtest_IAMNEW.ibd
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.cfg ./tbtest_IAMNEW.cfg
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# chown mysql:mysql *
root@000033:/home/mysql/instances/my56testm/DATA/test_tablespace1# ll
total 1326476
drwx------ 2 mysql mysql       4096 2012-05-06 18:30 ./
drwxr-xr-x 4 mysql mysql       4096 2012-05-06 18:25 ../
-rw-rw---- 1 mysql mysql         65 2012-04-27 14:28 db.opt
-rw-r----- 1 mysql mysql        476 2012-05-05 19:06 tbtest_100MB.cfg
-rw-r----- 1 mysql mysql       8606 2012-05-05 19:06 tbtest_100MB.frm
-rw-r----- 1 mysql mysql   92274688 2012-05-05 19:17 tbtest_100MB.ibd
-rw-r----- 1 mysql mysql        475 2012-05-06 18:09 tbtest_10MB.cfg
-rw-rw---- 1 mysql mysql       8606 2012-05-06 18:09 tbtest_10MB.frm
-rw-r----- 1 mysql mysql   10485760 2012-05-06 18:10 tbtest_10MB.ibd
-rw-rw---- 1 mysql mysql      98304 2012-05-06 18:09 tbtest_10MB.ibt
-rw-r----- 1 mysql mysql        474 2012-05-05 19:06 tbtest_1GB.cfg
-rw-r----- 1 mysql mysql       8606 2012-05-05 19:06 tbtest_1GB.frm
-rw-r----- 1 mysql mysql 1203765248 2012-05-05 19:26 tbtest_1GB.ibd
-rw-r----- 1 mysql mysql        475 2012-05-06 18:30 tbtest_IAMNEW.cfg
-rw-rw---- 1 mysql mysql       8606 2012-05-06 18:30 tbtest_IAMNEW.frm
-rw-r----- 1 mysql mysql   10485760 2012-05-06 18:30 tbtest_IAMNEW.ibd
 

 

Finally do the real test
1
2
3
4
5
6
7
8
9
10
11
12
13
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_IAMNEW IMPORT TABLESPACE;

(root@localhost) [test_tablespace1]>check table tbtest_IAMNEW;

+--------------------------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------------------------+-------+----------+----------+

| test_tablespace1.tbtest_IAMNEW | check | status | OK |

+--------------------------------+-------+----------+----------+

1 row in set (0.05 sec)

 

Done.

Final test do it in a different Schema

Create Schema
1
2
3
4
5
6
7
8
9
10
11
(root@localhost) [test_tablespace1]>create schema test_tablespace2;
Query OK, 1 row affected (0.05 sec)
(root@localhost) [test_tablespace1]>use test_tablespace2
DATABASE changed
CREATE new TABLE
(root@localhost) [test_tablespace2]>CREATE TABLE `tbtest_IAMNEW` (   `a` int(11) NOT NULL DEFAULT '0',   `b` char(3) DEFAULT 'AAA',   `dat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.22 sec)
Do the process, we know it quite well now.
(root@localhost) [test_tablespace2]>ALTER TABLE tbtest_IAMNEW DISCARD TABLESPACE;
Query OK, 0 rows affected (0.03 sec)
 

 

Let test the real situation on FS

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.frm ../test_tablespace2/tbtest_IAMNEW.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.cfg ../test_tablespace2/tbtest_IAMNEW.cfg
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/tbtest_10MB.ibd ../test_tablespace2/tbtest_IAMNEW.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cd ../test_tablespace2
root@000033:/home/mysql/instances/my56testm/data/test_tablespace2# ll
total 10364
drwx------ 2 mysql mysql     4096 2012-05-06 18:47 ./
drwxr-xr-x 5 mysql mysql     4096 2012-05-06 18:46 ../
-rw-rw---- 1 mysql mysql       65 2012-05-06 18:46 db.opt
-rw-r----- 1 mysql mysql      475 2012-05-06 18:47 tbtest_IAMNEW.cfg
-rw-rw---- 1 mysql mysql     8606 2012-05-06 18:47 tbtest_IAMNEW.frm
-rw-r----- 1 mysql mysql 10485760 2012-05-06 18:47 tbtest_IAMNEW.ibd
 

 

I also Patch the schema name
1
2
root@000033:/home/mysql/instances/my56testm/data/test_tablespace2# vi tbtest_IAMNEW.cfg

 

1
2
3
4
5
6
7
8
9
10
11
(root@localhost) [test_tablespace2]>ALTER TABLE tbtest_IAMNEW IMPORT TABLESPACE;
Query OK, 0 rows affected (3.03 sec)
(root@localhost) [test_tablespace2]>show TABLE STATUS;
(root@localhost) [test_tablespace2]>check TABLE tbtest_IAMNEW;
+--------------------------------+-------+----------+----------+
| TABLE                          | Op    | Msg_type | Msg_text |
+--------------------------------+-------+----------+----------+
| test_tablespace2.tbtest_IAMNEW | CHECK | STATUS   | OK       |
+--------------------------------+-------+----------+----------+
1 row IN SET (0.06 sec)
 

 

Done !

Conclusion

The way we can play with tablespace without having to face the previous issue is definitely good.
The time IMPORT the partition is long and it could be really too long when use on huge tablespace.
Almost 9 minutes, for a 1GB tablespace, on a simple machine 4 CPU 4GB RAM, doing nothing else, is really too much.
But I am not complain, never the less for DBAs one of the most common use is related to PARTITIONS management, Backup/recovery and possible data move.
Given that having the PARTITIONs cut out is really nonsense for me.
Finally, also IF I understand that some user can do dangerous things moving table from one ORIGINAL TABLE to another, I see this feature only use from advance DBA.
Given that limiting it, and limiting the flexibility is again none sense for me.
I know we can hack the code in a very easy way, but this is not nice anyhow.
My Shopping list for this feature is the following:
  1. move tablespace from one object (table/schema) to another
  2. having the chance to DISCAR/IMPORT by partition
  3. make the process on IMPORTing the table space faster
Not really too much, not really complicate, but really helpful for DBAs.

{joscommentenable}

Commenti (1)
  • Mattias Jonsson  - EXCHANGE PARTITIONS WITH TABLE
    You can swap a partition with a table with 'ALTER TABLE t_part EXCHANGE PARTITION partX WITH TABLE t'

    That way you have the chance to DISCARD/IMPORT by partition.

    see more at:
    http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
  • Marco Tusa
    Mattias, thanks I know that, but for admin/recovery purpose that is not a valid solution, given you cannot push back a table into a partition right?
    So the method is one way only.
    What I was looking for is how to automate a possible disaster recovery script, base on the tablespace.

    Thanks for the comment, and let me know if I am wrong about the fact that I cannot push back a table to a partition. I mean right away not doing work around like, create truncate and fill it back.
  • Mattias Jonsson
    EXCHANGE PARTITION is two way :)

    So the data in partX will be the data in t, and the data in t will be the data in partX. As if they change places.

    That way you should be able to both export the data via a table, or import it into a partition via a table.
  • Marco Tusa
    Damn I miss it.. ;o) Will test asap.
    THANKS!
Solo gli utenti registrati possono inviare commenti!

!joomlacomment 4.0 Copyright (C) 2009 Compojoom.com . All rights reserved."

Ultimo aggiornamento Domenica 18 Agosto 2013 17:27
 
 

Who's Online

 23 visitatori online