Solved

mysql DUMP without/of indexes

Posted on 2007-03-27
7
4,574 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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