Does mysqldump export indices, by default?

Question

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:

--disable-keys, -K

which suggests, that it actually does export the indices. But I don't want to trust my interpretation and I want to make sure I got it right (or wrong ;-)). Can anyone confirm that?

Please refresh. I updated my answer with example mysqldump excerpts. — RolandoMySQLDBA, May 31 '11 at 14:50
Please refresh. I updated my answer with an additional caveat about DISABLE KEYS and ENABLE KEYS. — RolandoMySQLDBA, May 31 '11 at 14:55
Please refresh. I updated my answer with an additional caveats about bypassing DISABLE KEYS and dumping tablespaces. — RolandoMySQLDBA, May 31 '11 at 15:07
This question deserves a +1 because I am almost certain many have had this same question in mind but was afraid to ask. You asked, you get the +1 !!! — RolandoMySQLDBA, May 31 '11 at 15:38
@RolandoMySQLDBA: You put a lot of effort in your answer. Thank you very much!! — Aufwind, May 31 '11 at 16:28

RolandoMySQLDBA · Accepted Answer · 2011-07-27T17:49:26.830

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs:

DROP TABLE IF EXISTS `tblAccountLinks`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tblAccountLinks` (
  `ID` int(11) NOT NULL auto_increment,
  `FirmNo` varchar(10) NOT NULL,
  `CustomerNo` varchar(20) NOT NULL,
  `AccountNo` varchar(20) NOT NULL,
  `LinkType` smallint(6) NOT NULL,
  `AccessLevel` smallint(6) NOT NULL,
  `Status` smallint(6) NOT NULL,
  `CreatedOn` datetime NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=27023 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tblAccountLinks`
--

LOCK TABLES `tblAccountLinks` WRITE;
/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;
INSERT INTO `tblAccountLinks` VALUES (1,'F0001','C001','T00000001',1,2,1,'2008-06-30 07:55:43'),(2,'
F0001','C001','T00000002',2,2,1,'2008-06-30 07:55:43'),(3,'F0001','C002','27601012',1,2,1,'2008-06-3 ...

The line after LOCK TABLES is

/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;

This is what the --disable-keys option embeds in the mysqldump.

Also, this is embedded after all the INSERTs are done

/*!40000 ALTER TABLE `tblAccountLinks` ENABLE KEYS */;
UNLOCK TABLES;

CAVEAT #1

DISABLE KEYS and ENABLE KEYS were implemented to disable the reloading of nonunique indexes while a table is being reloaded. Primary Keys and Unique Keys are not disabled. They are loaded at the same moment the INSERTs are being. Once you ENABLE KEYS, the nonunique indexes are rebuilt via sorting (or using the MyISAM key cache is there isn't enough available memory)

Unfortunately, DISABLE KEYS and ENABLE KEYS only work for MyISAM tables, not InnoDB.

CAVEAT #2

You do not have to --disable-keys. You could disable DISABLE KEYS (no pun intended) them with --skip-disable-keys:

  -K, --disable-keys  '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
                      '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
                      in the output.
                      (Defaults to on; use --skip-disable-keys to disable.)

This could result in a slower load and a potential lopsiding of the index pages for nonunique indexes.

CAVEAT #3

You can dump the actual InnoDB tablespaces (MySQL 5.5.12)

  -Y, --all-tablespaces 
                      Dump all the tablespaces.
  -y, --no-tablespaces 
                      Do not dump any tablespace information.

Please note this is out of date, MySQL 5.7 does include indexes. — Erica Kane, Oct 18 '17 at 00:30

Does mysqldump export indices, by default?

1 Answers1