Convert partitioned MRG_MYISAM table to Innodb

I want to convert a partitioned MYISAM table (MRG_MYISAM) that has over 110 million rows (in around 60 partition tables) into Innodb.

Is there a safe way to do this? (e.g. Alter Table ....)

Or should I prepare a dump (which will take 10s of gigs of disk, and it might never finish)

Solution

I tried the example MERGE table on this page: https://dev.mysql.com/doc/refman/5.5/en/merge-storage-engine.html

CREATE TABLE `total` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `message` char(20) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 INSERT_METHOD=LAST UNION=(`t1`,`t2`)

And I converted it:

ALTER TABLE total ADD PRIMARY KEY (a),
  ENGINE=InnoDB
  PARTITION BY HASH(a) PARTITIONS 4;

I had to make sure there would be no duplicates in column a first.

The resulting table is:

CREATE TABLE `total` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `message` char(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (a)
PARTITIONS 4

So it still has the redundant index on column a, but otherwise it seems to have worked.

Note that this does require extra storage space. The original tables that the merge table was defined to union still exist.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| total          |
+----------------+

Any further changes to data in t1, t2 will NOT become part of the total table.