Welcome > MySQL > MySQL: Remove Duplicates

MySQL: Remove Duplicates

1. Dump database:

echo "use $DBNAME;" >backup.sql
echo "" >>backup.sql
mysqldump -h $DBHOST -P $DBPORT -u $DBUSER -p$DBPASSWORD --opt $DBNAME >>backup.sql

2. Edit backup.sql to change schema's primary key (the reason for duplicates to begin with).

3. Edit backup.sql and change all "INSERT INTO table VALUES " to "INSERT IGNORE INTO table VALUES "

4. Reload database: mysql -h $DBHOST -P $DBPORT -u $DBUSER -p$DBPASSWORD $DBNAME < backup.sql

Content Copyright 2003-2014 George Toft