MySQL – How to Use mysqldump with INSERT … ON DUPLICATE

backupMySQLmysqldump

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.

Best Answer

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

Related Solutions

Mysql – slow load speed of data from thesqldump

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
Turn off foreign key checks and turn on auto-commit.
If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..

Mysql – Copying a database for testing in a safe way

Is this procedure correct ?

If you have no foreign key constraints, then yes this is correct.

If you have foreign key constraints, some data may be inaccessible. You would have to fix it.

Can I be sure that the original database will not be modified whatsoever?

After the mysqldump do the following:

grep -c "DROP DATABASE" test.sql

If you get back 0, then yes the original database will not be modified whatsoever

Best Answer

Related Solutions

Mysql – slow load speed of data from thesqldump

Mysql – Copying a database for testing in a safe way

Related Question