I am moving our current database server from a regular EC2 instance running mysql to a RDS instance. I moved all the databases using the amazon-suggested method, simply piping the mysqldump output to a mysql client connected to the remote database (RDS instance). Everything worked fine for most databases, except one. After searching, the cause is a big, 13 Gb table that starts copying OK, but after some time the mysql client throws an error (sadly I didn't take note of the error code when it happened). It was "Connection lost" for sure.
This is the table:
CREATE TABLE `UniqueTextLookup` (
`UniqueTextLookupID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`TextID` CHAR(32) NOT NULL COLLATE 'ascii_bin',
`CrcTextID` INT(10) UNSIGNED NOT NULL,
`UniqueText` CHAR(255) NOT NULL,
`CrcUniqueText` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`UniqueTextLookupID`),
UNIQUE INDEX `TextID` (`TextID`),
INDEX `UniqueText` (`UniqueText`(10)),
INDEX `CrcTextID` (`CrcTextID`),
INDEX `CrcUniqueText` (`CrcUniqueText`)
)
I suspect that the table size has something to do with the indexes more than the data (it's just a lookup table for unique texts, with some hacks to accelerate queries using CRC32 of some columns). It has 15 millon rows.
Any Ideas? I'm running the dump+pipe again to pinpoint the error code.
Some notes:
I cannot login by ssh to the RDS instance, so dumping to a file and copying by scp to the RDS server is not an option.
EDIT:
Checking with mytop It looks like this query (one of the lasts of the dump) :
/*!40000 ALTER TABLE `UniqueTextLookup` ENABLE KEYS */
gets stalled, it has been running for 322 seconds and counting...