google
 
 
Home MySQL Blogs
MySQL

My MySQL tipsvalid-rss-rogers




01
Ott
2012
Another day at MySQL connect 2012 PDF Stampa E-mail
Scritto da Marco Tusa   

Today was another good day, but I had a problem with one presentation and keynote.

the "Big Data is a Big Scam: Most of the Time". There is something that is not convincing me in the architecture,

and is mainly base on the lack of numbers and match between requirements and implementation.

 

The presentation was interesting from some aspects, and the tile approach is probably a good one.

 

I have enjoy the general idea and I did found awesome the bravery of setting up a circular replication cross geographic regions.

 

Never the less I remain bewildered by the tiles approach numbers, probably because the presentation was missing of few additional information.

 

Just to be more clear I was trying to understand better the real number and how works.

 

As for example if each tile is compose by 1 MySQL node, and 2 data node, and given that this solution use Xtra large EC2 with 68GB ram. I can assume that,

we have approximately 65 GB data (given some overhead for buffers and so on) for the current Node Group on this tile.

From the description I have also understood that the number of tails implemented, is different by region.

 

Given that the number of Node Group is different and by direct consequence the memory available as well, and the MySQL SQL node as well.

 

This raise an immediate question, are we having the same data set replicated world wide?

 

Or are we having a different sets?

 

Also,

each Node Group can host ~69GB, the maximum number of data node in 7.2 is still 48 (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html), which means that the maximum size that can be served is of 1.3TB for the whole cluster.

 

Given during the presentation my understanding was that the data set is of 100TB, where are located, now or in the future, the remaining 98.7TB? Keeping in mind that I asks if table on disks where used and answer was NO.

Then I have another question-mark infront of me.

If the set of tiles in US is compose of 20 tiles, (so 20 MySQL SQL Nodes, 40 Data nodes and so on), and it set like this because it needs to sustain the traffic mainly it requires that setting for "calculation power" needs.

I assume (probably wrong) that this is due to the number of request per second coming in.

 

Given we have circular replication in the architecture, and given we have different numbers of tiles, and by consequence of MySQL nodes, HOW can a 8 MySQL SQL Node setup (say in Australia) sustain the traffic coming in from a 20 MySQL SQL nodes setup?

 

I am sure, we are missing something in the presentation in terms of real numbers.

 

Being a MySQL NDB lover, I would really like to have more information and details, given that what I get from the presentation was not enough for me.

 

{joscommentenable}

Ultimo aggiornamento Domenica 18 Agosto 2013 17:24
 
22
Lug
2012
MySQL 5.6.6 not compile on Mac PDF Stampa E-mail
Scritto da Marco Tusa   

Today, I had the need to debug mysql, given that I download the code and I try to compile on my MacBook pro.

Surprise I got the following issue:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC - Success
-- Check size of pthread_t
-- Check size of pthread_t - done
-- Performing Test HAVE_PEERCRED
-- Performing Test HAVE_PEERCRED - Failed
-- GTEST_LIBRARIES:optimized;/Users/marcotusa/mysql_work/source/gtest-1.6.0/test;debug;/Users/marcotusa/mysql_work/source/gtest-1.6.0
-- Configuring incomplete, errors occurred!
tusamac:mysql-5.6.5-m8 marcotusa$ cmake . -G Xcode -DCMAKE_INSTALL_PREFIX:PATH=/Users/marcotusa/mysql_work/template/mysql-5.6.5
 -DCMAKE_USE_RELATIVE_PATHS=1 -DDEBUG_EXTNAME=1 -DDISABLE_SHARED=OFF -DENABLE_DOWNLOADS=1 -DWITHOUT_SERVER=0
 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_DEBUG=1 -DWITH_EMBEDDED_SERVER=0
 -DWITH_EXTRA_CHARSETS=all -DWITH_FAST_MUTEXES=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_LIBEDIT=1 -DWITH_LIBWRAP=1
-DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 
-- MySQL 5.6.5-m8
CMake Error at configure.cmake:196 (LIST):
  list sub-command REMOVE_DUPLICATES requires list to be present.
Call Stack (most recent call first):
  CMakeLists.txt:262 (INCLUDE)
 

 

Obviously on linux all works fine.

