If you use the
mysqldump --no-data or
-d option (do not dump table contents), you will have a data base schema file (with no data in it).
If you look at that file in a text editor, you're looking for lines of this form:
Code:
KEY `gnis01` (`dd_long`,`dd_lat`,`fc`,`dsg`)
Those are the indexes.
The ones that are going to give you trouble (take forever) will be those with
UNIQUE KEY. As rows are added, the entire table is scanned from the top to determine whether the unique value already exists; row-by-row, every single row.
So, what you need to do is scan through the entire schema file looking for "KEY" and make sure that the index declarations do do not spread of two or more lines -- if they do, join the lines so the entire KEY is on one line. That is, the line should begin with KEY and end with ).
When you're done with that, save that file then use the
grep utility to extract all the lines that have the pattern KEY in them, some thing like
Code:
grep KEY dbname.sql > indexes.sql
The file
indexes.sql will have your indexes in it.
Then, delete the indexes from your schema file:
Code:
vi dbname.sql
:/KEY/d
:wq
Now you've got a schema with no indexes (you may want to make sure that a "drop database if exists 'dbname'" is at the top of that file -- before the "create database 'dbname'" statement).
So, create your back up data base
Code:
mysql -p mysql < dbname.sql
<do your grants here>
Then unload the tables using
mysqldump; just the data, no schema. Copy those files over to the back up server. Then load your tables using those files (they'll load quickly with no indexes present). Finally, create the indexes:
Code:
mysql -p dbname < indexes.sql
That ought to go a lot faster.
Now it would be a really good idea to get into the MySQL manual and look through the sections about backing up a data base -- there's a lot of good information there. It would also be a good idea to examine the indexes, look at them with a jaundiced eye for whatever is overkill. Ask yourself if UNIQUE indexes are really necessary -- any table may have a unique index, but that table doesn't need more than one unique index. If there is a main table with a unique index, that may all right but any table that joins to it based on that unique index does not need a unique index. Look for lots of indexes on a given table, ask if they make sense. Experiment a little. Read the user guide about indexes and indexing strategies.
This isn't something you'll learn in five minutes but given the amount of time it takes to load your back up data base now, a day or two studying up a little might be time well spent.
Hope this helps some.