Convert Mysql Dump File from INSERT to INSERT IGNORE

Question

I have a huge dump file about 40G in size, and I need to dump that back into the database since there are some records are missing after a recovery. Is there any easy way I can covert the INSERT into INSERT IGNORE in the dump file to avoid duplicate entries errors? loading the file to a text editor seems a no go to me. thank you very much in advance

Solution 1

If your using a unix-like operating system, you can use sed:

cat file.sql | sed s/"^INSERT"/"INSERT IGNORE"/g > updated.sql

Solution 2

There is also a switch for mysqldump

--insert-ignore in mysqldump

Solution 3

Use a text processing application like sed from the command line to do the search-replace on your imports.

Comments

[Happy]

--insert-ignore option in mysqldump (as the answer bellow) is the best option. See: dev.mysql.com/doc/refman/8.0/en/…

[John]

@Happy while insert-ignore is a good option, the large majority of the people coming here are fully aware of that. But making a new dump is often not an option. There is no downside at using sed . Though the usage of quotation in the sample is strange. just put the entire sed statement into " "