How to Skip Existing Data While Restoring mysqldump File?

Question

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.

Answer 1

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 !!!

Answer 2

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 ... ;