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.
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.
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...