mysql import only missing rows

Question

I'm looking for a way to restore my DB from a prior backup. However, the backup should not simply overwrite all existing records but instead add only the difference between current DB and the backup file. If no "non existent" records are stored in the backup, nothing should happen. The backups were made with mysqldump. Any clues? Thanks in advance

Answer 1

Here is a less manual answer:

mysqldump -t --insert-ignore --skip-opt -u USER -pPASSWORD -h 127.0.0.1 database > database.sql

That export command with the -t --insert-ignore --skip-opt options will give you a sql dump file with no DROP TABLE or CREATE TABLE commands and every INSERT is now an INSERT IGNORE.

BONUS:

This will dump a single table in the same way:

mysqldump -t --insert-ignore --skip-opt -u USER -pPASSWORD -h 127.0.0.1 database table_name > table_name.sql

I needed this today and could not help but to share it!

Answer 2

Remove the DROP TABLE and CREATE TABLE statements from the dump file. Change the INSERT statements to INSERT IGNORE. Then load the backup file and it should not update any duplicate rows.