mysql DUMP without/of indexes

How can i take mysqldump without indexes ?
and also is there any way to only dump out the indexes ?
LVL 22
theGhost_k8Database ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
theGhost_k8Database 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
theGhost_k8Database ConsultantAuthor Commented:
oh sorry my mistake
0
theGhost_k8Database 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.