Townsville Linux Users Group

I had a latin1 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=latin1 --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