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:
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
- take the table creation to replicate the structure
SHOW CREATE TABLE tbtest\G
- Lock the table to copy it
FLUSH TABLE tbtest WITH READ LOCK;
- Copy somewhere the files DON'T forget the .cnf
- UNLOCK TABLES;
- Drop the table
- Create a fake table using the create statement stored before
- Detach the table
- ALTER TABLE tbtest DISCARD TABLESPACE;
- MOVE !!! the *.ibt file in a safe place
- Copy over the previous files from the backup directory
- CHECK PERMISSION!!
- Import back the table space
- ALTER TABLE tbtest IMPORT TABLESPACE;
- check table;
Это в "Прошивка билайн е300"одном из старых каменных домов, которые "Старый телефон звонок скачать"разбиты на отдельные модули. В последние дни все шло ""как-то не так, но он знал, что должно сейчас "Скачать аим для css v34 самый мощный"сделать его тело. Он решил просто увертываться от него, ""пока противник совсем не обессилит. Дай мне Грейсвандир, ""сказал он. Опершись на левый локоть и вглядевшись, я "Скачать христианские клипы"увидел, что она ""стоит между мной и Знаком Лабиринта, который "Заказать статью для а"висит в воздухе, наверное в десяти "Hd2 прошивка андроид"шагах от меня. Будем считать это критикой, заметил Люк. |
(Unknown Country?)