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

You can create a view of the table without the primary key column, then run mysqldump on that view.

So if your table "users" has the columns: id, name, email

> CREATE VIEW myView AS
  SELECT name, email FROM users

Edit: ah I see, I'm not sure if there's any other way then.

Answer 2

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

Answer 3

SELECT null as fake_pk, `col_2`, `col_3`, `col_4` INTO OUTFILE 'your_file'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;

LOAD DATA INFILE 'your_file' INTO TABLE your_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

For added fanciness, you can set a before insert trigger on your receiving table that sets the new primary key for reach row before the insertion occurs, thereby using regular dumps and still clearing your pk. Not tested, but feeling pretty confident about it.

Answer 4

Use a dummy temporary primary key:

Use mysqldump normally (--opts -c). For example, your primary key is 'id'. Edit the output files and add a row "dummy_id" to the structure of your table with the same type as 'id' (but not primary key of course). Then modify the INSERT statement and replace 'id' by 'dummy_id'. Once imported, drop the column 'dummy_id'.

Answer 5

jimyi was on the right track.

This is one of the reasons why autoincrement keys are a PITA. One solution is not to delete data but add to it.

CREATE VIEW myView AS
SELECT id*10+$x, name, email FROM users

(where $x is a single digit uniquely identifying the original database) either creating the view on the source database (which you hint may not be possible) or use an extract routine like that described by Autocracy or load the data into staging tables on the test box.

Alternatively, don't create the table on the test system - instead put in separate tables for the src data then create a view which fetches from them both:

CREATE VIEW users AS
(SELECT * FROM users_on_a) UNION (SELECT * FROM users_on_b)

C.