Convert a MySQL DB from latin1 to UTF8

C'mon utf-8 :-)

I had a utf8 collated MySQL 4.1 db that contained both English and Japanese text. I dumped the data and then tried to migrate it into my new MySQL 5.0.26 installation. Of course being a bit lazy I did not take the time to check things out properly and was not surprised to find that all Japanese text had become mojibake. I set off on a mission to fix the problem.

I had two options. Stick with latin1 or move to utf8. I chose to move to utf8 as the front end of my website is all in utf8 so making the whole thing utf8 from front to back would make sense. Here is how I did it:

Dumped the original db with the following command:

mysqldump -u root -p --opt --default-character-set=utf8 --skip-set-charset  DBNAME > DBNAME.sql


Then using sed I changed all occurrences of the word latin1 to utf8:

sed  -e 's/latin1/utf8/g' -i ./DBNAME.sql


From here I then created the new database and then imported the dumpfile.

mysql -p -e "create database DBNAME"

mysql -p --default-character-set=utf8  DBNAME < DBNAME.sql

And that's it.

Regards, Bawdo 

tags: MySQL, utf-8

Wed 14 Feb 2007, 17:44