After having fight a little by myself, I start to look around and I have found that bug 65050 was talking about it.

But for version 5.5. and is marked as Critical.

 

Anyhow I review the patch describe there, and look in the file configure.cmake.

Obviously the lines were different but once identify the right position: line 196 in 5.6.6 m8.

Replacing the line

196
LIST(REMOVE_DUPLICATES CMAKE_REQUIRED_LIBRARIES)

 

with 

196
197
198
  IF(CMAKE_REQUIRED_LIBRARIES)
   LIST(REMOVE_DUPLICATES CMAKE_REQUIRED_LIBRARIES)
  ENDIF()

 

I run again the cmake and finally I got :

1
2
3
4
5
6
7
8
9
10
11
tusamac:mysql-5.6.5-m8 marcotusa$ cmake . -G Xcode -DCMAKE_INSTALL_PREFIX:PATH=/Users/marcotusa/mysql_work/template/mysql-5.6.5 
-DCMAKE_USE_RELATIVE_PATHS=1 -DDEBUG_EXTNAME=1 -DDISABLE_SHARED=OFF -DENABLE_DOWNLOADS=1
 -DWITHOUT_SERVER=0 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DWITH_DEBUG=1 -DWITH_EMBEDDED_SERVER=0 -DWITH_EXTRA_CHARSETS=all -DWITH_FAST_MUTEXES=1
 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_LIBEDIT=1
 -DWITH_LIBWRAP=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_UNIT_TESTS=1
-- MySQL 5.6.5-m8
-- C warning options: -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wdeclaration-after-statement
-- C++ warning options: -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wno-unused-parameter -Woverloaded-virtual
-- Successfully downloaded http://googletest.googlecode.com/files/gtest-1.5.0.tar.gz to /Users/marcotusa/mysql_work/source/mysql-5.6.5-m8/source_downloads
-- GTEST_LIBRARIES:gtest
-- Configuring done
-- Generating done
-- Build files have been written to: /Users/marcotusa/mysql_work/source/mysql-5.6.5-m8
tusamac:mysql-5.6.5-m8 marcotusa$ 
 

 

Easy and clean.

 

Thank you to : Mike McQuaid for identifying the issue and submit the patch

And shame on MySQL/Oracle for having Mac OS/X so out of the radar.

{joscommentenable}

Ultimo aggiornamento Domenica 18 Agosto 2013 17:26
 
08
Mag
2012
Portable table space part III or We can do it (with partition) PDF Stampa E-mail
Scritto da Marco Tusa   

Portable table space part III or We can do it (with partition)

First of all, I have to say that as usual I did the three investigation, because I had fun.

MySQL is still fun for me, and also if I am not touching the magic topic of the moment like SSD,

replication and so on, I enjoy what I was doing, and I see how I can save my customer data, with this.

Second, I thanks again the people who helps me in understanding better MySQL, in this case Mattias Jonsson, who points me in right direction.

 

The Solution

The solution is a little bit cumbersome, but it works.

I have assume that:

  • a customer have a data-set which contains a partitioned table
  • we can have short period of read only data

To use the DISCARD tablespace on a partition the process is:

  1. - LOCK the table with partition and the final table where I will put the data
  2. - EXCHANGE the table with the partition
  3. - Flush the table
  4. - save the files
  5. - EXCHANGE AGAIN table with partition
  6. - reload the record IF ANY from the table I have move back to the partition (delta insert during the first exercise)
  7. - Flush again
  8. - save files

To recover do the usual procedure but against the table then EXCHANGE it with the partition.

It is obvious that more time pass more DATA will differ from the saved tablespace and the live data set, so longer it will take to reload the data.

Anyhow below the full process in detail, have fun.

Process details

INITIAL data set

 

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
(root@localhost) [test_tablespace1]>select * FROM partD;
Empty SET (0.00 sec)
(root@localhost) [test_tablespace1]>show CREATE TABLE tbtest_part \G
*************************** 1. row ***************************
TABLE: tbtest_part
CREATE TABLE: 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) */
1 row IN SET (0.02 sec)
(root@localhost) [test_tablespace1]>select COUNT(*) FROM tbtest_part WHERE part=4;
+----------+
| COUNT(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.95 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>create TABLE parD SELECT * FROM tbtest_part LIMIT 0;
 

 

I have to exchange the PARTITION and then use the delta to fill the difference.

lock tables tbtest_part write, partD write;   then run the  ALTER TABLE tbtest_part EXCHANGE PARTITION partD with table partD;

 

 

1
2
3
4
5
6
7
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (0.32 sec)
(root@localhost) [test_tablespace1]>unlock TABLES;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test_tablespace1]>FLUSH TABLE partD WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 

 

