convert ascii mysql db to utf8

July 13, 2011

how to convert ascii mysql db to utf8 ?

I had a MyISAM mediawiki 1.11.0 database that needed to be utf8 to upgrade to the latest version. Before I could upgrade it I had to convert it to utf8.

Here’s the steps on FreeBSD:

mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset -r wikidb_latin1a.sql wikidb
iconv -f ISO8859-1 -t UTF-8 wikidb_latin1a.sql > wikidb_utf8a.sql
sed  -e 's/character set latin1 collate latin1_bin/character set utf8 collate utf8_unicode_ci/g' -e 's/CHARSET=latin1/CHARSET=utf8/g' wikidb_utf8a.sql > wikidb_utf8_sed_fixed2.sql
mysql -u root -p --execute="CREATE DATABASE wikidbNEW CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql -u root -p --default-character-set=utf8 wikidbNEW < wikidb_utf8_sed_fixed2.sql