Have a mysqldump with Indexes. How do I import it by disabling them?

Question

I am trying to import a large database but because there's an index, it is taking forever to import the database. I tried copying the raw files to a different machine but for some reason, the largest table is not being read giving me an error: can't read file: 'tablename.MYD. So I mysqldumped my old database but it did so with an index. I cannot do it again because it takes a really long time.

I am trying to import the database but am not sure how to do it without indexes. I thought adding an index after import will be much faster. Does anyone have any suggestions?

Answer 1

To disable the indexes and import from a dump file:

alter table tablename disable keys

And then when the import is done:

alter table tablename enable keys

Although copying the file in the manner you mentioned should work. You just have a permission error. On the shell do:

chown mysql.mysql tablename.* and then restart your database. (make sure you are in the folder the table exists at when you use that command) That should fix the issue.

Edit: clarified where you should use the command

Answer 2

By default, mysqldump produces SQL that disables indexes during mass import and applies them afterward. It looks like this:

/*!40000 ALTER TABLE `my_table` DISABLE KEYS */;
INSERT INTO `my_table` VALUES (...), (...);
/*!40000 ALTER TABLE `my_table` ENABLE KEYS */;