5

I use this query to import data using MySQL Dump (Command Prompt / Command Line)

mysql -u root -p database_name < database_name.sql

I want to import the large database (only new deltas) as of now I drop database and re-import but I want to import only new deltas.

1
  • i want to import only new datas. Create empty DB, copy "new datas" into it, then backup.
    – Akina
    Aug 24 '20 at 7:48
4

I have four(4) suggestions:

SUGGESTION #1 : Recreate dump with INSERT IGNORE

Use --insert-ignore

Write INSERT IGNORE statements rather than INSERT statements.

What happens on reload ???

  • New data gets inserted.
  • Already existing primary keys are skipped

SUGGESTION #2 : Change INSERT to INSERT IGNORE on reload

Instead of loading normally

mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:

cat database_name.sql|sed 's/^INSERT/INSERT IGNORE/'|mysql -u root -p database_name

SUGGESTION #3 : Recreate dump with REPLACE INTO

Use --replace

Write REPLACE statements rather than INSERT statements.

What happens on reload ???

  • New data gets inserted.
  • Already existing primary keys will be deleted and re-inserted

SUGGESTION #4 : Change INSERT INTO to REPLACE INTO on reload

Instead of loading normally

mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:

cat database_name.sql|sed 's/^INSERT INTO/REPLACE INTO/'|mysql -u root -p database_name

SUMMARY

  • Use suggestions 1 or 3 if you can recreate the dump
  • Use suggestions 2 or 4 if you cannot recreate the dump

GIVE IT A TRY !!!

4
  • Hey @manigopal if your deltas are just new rows, try suggestion 1 or 3. If your deltas are changed columns, try suggestion 2 or 4. Aug 24 '20 at 16:03
  • Heads Up : a REPLACE INTO performs a DELETE and an INSERT. So, more I/O will occur. Aug 24 '20 at 16:04
  • Good answer. Just one thing: cat is to conCATenate multiple files, you don't need it to pipe a single file to a command perfectly able to take a single file as input. sed 's/^INSERT/INSERT IGNORE/' database_name.sql | mysql -u root -p database_name will do the trick just fine
    – Ilario
    Aug 4 at 7:43
  • Hey @RolandoMySQLDBA SED & CAT queries are not working says 'cat' is not recognized as an internal or external command,' the same for SED
    – manigopal
    Sep 19 at 4:33
1

I would start by not trusting the dump. So, I would load the data into a separate database (or at least separate table(s)). Then I would run some SQL queries to verify that the deltas are what I expected -- whether that is "new" rows, no "dup" unique ids, etc. If I find issues, then I would craft SQL to deal with the issues. As Rolando suggests some INSERT/REPLACE [IGNORE] etc might be what is needed.

I might DELETE some of the 'new' rows, then INSERT .. SELECT to shovel the rest into the main table(s). Or, equivalently (but possibly faster): INSERT .. SELECT .. WHERE .. to shovel only the desired rows over.

Note also that FOREIGN KEYs may cause trouble.

Watch out for PRIMARY KEYs for any JOINs that are in your code, even if FOREIGN KEYs are not in place.

IODKU is usually better than REPLACE. And it can be done via:

INSERT INTO t2 (...)
    SELECT ... FROM t1 WHERE ...
    ON DUPLICATE KEY UPDATE ... ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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