mysqldump table without dumping the primary key

Question

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 1

To solve this problem, I looked up this question, found @pumpkinthehead's answer, and realized that all we need to do is find+replace the primary key in each row with the NULL so that mysql will use the default auto_increment value instead.

(your complete mysqldump command) | sed -e "s/([0-9]*,/(NULL,/gi" > my_dump_with_no_primary_keys.sql

Original output:

INSERT INTO `core_config_data` VALUES     (2735,'default',0,'productupdates/configuration/sender_email_identity','general'),     (2736,'default',0,'productupdates/configuration/unsubscribe','1'), 

Transformed Output:

INSERT INTO `core_config_data` VALUES     (NULL,'default',0,'productupdates/configuration/sender_email_identity','general'),     (NULL,'default',0,'productupdates/configuration/unsubscribe','1'), 

Note: This is still a hack; For example, it will fail if your auto-increment column is not the first column, but solves my problem 99% of the time.