Solved

mysql DUMP without/of indexes

Posted on 2007-03-27
7
4,682 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

740 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