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
Wed 14 Feb 2007, 17:44
0 comments