Solved

mysql DUMP without/of indexes

Posted on 2007-03-27
7
4,234 Views
Last Modified: 2008-02-01
How can i take mysqldump without indexes ?
and also is there any way to only dump out the indexes ?
0
Comment
Question by:theGhost_k8
  • 3
  • 2
7 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18806171
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
 
LVL 21

Author Comment

by:theGhost_k8
ID: 18806191
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
 
LVL 21

Author Comment

by:theGhost_k8
ID: 18806212
oh sorry my mistake
0
 
LVL 21

Author Comment

by:theGhost_k8
ID: 18806216
what are available options?
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 500 total points
ID: 18806267
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now