Dump of a mysql table on import replaced existing records

I took a dump using mysqldump..

mysqldump -u... -p... mydb t1 > mydb_table.sql

Then I imported the dump in another database having same table, but different records..

mysql -u...-p... mydb < mydb_tables.sql

The importing db had records from primary_key 1 to 1000, and the exporting db had 5000 to 10,000..

But on import the existing records, ie 1 to 1000 got deleted..

How?? Why?? If it a default behaviour, what options I can give to dump to not let it happen next time..

The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this:

mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql

That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will have import like this:

mysql -u...-p... --force mydb < mydb_tables.sql

The --force option is for the sole purpose of continuing INSERTs in the event a duplicate key is encountered. In that instance, the offending INSERT's error is ignored and on to the next INSERT.

StackExchange Database Administrators Q#33807, answer score: 13