Time to save the files in the backup directory

 

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 1407388
drwx------ 2 mysql mysql 4096 2012-05-07 17:16 ./
drwxr-xr-x 5 mysql mysql 4096 2012-05-07 11:41 ../
-rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql 523 2012-05-07 17:16 partD.cfg
-rw-rw---- 1 mysql mysql 8636 2012-05-07 11:41 partD.frm
-rw-rw---- 1 mysql mysql 96468992 2012-05-07 11:45 partD.ibd
-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
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.* /home/mysql/backup/

 

 

Let assume that during a period of time N the table will get additional 600 records

such that when we bring it back we have to reinsert them.

 

 

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
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.46 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.94 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM partD;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961464 |
+----------+
1 row IN SET (0.94 sec)
 

 

Good all data recover, now we must do something to preserver our set, we must save again the partD table to preserve the delta

we miss during the EXCHANGE.

We must also rename the table's files.

 

 

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.ibd /home/mysql/backup/parDdelta.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.frm /home/mysql/backup/parDdelta.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.cfg /home/mysql/backup/parDdelta.cfg

 

 

 

Assume that we have a disaster and data is lost, we will simulate truncating the partition

 

1
2
3
4
5
6
7
8
9
10
(root@localhost) [test_tablespace1]>alter TABLE tbtest_part TRUNCATE partition partD;
Query OK, 0 rows affected (0.27 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row IN SET (0.00 sec)

 

 

Help my data is lost!!!

And assume I still have insert until I put the table on reed only (hoping I can doit at elast for short time):

 

1
2
3
4
5
6
7
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row IN SET (0.00 sec)

 

 

Now we have to:

  1. re-attach the old dataset to a new table and exchange with the partitioned table.
  2. save new data in a mew table
  3. re-attach the tablespace to partionD and reload the data;
  4. reload the delta between crash and new inserts

let's go:

1) Do the different steps to have the original data back

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
(root@localhost) [test_tablespace1]>create TABLE partD SELECT * FROM partD_temp LIMIT 0;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE partD DISCARD tablespace;
Query OK, 0 rows affected (0.03 sec)

Copy old TABLE space FROM my BACKUP area TO DATA directory
(root@localhost) [test_tablespace1]>ALTER TABLE partD IMPORT tablespace;
Query OK, 0 rows affected (30.94 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.43 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.93 sec)

 

 

Finally save the DELTA of the data in a different table to do not overwrite it.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(root@localhost) [test_tablespace1]> RENAME TABLE partD TO partD_AFTER_EMERGENCY;
Query OK, 0 rows affected (0.08 sec)
2) reload DATA of the partD delta (the 600 entries we saved before)
(root@localhost) [test_tablespace1]>create TABLE parDdelta SELECT * FROM tbtest_part LIMIT 0;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta DISCARD tablespace;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta IMPORT tablespace;
Query OK, 0 rows affected (2.95 sec)
3) reload the DATA FROM the reattached tablespace
(root@localhost) [test_tablespace1]>select count(*) FROM parDdelta;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM parDdelta;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0

 

 

4) reload the missed delta after the EMERGENCY

 

1
2
3
4
5
6
7
8
9
10
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD_AFTER_EMERGENCY;
Query OK, 200 rows affected (0.06 sec)
Records: 200 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961664 |
+----------+
1 row IN SET (0.94 sec)

 

 

Done the whole dataset is rebuild.

 

Conclusion

It is possible to have per PARTITION tablespace backup using DISCARD/IMPORT and EXCHANGE.

It is also possible to recover any delta that the application will insert in the meanwhile.

It is obvious that during th process the dataset will be inconsistent, so the best thing to do is to avoid inserts.

Copy the specific tablespace will be much easier and faster, then reloading from a full backup, if the issue is limited to a single tablespace or partition.

