mysqldump with INSERT ... ON DUPLICATE

Question

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.

Answer 1

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

• Edward Newell: Wait a minute: using --replace means that DB2 data will overwrite DB1 data, and using --insert-ignore means that DB1 data prevails. The question seems to ask how to do UPDATE in the case of duplicate keys. In any case, I'd like to know.
• RolandoMySQLDBA: @EdwardNewell Please note that --replace is equivalent to doing ON DUPLICATE UPDATE on every column. Unfortunately, mysqldump is not designed to update specific columns because of the bulk loading and dumping nature of mysqldump. My answer simply reveals what mysqldump is capable of doing. You would have to write custom code, apart from mysqldump, to do ON DUPLICATE UPDATE.
• Edward Newell: As long as DB1 and 2 have exactly the same schema, you're right. But suppose DB1 has extra fields. Then --replace will cause the extra fields to revert to defaults (or an error if there are no defaults) instead of just updating the shared fields. I realize the OP's situation is for two databases with the same schemas, but just pointing out that there is a difference, and it would be useful to have a true update-type dump in certain cases (I'm facing one now!)
• Christopher Schultz: Note that if there are foreign keys pointing to the record being updated, using REPLACE INTO might fail because the record cannot be deleted with severing those relationships. If you have ON DELETE CASCADE, then you will empty-out those tables that depend upon the one being updated. REPLACE INTO is quite a dangerous operation.
• Christopher Schultz: @RolandoMySQLDBA Agreed. I just wanted to comment so that anyone reading this answer understood that these options (particularly REPLACE INTO) can be dangerous and have "surprising" effects. Good answer -- just wanted to add a caveat.