mysqldump with INSERT ... ON DUPLICATE

I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate entries or something else) in this case.

But I do some merges for testing purposes and I'll do final merge later. So, I want to execute merge (data may be changed) a few times. Notice, my rows in my tables never deletes, only can be inserted or updated.

Can I create mysqldump with ON DUPLICATE option? Or may be I can merge dump that inserts new data and update modified data?

Sure, I can insert ON DUPLICATE in dump manually, but I want to automate merge process.

There are options to help you in this:

--insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.

Keep this paradigm in mind

• mysqldump everything from DB1 into DUMP1

• load DUMP1 into DB3

• mysqldump everything from DB2 using --replace (or --insert-ignore)

and --no-create-info into DUMP2

• load DUMP2 into DB3

StackExchange Database Administrators Q#5033, answer score: 50