What I have done here manually must be automize using a script, that will simplify the process and make it much faster.

 

I hope you enjoy the whole as I did during the tests.

 

 

 

{joscommentenable}

Ultimo aggiornamento Domenica 18 Agosto 2013 17:26
 
07
Mag
2012
Portable Tablespace in InnoDB I test it PART2 PDF Stampa E-mail
Scritto da Marco Tusa   

 

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}

Ultimo aggiornamento Domenica 18 Agosto 2013 17:27
 
27
Apr
2012
Portable Tablespace in InnoDB I test it! PDF Stampa E-mail
Scritto da Marco Tusa   

 Overview

I have recently blog on the company site about portable Tablespaces

 

What I was saying is that is one of the things that could make us, people who work with MySQL/InnoDB happy.
This because it is a useful feature for administration and not just a "cool" thing to have.
My words were "This is a huge improvement that only people working daily with MySQL/InnoDB can understand,
so far it is still in the lab version but we all really hope to have it deliver with the new MySQL 5.6 GA"
From there I decide to try it right away, and to also try to extend the test.
So what I have done is to take the MySQL version from lab and start to play with tables and tablespaces.
Below my results and final considerations.
After having downloaded and install the Lab version
(root@localhost) [(none)]>status;
--------------
/home/mysql/templates/mysql-56p/bin/mysql  Ver 14.14 Distrib 5.6.6-labs-april-2012, for linux2.6 (i686) using  EditLine wrapper
 
Connection id:        1
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.6-labs-april-2012-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /home/mysql/instances/my56testm/mysql.sock
Uptime:            21 sec
 
Threads: 1  Questions: 5  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.238

 

