Copying huge InnoDB table as a file?

We have a huge InnoDB table (153G) that we need to move to another server. We use innodb_file_per_table. Is there a way to copy just the IBD file somehow? How would target DB recognize the file and know about its metadata? If needed, we can stop the DB for the copying.

EDITED: One of the big tables is this:

CREATE TABLE `140M_table` (
  `field1` bigint(20开发者_Go百科) NOT NULL AUTO_INCREMENT,
  `field2` datetime DEFAULT NULL,
  `field3` varchar(255) DEFAULT NULL,
  `field4` int(11) DEFAULT NULL,
  `field5` varchar(255) DEFAULT NULL,
  `field6` varchar(255) DEFAULT NULL,
  `field7` int(11) DEFAULT NULL,
  `field8` bigint(20) DEFAULT NULL,
  `field9` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`field1`),
  KEY `captureDateIdx` (`field2`,`feild6`),
  KEY `personaSubIdx` (`field3`,`field6`,`field5`),
  KEY `FKE6B5072DF27AF1FE` (`field8`),
  KEY `FKE6B5072D54F7256A` (`field7`),
  KEY `temp_key_idx` (`field9`),
  CONSTRAINT `FKE6B5072D54F7256A` FOREIGN KEY (`field7`) REFERENCES `small_table`     (`field7`),
  CONSTRAINT `FKE6B5072DF27AF1FE` FOREIGN KEY (`field8`) REFERENCES `2M_table` (`field9`)
) ENGINE=InnoDB AUTO_INCREMENT=136181731 DEFAULT CHARSET=latin1

---

You can't just copy InnoDB tables. Even with innodb_file_per_table all InnoDB files share a central "cache" file (ibdata#, ib_logfile#), which are stored in the root level of the mysql data directory.

You need to copy ALL the InnoDB tables plus the shared cache files to the new server, or dump the table. Dumping the table can take quite a bit longer since you are "moving" the data 3 time (dump, copy, restore). Make sure your InnoDB settings in my.cnf are the same on both servers, otherwise you will get errors when you try to startup mysql on the new server.

---

With the help of partition the problem would be resolved as ubder:

CREATE TABLE test_part_innodb (c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=InnoDB
-> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2001) , PARTITION p2 VALUES LESS THAN (2002) ,
-> PARTITION p3 VALUES LESS THAN (2003) , PARTITION p4 VALUES LESS THAN (2004) ,
-> PARTITION p5 VALUES LESS THAN (2005) , PARTITION p6 VALUES LESS THAN (2006) ,
-> PARTITION p7 VALUES LESS THAN (2007) , PARTITION p8 VALUES LESS THAN (2008) ,
-> PARTITION p9 VALUES LESS THAN (2009) , PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p99 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.13 sec)



show create table test_part_innodb\G;
*************************** 1. row ***************************
Table: test_part_innodb
Create Table: CREATE TABLE `test_part_innodb` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)


delimiter //
CREATE PROCEDURE load_test_part_innodb()
begin
do
declare v int default 0;
while v do
insert into test_part_innodb
values (v,'testing partitions',adddate('2000-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end
// 
Query OK, 0 rows affected (0.00 sec)

delimiter ;
call load_test_part_innodb();
Query OK, 1 row affected (1 hour 11 min 25.19 sec)

select count(*) from test_part_innodb where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (0.22 sec)


show create view part_backup\G;
    *************************** 1. row ***************************
    View: part_backup
    Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `part_backup` AS select

    concat('mysqldump',' -u root',' -p ',' --no-create-info',' --where="',(case `p2`.`PARTITION_METHOD` when 'hash' then
    concat(`p2`.`PARTITION_EXPRESSION`,' % ',(select count(0) from `information_schema`.`partitions` `p3` where ((`p3`.`TABLE_SCHEMA` =
    `p2`.`TABLE_SCHEMA`) and (`p3`.`TABLE_NAME` = `p2`.`TABLE_NAME`))),' = ',(`p2`.`PARTITION_ORDINAL_POSITION` - 1)) when 'list' then
    concat(`p2`.`PARTITION_EXPRESSION`,' in (',`p2`.`PARTITION_DESCRIPTION`,')') when 'range' then
    concat(if((`p2`.`PARTITION_ORDINAL_POSITION` = 1),'',concat(`p2`.`PARTITION_EXPRESSION`,' >=
    ',replace(`p1`.`PARTITION_DESCRIPTION`,'MAXVALUE',~(0)),' and ')),concat(`p2`.`PARTITION_EXPRESSION`,'
    ',`p2`.`TABLE_SCHEMA`,'.',`p2`.`TABLE_NAME`) AS `Name_exp_1` from (`information_schema`.`partitions` `p2` left join
    `information_schema`.`partitions` `p1` on(((`p1`.`TABLE_SCHEMA` = `p2`.`TABLE_SCHEMA`) and (`p1`.`TABLE_NAME` = `p2`.`TABLE_NAME`) and

    ((`p1`.`PARTITION_ORDINAL_POSITION` + 1) = `p2`.`PARTITION_ORDINAL_POSITION`)))) where ((`p2`.`TABLE_SCHEMA` = database()) and
    (`p2`.`PARTITION_METHOD` in ('hash','list','range')))
    character_set_client: utf8
    collation_connection: utf8_general_ci
    1 row in set (0.01 sec)

desc part_backup;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| Name_exp_1 | mediumtext | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.02 sec)

select * from part_backup;