mysqldump table without dumping the primary key
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.
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
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.