• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5576
  • Last Modified:

mysql DUMP without/of indexes

How can i take mysqldump without indexes ?
and also is there any way to only dump out the indexes ?
0
K V
Asked:
K V
  • 3
  • 2
1 Solution
 
todd_farmerCommented:
There is no option for mysqldump to include/exclude indexes independently from the rest of the table definitions.  You would need to drop them after they are created, or use a text editor to delete those lines in the resulting text file (they should be pretty easy to identify as they will start with PRIMARY KEY, UNIQUE KEY or KEY, I believe).
0
 
K VDatabase ConsultantAuthor Commented:
oh really!! even i was having the same idea but this article has confused me a lot...
it shows clearly that its possible.
http://node1.yo-linux.com/cgi-bin/man2html?cgi_command=mysqldump
       ?  --disable-keys, -K

        For each table, surround the INSERT statements with /*!40000 ALTER
        TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
        ENABLE KEYS */; statements. This makes loading the dump file into a
        MySQL 4.0 or newer server faster because the indexes are created
        after all rows are inserted. This option is effective for MyISAM
        tables only.
0
 
K VDatabase ConsultantAuthor Commented:
oh sorry my mistake
0
 
K VDatabase ConsultantAuthor Commented:
what are available options?
0
 
todd_farmerCommented:
Yeah, as you've probably discovered, the --disable-keys option doesn't eliminate the key definitions but just temporarily disables them as the data is loaded to the table for insert speed.

There is no option within mysqldump to skip the indexes.  You can modify the dump file using a text editor or a shell script or something similar.  You could also do something like the following in MySQL 5.0:

mysql> select concat('ALTER TABLE `', table_schema, '`.`', table_name, '` DROP KEY `', constraint_name, '`;') from information_schema.key_column_usage;

That get's you a bunch of statements like:


 ALTER TABLE `world`.`city` DROP KEY `PRIMARY`;
                          |
 ALTER TABLE `world`.`country` DROP KEY `PRIMARY`;
                          |
 ALTER TABLE `world`.`countrylanguage` DROP KEY `PRIMARY`;
                          |
 ALTER TABLE `world`.`countrylanguage` DROP KEY `PRIMARY`;

You can take those, save them to a file (perhaps using SELECT ... INTO OUTFILE ... ) and then execute them to drop the indexes.

Hope that helps.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now