29

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Xupypr MV
  • 465
  • 2
  • 5
  • 8

1 Answers1

50

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
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1
    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 02 '14 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 03 '14 at 02:08
  • 1
    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!) – Edward Newell Jul 03 '14 at 02:26
  • Since the question specifically says import it to another database (with same tables structure), then my answer matches the question as posed. In the event of updating a few columns instead of all columns (regardless of matching table structures or not), you have to custom code ON DUPLICATE KEY UPDATE. mysqldump is definitely not the solution in your particular case. I recommend that you post your problem as a separate question. – RolandoMySQLDBA Jul 03 '14 at 02:49
  • 1
    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 Jul 03 '14 at 20:36
  • @ChristopherSchultz this is why mysqldump is not the solution for everyone. The mysqldump options for replace and insert ignore are not got the faint of heart but are for those who truly know their effects and can live with the data thereafter. Mysqldump is never to thought of as a replacement for custom data manipulation, nor is it a panacea for every DB setup. Custom scripting of bulk loading is the safest and most responsible approach by far – RolandoMySQLDBA Jul 03 '14 at 21:57
  • 1
    @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. – Christopher Schultz Jul 07 '14 at 13:36
  • been in need of this for a long while now. thumbs up – Jimmy Ilenloa Nov 02 '22 at 17:04