Question

Earlier versions of MySQL stored UTF8 data in mysql tables where the collation and character set was set to latin1. The data went in and out to FogBugz correctly and MySQL itself didn't really "know" what type of data it was.

How do I get the data into UTF8 tables so my copy tool (preferably ESF Database Convert) works correctly?

Answer 1

You can fix this by getting the data out and back into MySQL in utf8 format.

a. Export the schema only:

mysqldump --default-character-set=latin1 --skip-set-charset -d -uusername -ppassword dbname > dbname_schema.sql

b. Export the data only to a different file:

mysqldump --default-character-set=latin1 --skip-set-charset -t -uusername -ppassword  dbname > dbname_data.sql

c. Open the file 'dbname_schema.sql' with any editor and replace each "DEFAULT CHARSET=latin1" phrase with "DEFAULT CHARSET=utf8" one

d. Make a new utf8 database in MySQL

 mysql -uusername -ppassword -e "CREATE DATABASE new_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"

e. Import the schema and then the data in utf8 format

 mysql --default-character-set=utf8 -uusername -ppassword new_db < dbname_schema.sql
 mysql --default-character-set=utf8  -uusername -ppassword new_db < dbname_data.sql