Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 10 down vote accepted

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
share|improve this answer
    
great info and logic walkthrough. –  Derek Downey Aug 25 '11 at 15:52
    
so simple... thanks... –  Xupypr MV Aug 25 '11 at 16:26
    
Very helpful, I missed --no-create-info in the docs and when searching the web found some solutions based on commenting out the create table line after the dump is finished. –  codewaggle Dec 18 '12 at 11:08
    
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. –  Edward Newell Jul 2 at 22:09
    
@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. –  RolandoMySQLDBA Jul 3 at 2:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.