Real work now and create a schema, tables and feed them.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
(root@localhost) [(none)]>use test_tablespace1
Database changed
(root@localhost) [test_tablespace1]>show tables;
Empty set (0.00 sec)
(root@localhost) [test_tablespace1]>create table tbtest(a int auto_increment PRIMARY KEY, 
b char(3) DEFAULT 'AAA', dat TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.24 sec) (root@localhost) [test_tablespace1]> (root@localhost) [test_tablespace1]>insert into tbtest (b) values ('aaa'),('bbb'),('ccc'),('dddd'); Query OK, 4 rows affected, 1 warning (0.05 sec) Records: 4 Duplicates: 0 Warnings: 1 (root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest; Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 .... (root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest; Query OK, 4096 rows affected (0.44 sec) Records: 4096 Duplicates: 0 Warnings: 0  

 

Now what we do have on fs ?
1
2
3
4
5
6
rwx------ 2 mysql mysql   4096 2012-04-27 14:42 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

Time to RAISE THE COMMAND
1
2
(root@localhost) [test_tablespace1]>FLUSH TABLES tbtest WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

 

Check on the file system to see the new cfg file
1
2
3
4
5
6
7
drwx------ 2 mysql mysql   4096 2012-04-27 14:51 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg <--------------IS THERE!!!
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

And given I am curious, I read insight:
we have there:
1
2
3
4
5
6
7
8
Name of the machine:     000033
Schema/Table name:       test_tablespace1/tbtest
Table definition:        a, b, dat
last ROW_ID:             DB_ROW_ID
Transaction ID:          DB_TRX_ID
Rollback pointer:        DB_ROLL_PTR
Primary kye and value :  PRIMARY a
Last transaction valuea: DB_TRX_ID DB_ROLL_PTR b dat

 

Now let us copy then remove it and see what happens.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# mkdir -p /home/mysql/backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest.* /home/mysql/backup/
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 380
drwx------ 2 mysql mysql   4096 2012-04-27 14:59 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll /home/mysql/backup
total 376
drwxr-xr-x  2 root  root    4096 2012-04-27 14:59 ./
drwxrwxr-- 30 mysql mysql   4096 2012-04-27 14:59 ../
-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
root@000033:/home/mysql/instances/my56testm/data/test_tablespace
 

 

I have the files in the backup dir
Now is time to unlock the tables:
1
UNLOCK TABLES;

 

And check what happened:
1
2
3
4
5
6
drwx------ 2 mysql mysql   4096 2012-04-27 15:01 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
 

 

No more cfg file.
Copy the table to something else and Drop the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(root@localhost) [test_tablespace1]>create table tbtest2 select * from tbtest;
Query OK, 8192 rows affected (1.02 sec)
Records: 8192  Duplicates: 0  Warnings: 0
(root@localhost) [test_tablespace1]>
Drop tbtest:
(root@localhost) [test_tablespace1]>drop table tbtest;
Query OK, 0 rows affected (0.08 sec)
(root@localhost) [test_tablespace1]>show tables;
+----------------------------+
| Tables_in_test_tablespace1 |
+----------------------------+
| tbtest2                    |
+----------------------------+
1 row in set (0.00 sec)
 

 

Create a fake table tbtest
1
2
3
4
5
6
7
8
9
10
11
12
13
(root@localhost) [test_tablespace1]>create table tbtest(a char(1));
Query OK, 0 rows affected (0.23 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>
drwx------ 2 mysql mysql   4096 2012-04-27 15:05 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-rw---- 1 mysql mysql   8554 2012-04-27 15:04 tbtest.frm
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt <----------- DETACHED tablespace
 

 

COPY back the files:
1
2
3
4
5
6
7
8
9
10
11
12
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/* .
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 root  root     440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 root  root  360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Fix permissions
1
2
3
4
5
6
7
8
9
10
11
12
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# chown mysql:mysql tbtest.*
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 mysql mysql    440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 mysql mysql 360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Remove fake table space and import back the old one
1
2
3
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# rm -f tbtest.ibt
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
ERROR 1801 (HY000): InnoDB: Number of columns don't match, table has 4 columns but the tablespace meta-data file has 6 columns

 

Error, well that was easy to understand, my fault I was suppose to create a fake table with the same structure not different.
Let me repeat the process of the fake table.
I did:
  • attach the ibt table space (I had it saved in backup)
  • drop it
  • recreate table as for initialal structure
  • detach it again.
  • copy back the old idb file and cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
Query OK, 0 rows affected (2.93 sec)
(root@localhost) [test_tablespace1]>check table tbtest;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| test_tablespace1.tbtest | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.02 sec)
(root@localhost) [test_tablespace1]>select count(*) from tbtest;
+----------+
| count(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.00 sec)

 

WOW it works that could make me happy but given I am never happy (enough)
Let me try a crazy thing.
  • Create a fake table tbtest3
  • change the info in the cfg file and the table space filename
  • try to import it.
Let's go ...
First copy and modify the cfg file:
1
2
3
root@000033:/home/mysql/backup# cp tbtest.ibd tbtest3.ibd
root@000033:/home/mysql/backup# cp tbtest.cfg tbtest3.cfg
root@000033:/home/mysql/backup# vi tbtest3.cfg

 

Try to attach it:
1
2
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest3 IMPORT TABLESPACE;
ERROR 1712 (HY000): InnoDB: While reading table name: 'I/O error'.

 

No luck, it would have being to cool and easy.

Ok so Conclusions

====================
1) we can now do export - import of table spaces a little bit more easily
2) we cannot move tablespace cross schemas
3) we cannot attach a tablespace to another table
I understand that would be too cool and we must accept what we have, and it would be also nice to take a look at the code.

The full procedure in a thumb

========================================
Assuming you already have the table in place
  1. take the table creation to replicate the structure
    SHOW CREATE TABLE tbtest\G
  2. Lock the table to copy it
    FLUSH TABLE tbtest WITH READ LOCK;
  3. Copy somewhere the files DON'T forget the .cnf
  4. UNLOCK TABLES;
  5. Drop the table
  6. Create a fake table using the create statement stored before
  7. Detach the table
  8. ALTER TABLE tbtest DISCARD TABLESPACE;
  9. MOVE !!! the *.ibt file in a safe place
  10. Copy over the previous files from the backup directory
  11. CHECK PERMISSION!!
  12. Import back the table space
  13. ALTER TABLE tbtest IMPORT TABLESPACE;
  14. check table;

 

{joscommentenable}



Ultimo aggiornamento Domenica 18 Agosto 2013 17:28
 
«InizioPrec.12345678Succ.Fine»

JPAGE_CURRENT_OF_TOTAL
 

Who's Online

 37 visitatori online