Today I needed to replace an existing MySQL database with an updated version of the same database from .sql MySQL dump file. Because my updated sql dump contained duplicate rows that were already in the target MySQL database, just using the standard command-line import produced a fatal insert error due a duplicate primary key.
I couldn’t use PHPMyAdmin for the same reason. Apparently some versions have an option that ignores duplicates but the one on my hosting provider didn’t give me that option.
So to import the SQL dump ignoring the duplicates we need to change the INSERT statements in the file to INSERT IGNORE statements. This can be done using sed:
sed -i -- 's/INSERT/INSERT IGNORE/g' backup.sql
Then import your SQL dump using:
mysql -h host -u username -p -r database < backup.sql
There are two ways to use this with your hosting provider:
- If you have shell access on your host and access to the mysql and sed commands you can run it on the host.
- If you don’t, you need access to any machine(Linux box or Mac or Cygwin for instance) with both the sed command and mysql client command-line tool. Just replace host above with your hosting provider’s MySQL server address.
For the record, you can also probably do this with any other text editor that has a Find/Replace function. On Windows I’d suggest Notepad++. I used sed though because it made it easy to do the whole task on my hosting provider’s shell.