In MySQL 5.7 transportable tablespaces works at a partition level:
alter table <table> discard partition <partition> tablespace;
alter table <table> import partition <partition> tablespace;
This is the source database.
root [(none)]> create database arcdata;
Query OK, 1 row affected (0.00 sec)
This is the target database
root [(none)]> create database archive;
Query OK, 1 row affected (0.00 sec)
root [(none)]> use arcdata;
Database changed
root [arcdata]> CREATE TABLE athlete(id int(11) NOT NULL AUTO_INCREMENT,
-> fname varchar(25) NOT NULL,
-> lname varchar(25) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY RANGE (id)
-> (PARTITION p_track VALUES LESS THAN (1000) ENGINE = InnoDB,
-> PARTITION p_cycle VALUES LESS THAN (2000) ENGINE = InnoDB,
-> PARTITION p_row VALUES LESS THAN (3000) ENGINE = InnoDB,
-> PARTITION p_sail VALUES LESS THAN (4000) ENGINE = InnoDB,
-> PARTITION p_tennis VALUES LESS THAN (5000) ENGINE = InnoDB,
-> PARTITION p_boxing VALUES LESS THAN (6000) ENGINE = InnoDB,
-> PARTITION p_shooting VALUES LESS THAN (7000) ENGINE = InnoDB,
-> PARTITION p_judo VALUES LESS THAN (8000) ENGINE = InnoDB,
-> PARTITION p_horse VALUES LESS THAN (9000) ENGINE = InnoDB,
-> PARTITION p_catchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.15 sec)
root [arcdata]> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'athlete'; +----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p_track | 4 |
| p_cycle | 8 |
| p_row | 4 |
| p_sail | 1 |
| p_tennis | 4 |
| p_boxing | 1 |
| p_shooting | 1 |
| p_judo | 1 |
| p_horse | 1 |
| p_catchall | 0 |
+----------------+------------+
10 rows in set (0.00 sec)
root [arcdata]> flush table athlete for export;
Query OK, 0 rows affected (0.01 sec)
In the mysqld.log, you can mysql 5.7 creates a .cfg for each partition.
2014-11-20T14:44:15.514522Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.5-m15-log' socket: '/db/DB1/config/mysql.sock' port: 13306 MySQL Community Server (GPL)
2014-11-20T14:52:53.946206Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_track" */' started.
2014-11-20T14:52:53.946242Z 2 [Note] InnoDB: Stopping purge
2014-11-20T14:52:53.946281Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_track.cfg'
2014-11-20T14:52:53.946456Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_track" */' flushed to disk
2014-11-20T14:52:53.946508Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_cycle" */' started.
2014-11-20T14:52:53.946528Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_cycle.cfg'
2014-11-20T14:52:53.946589Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_cycle" */' flushed to disk
2014-11-20T14:52:53.946607Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_row" */' started.
2014-11-20T14:52:53.946621Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_row.cfg'
2014-11-20T14:52:53.946675Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_row" */' flushed to disk
2014-11-20T14:52:53.946720Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_sail" */' started.
2014-11-20T14:52:53.946738Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_sail.cfg'
2014-11-20T14:52:53.946834Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_sail" */' flushed to disk
2014-11-20T14:52:53.946853Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_tennis" */' started.
2014-11-20T14:52:53.946868Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_tennis.cfg'
2014-11-20T14:52:53.946923Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_tennis" */' flushed to disk
2014-11-20T14:52:53.946940Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_boxing" */' started.
2014-11-20T14:52:53.946973Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_boxing.cfg'
2014-11-20T14:52:53.947029Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_boxing" */' flushed to disk
2014-11-20T14:52:53.947047Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_shooting" */' started.
2014-11-20T14:52:53.947061Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_shooting.cfg'
2014-11-20T14:52:53.947122Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_shooting" */' flushed to disk
2014-11-20T14:52:53.947141Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_judo" */' started.
2014-11-20T14:52:53.947154Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_judo.cfg'
2014-11-20T14:52:53.947208Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_judo" */' flushed to disk
2014-11-20T14:52:53.947225Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_horse" */' started.
2014-11-20T14:52:53.947239Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_horse.cfg'
2014-11-20T14:52:53.947292Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_horse" */' flushed to disk
2014-11-20T14:52:53.947310Z 2 [Note] InnoDB: Sync to disk of '"arcdata"."athlete" /* Partition "p_catchall" */' started.
2014-11-20T14:52:53.947324Z 2 [Note] InnoDB: Writing table metadata to './arcdata/athlete#P#p_catchall.cfg'
2014-11-20T14:52:53.947377Z 2 [Note] InnoDB: Table '"arcdata"."athlete" /* Partition "p_catchall" */' flushed to disk
[mysql@mysql57db1 DB57 /db/DB1/data/arcdata ]$ ls -lrt
total 1060
-rw-rw---- 1 mysql mysql 61 Nov 20 14:46 db.opt
-rw-rw---- 1 mysql mysql 112 Nov 20 14:47 athlete.par
-rw-rw---- 1 mysql mysql 8620 Nov 20 14:47 athlete.frm
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_catchall.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_track.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_tennis.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_shooting.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_sail.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_row.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_judo.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_horse.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_cycle.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:47 athlete#P#p_boxing.ibd
-rw-rw---- 1 mysql mysql 461 Nov 20 14:56 athlete#P#p_track.cfg
-rw-rw---- 1 mysql mysql 462 Nov 20 14:56 athlete#P#p_tennis.cfg
-rw-rw---- 1 mysql mysql 464 Nov 20 14:56 athlete#P#p_shooting.cfg
-rw-rw---- 1 mysql mysql 460 Nov 20 14:56 athlete#P#p_sail.cfg
-rw-rw---- 1 mysql mysql 459 Nov 20 14:56 athlete#P#p_row.cfg
-rw-rw---- 1 mysql mysql 460 Nov 20 14:56 athlete#P#p_judo.cfg
-rw-rw---- 1 mysql mysql 461 Nov 20 14:56 athlete#P#p_horse.cfg
-rw-rw---- 1 mysql mysql 461 Nov 20 14:56 athlete#P#p_cycle.cfg
-rw-rw---- 1 mysql mysql 464 Nov 20 14:56 athlete#P#p_catchall.cfg
-rw-rw---- 1 mysql mysql 462 Nov 20 14:56 athlete#P#p_boxing.cfg
Copy the required .ibd and .cfg to another location. During this process the table is locked.
[mysql@mysql57db1 DB57 /db/DB1/data/arcdata ]$ cp athlete#P#p_cycle.cfg athlete#P#p_cycle.ibd /home/mysql/tempfiles/
root [arcdata]> unlock tables;
Query OK, 0 rows affected (0.00 sec)In the mysqld.log
2014-11-20T14:58:35.347060Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_track.cfg'
2014-11-20T14:58:35.347138Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_cycle.cfg'
2014-11-20T14:58:35.347187Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_row.cfg'
2014-11-20T14:58:35.347254Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_sail.cfg'
2014-11-20T14:58:35.347294Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_tennis.cfg'
2014-11-20T14:58:35.347350Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_boxing.cfg'
2014-11-20T14:58:35.347394Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_shooting.cfg'
2014-11-20T14:58:35.347447Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_judo.cfg'
2014-11-20T14:58:35.347487Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_horse.cfg'
2014-11-20T14:58:35.347529Z 3 [Note] InnoDB: Deleting the meta-data file './arcdata/athlete#P#p_catchall.cfg'
2014-11-20T14:58:35.347538Z 3 [Note] InnoDB: Resuming purge
In the source database.
root [arcdata]> use archive
Database changed
root [archive]> CREATE TABLE athlete(id int(11) NOT NULL AUTO_INCREMENT,
-> fname varchar(25) NOT NULL,
-> lname varchar(25) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY RANGE (id)
-> (PARTITION p_track VALUES LESS THAN (1000) ENGINE = InnoDB,
-> PARTITION p_cycle VALUES LESS THAN (2000) ENGINE = InnoDB,
-> PARTITION p_row VALUES LESS THAN (3000) ENGINE = InnoDB,
-> PARTITION p_sail VALUES LESS THAN (4000) ENGINE = InnoDB,
-> PARTITION p_tennis VALUES LESS THAN (5000) ENGINE = InnoDB,
-> PARTITION p_boxing VALUES LESS THAN (6000) ENGINE = InnoDB,
-> PARTITION p_shooting VALUES LESS THAN (7000) ENGINE = InnoDB,
-> PARTITION p_judo VALUES LESS THAN (8000) ENGINE = InnoDB,
-> PARTITION p_horse VALUES LESS THAN (9000) ENGINE = InnoDB,
-> PARTITION p_catchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.12 sec)
root [archive]> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'athlete' and TABLE_SCHEMA = 'archive';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p_track | 0 |
| p_cycle | 0 |
| p_row | 0 |
| p_sail | 0 |
| p_tennis | 0 |
| p_boxing | 0 |
| p_shooting | 0 |
| p_judo | 0 |
| p_horse | 0 |
| p_catchall | 0 |
+----------------+------------+
10 rows in set (0.00 sec)
This the change in change in mysql 5.7. Please note the partition keyword.
root [archive]> alter table athlete discard partition p_cycle tablespace;
Query OK, 0 rows affected (0.00 sec)
Nothing in the alert log. The .ibd for the partition has now been deleted.
[mysql@mysql57db1 DB57 /db/DB1/data/archive ]$ ls -lrt
total 920
-rw-rw---- 1 mysql mysql 61 Nov 20 14:47 db.opt
-rw-rw---- 1 mysql mysql 112 Nov 20 14:59 athlete.par
-rw-rw---- 1 mysql mysql 8620 Nov 20 14:59 athlete.frm
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_track.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_tennis.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_shooting.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_sail.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_row.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_judo.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_horse.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_catchall.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_boxing.ibd
Move the source partition .ibd and .cfg to the require data directory.
[mysql@mysql57db1 DB57 /db/DB1/data/archive ]$ cp /home/mysql/tempfiles/* .
[mysql@mysql57db1 DB57 /db/DB1/data/archive ]$ ls -lrt
total 1024
-rw-rw---- 1 mysql mysql 61 Nov 20 14:47 db.opt
-rw-rw---- 1 mysql mysql 112 Nov 20 14:59 athlete.par
-rw-rw---- 1 mysql mysql 8620 Nov 20 14:59 athlete.frm
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_track.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_tennis.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_shooting.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_sail.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_row.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_judo.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_horse.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_catchall.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 14:59 athlete#P#p_boxing.ibd
-rw-rw---- 1 mysql mysql 98304 Nov 20 15:05 athlete#P#p_cycle.ibd
-rw-rw---- 1 mysql mysql 461 Nov 20 15:05 athlete#P#p_cycle.cfgroot [archive]> alter table athlete import partition p_cycle tablespace;
Query OK, 0 rows affected (0.03 sec)
In the mysqld.log
2014-11-20T15:07:52.469353Z 3 [Note] InnoDB: Importing tablespace for table 'arcdata/athlete#P#p_cycle' that was exported from host 'mysql57db1'
2014-11-20T15:07:52.469494Z 3 [Note] InnoDB: Phase I - Update all pages
2014-11-20T15:07:52.469969Z 3 [Note] InnoDB: Sync to disk
2014-11-20T15:07:52.475235Z 3 [Note] InnoDB: Sync to disk - done!
2014-11-20T15:07:52.475842Z 3 [Note] InnoDB: Phase III - Flush changes to disk
2014-11-20T15:07:52.483011Z 3 [Note] InnoDB: Phase IV - Flush complete
2014-11-20T15:07:52.483222Z 3 [Note] InnoDB: "archive"."athlete" /* Partition "p_cycle" */ autoinc value set to 1801
root [archive]> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'athlete';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p_track | 0 |
| p_cycle | 0 |
| p_row | 0 |
| p_sail | 0 |
| p_tennis | 0 |
| p_boxing | 0 |
| p_shooting | 0 |
| p_judo | 0 |
| p_horse | 0 |
| p_catchall | 0 |
+----------------+------------+
10 rows in set (0.00 sec)
At this point I expected to see the rows in the table. I have seen this issue before and decided to analyse the table.
root [archive]> analyze table athlete;
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'athlete';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p_track | 0 |
| p_cycle | 8 |
| p_row | 0 |
| p_sail | 0 |
| p_tennis | 0 |
| p_boxing | 0 |
| p_shooting | 0 |
| p_judo | 0 |
| p_horse | 0 |
| p_catchall | 0 |
+----------------+------------+
10 rows in set (0.01 sec)
I would say at this point that MySQL 5.7 is not GA. The version I used is5.7.5-m15-log MySQL Community Server (GPL).
Also I was tasked with moving a partition in MySQL 5.6 using transportable tablespaces and worked around the issue by create a temporary table with the data required and then transporting that tablespace and to target and then inserting the data from the temporary table to the source table. This seemed safer as I wasn't dealing with the temporary tables. Now I am dealing with source data, so have to be more careful.
No comments:
Post a Comment