Convert MySQL content from one encoding to another

This is an example to convert from latin-1 to utf-8. I used this to convert the content of old wordpress mysql database to true utf-8 content.

# Dump the old database in its original encoding (latin1 in this example)
mysqldump --default-character-set=latin1 SOURCE_DATABASE > db.sql
# Replace sql code to reflect new encoding (utf8 in this example)
sed -e 's/SET NAMES latin1/SET NAMES utf8/g' -i db.sql
sed -e 's/CHARSET=latin1/CHARSET=utf8 COLLATE=utf8_unicode_ci/g' -i db.sql
# Import the db.sql into the database (which must be created with the correct new encoding)
mysql --default-character-set=utf8 -u root -p TARGET_DATABASE < db.sql

Leave a Reply