Easiest way to fix MySQL UTF8 to Latin1 character set encoding issues.

MySQL originally used the latin1 character set by default which stored characters in a 2-byte sequence. In recent versions it defaults to UTF-8 to be friendlier to international users.

When migrating MySQL databases, occasionally you’ll see odd characters appear on the new system. For example, a simple quote mark may be replaced by 4-5 characters of junk symbols.

This happens when MySQL is trying to display characters using a different character set to the one they are stored in. To fix, we need to make sure the database is marked as latin1 when we export it from the old system, and then re-encode it into UTF-8 when importing it into it’s new home.

Export:

mysqldump -u $user -p --opt --quote-names --skip-set-charset \
--default-character-set=latin1 $dbname > dump.sql

Import:

mysql -u $user -p --default-character-set=utf8 $dbname < dump.sql