[번역|StackOverflow|Mysql] mysqldump with INSERT ... ON DUPLICATE

mysqldump with INSERT ... ON DUPLICATE

https://dba.stackexchange.com/questions/5033/mysqldump-with-insert-on-duplicate

데이터베이스를 다른 데이터베이스로 합치고 싶다. 그래서, mysqldump 를 이용해 덤프를 만들었고, 다른 데이터베이스로 import 했다. (테이블 구조는 동일하다.) 이런 방식으로 하면 (중복데이터 등과 같은) 문제는 아직까지는 없다.

[번역생략] 

mysqldump 를 생성하면서, ON DUPLICATE 옵션을 같이 넣을 수 있나? 아니면, 덤프를 합치면서, 새로운 데이터는 insert 하고, 변경된 데이터는 update 하는 방법이 있나?

물론, 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.

asked Aug 25 '11 at 14:46 Xupypr MV

----

도움이 될만한 옵션으로 다음과 같은 것이 있다.

  --insert-ignore     INSERT IGNORE 로 데이터로우를 insert 한다.

  --replace           INSERT INTO 대신에 REPLACE INTO 를 사용한다.

  -t, --no-create-info  테이블생성문은 만들지 않는다.

다음 패러다임을 명심해 두라.

• mysqldump 로 DB1 전체를 DUMP1 으로 덤프한다.
• DB3 로 DUMP1 을 로드한다.
• mysqldump 로 --replace 또는 --insert-ignore 그리고, --no-create-info 옵션을 주어 DUMP2 로 덤프한다.
• DB3 로 DUMP2 를 로드한다.

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

answered Aug 25 '11 at 15:26 RolandoMySQLDBA

----

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 '14 at 22:09

--replace 는 모든 컬럼에 대해 ON DUPLICATE UPDATE 한 것과 동일하다. 단, mysqldump 로 특정 컬럼만 업데이트하도록 하는 건 불가능하다. 

@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 '14 at 2:08

DB1과 DB2의 스키마가 완전히 동일하다면, 이것은 옳다. 하지만, DB1 이 다른 필드를 갖는다면 문제를 만들 것이다.

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 3 '14 at 2:26 

질문에서 같은 테이블 구조라고 말했기 때문에, 내 답변은 질문에 부합한다. 일부 컬럼만 업데이트하려고 한다면, ON DUPLICATE KEY UPDATE 문을 손봐야 할 것이다. 

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 3 '14 at 2:49 

만약 업데이트되고 있는 데이터에 foreign key 가 있다면, 이 관계를 유지한 상태로 레코드를 지울 수는 없기 때문에, REPLACE INTO 를 썼을 때 실패할 수 있다. ON DELETE CASCADE 가 걸려 있다면, 업데이트 되고 있는 데이터와 연관된 테이블을 지울 것이다. (?) REPLACE INTO 는 매우 위험하다.

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 3 '14 at 20:36

그런 점 때문에, mysqldump 가 모든 경우의 해법이 될 수는 없다. replace나 insert ignore 옵션의 mysqldump 는 그 영향성에 대해 잘 이해하고 있는 사람들이 써야 하는 것이다. 가장 안전한 방법은 bulk load 하는 커스텀 스크립트이다.

@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 3 '14 at 21:57

@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 7 '14 at 13:36