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!

Hypothec

Answer 1

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

Sparklesparkler

looks promising, I'll start trying this. – Hypothec

I have a foreign key fields that reference primary keys. Does this solution maintain that sort of referential integrity? It doesn't look like it does. – Cutlerr

No, it won't. It changes the primary key. – Sparklesparkler

As an alternative, is if you use phpmyadmin or a tool like mysql workbench, you can do a mysql dump and exclude certain columns from the results. – Thoroughpaced

Answer 2

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.

Epizootic

If you use a text editor that supports regular expression replace you can look for VALUES \([0-9]+ and replace with VALUES \(NULL, this regular expression solution can also be extended for tables where the auto-increment is not on the first column. – Postulate

This may be a hack but it was exactly the right solution. – Covert

Answer 3

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.

Pylorectomy

Not on mysql 4 :(. I know, sad :(. – Hypothec

Depending on the size of the table, you could create a temp copy (without the PK) instead of creating a view. – Danger

Tables are in the millions of records range. – Hypothec

FYI Attempting to export a view in phpmyadmin will result in no rows. – Overstock

Answer 4

1. Clone Your table
2. Drop the column in clone table
3. Dump the clone table without the structure (but with -c option to get complete inserts)
4. Import where You want

Sain

Answer 5

This is a total pain. I get around this issue by running something like

sed -e "s/([0-9]*,/(/gi" export.sql > expor2.sql 

on the dump to get rid of the primary keys and then

sed -e "s/VALUES/(col1,col2,...etc.) VALUES/gi" LinxImport2.sql > LinxImport3.sql

for all of the columns except for the primary key. Of course, you'll have to be careful that ([0-9]*, doesn't replace anything that you actually want.

Hope that helps someone.

Polo

I had to remove the i flag from the regular expression, but otherwise, this worked like a charm! Thanks! – Desrosiers

you don't need the second statement when using mysqldump --complete-insert .... – Griffis

Answer 6

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.

Veterinarian

Answer 7

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'.

Giliana

Answer 8

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.

Jerrine

I don't understand how creating a view helps, since mysqldump doesn't output the view DATA, just the CREATE VIEW statement, so you're no further ahead. If you're just using the view to then create some temp table, then the view is redundant. If you're then going to use select .. into outfile, then once again, the view is redundant. What am I missing? – Occasionally

Answer 9

The solution I've been using is to just do a regular SQL export of the data I'm exporting, then removing the primary key from the insert statements using a RegEx find&replace editor. Personally I use Sublime Text, but I'm sure TextMate, Notepad++ etc. can do the same.

Then I just run the query in which ever database the data should be inserted to by copy pasting the query into HeidiSQL's query window or PHPMyAdmin. If there's a LOT of data I save the insert query to an SQL file and use file import instead. Copy & paste with huge amounts of text often makes Chrome freeze.

This might sound like a lot of work, but I rarely use more than a couple of minutes between the export and the import. Probably a lot less than I would use on the accepted solution. I've used this solution method on several hundred thousand rows without issue, but I think it would get problematic when you reach the millions.

Nonpartisan

Answer 10

I like the temporary table route.

create temporary table my_table_copy
select * from my_table;

alter table my_table_copy drop id;

// Use your favorite dumping method for the temporary table

Like the others, this isn't a one-size-fits-all solution (especially given OP's millions of rows) but even at 10^6 rows it takes several seconds to run but works.

Hohenzollern

Answer 11

Easiest way:

1) Drop the primary id field
2) Dump the table with -c option
3) Re-add Primary Key to the dump file
4) run the dump to create new keys

No need to clone the table or anything like that; if you're just looking to clean up your keys this is all you need to do. If you want to sort or re-order you'd do that between 2 and 4

Sandell