How to skip row when importing bad MySQL dump

Given bad mysqldump that causes error on import:

namtar backups # mysql -p < 2010-12-01.sql
Enter password: 
ERROR 1062 (23000) at line 8020: Duplicate entry 'l�he?' for key 'wrd_txt'

Is there an easy way to tell import to just skip given row and continue?

(Yes, I kn开发者_开发知识库ow I can manually edit the file or parse output, but it's not very convinient)

If you can make the dump again you could add --insert-ignore to the command-line when dumping.

Or you can try using the mysqlimport command with --force,which will continue even if it encounters MySQL Errors.

mysql -f -p < 2010-12-01.sql

the -f (force) being the operative option here, worked for me.

Following the advice from jmlsteele's answer and comment, here's how to turn the inserts into INSERT IGNORE on the fly.

If you're importing from an sql file:

sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" < 2010-12-01.sql | mysql -p

If you're importing from a gz file, just pipe the output from gunzip into sed instead of using the file input:

gunzip < 2010-12-01.sql.gz | sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" | mysql -p

Great tip. I did it a little different but same result.

perl -pi -e 's/INSERT INTO/INSERT IGNORE INTO/g' filename.sql

The other options certainly are viable options, but another solution would be to simply edit the .sql file obtained from the mysqldump.

Change:

INSERT INTO table_name ...

TO

INSERT IGNORE INTO table_name ...

Just a thought did you delete the MySQL directives at the top of dump? (I unintentionally did when I restarted a restore after deleting all the records/tables I'd already inserted with a sed command). These directives tell MySQL ,among other things, not to do unique tests, foreign key tests etc)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;