Mysql – How to Skip Existing Data While Restoring thesqldump File

command lineimportMySQLmysqldumprestore

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.

Best Answer

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

Related Question