mysqldump table without dumping the primary key

Zee Spencer picture Zee Spencer · Jun 19, 2009 · Viewed 38.7k times · Source

I have one table spread across two servers running MySql 4. I need to merge these into one server for our test environment.

These tables literally have millions of records each, and the reason they are on two servers is because of how huge they are. Any altering and paging of the tables will give us too huge of a performance hit.

Because they are on a production environment, it is impossible for me to alter them in any way on their existing servers.

The issue is the primary key is a unique auto incrementing field, so there are intersections.

I've been trying to figure out how to use the mysqldump command to ignore certain fields, but the --disable-keys merely alters the table, instead of getting rid of the keys completely.

At this point it's looking like I'm going to need to modify the database structure to utilize a checksum or hash for the primary key as a combination of the two unique fields that actually should be unique... I really don't want to do this.

Help!

Answer

longneck picture longneck · Jun 19, 2009

if you don't care what the value of the auto_increment column will be, then just load the first file, rename the table, then recreate the table and load the second file. finally, use

INSERT newly_created_table_name (all, columns, except, the, auto_increment, column)
       SELECT all, columns, except, the, auto_increment, column
         FROM renamed_table_name