Move data from mysql 4.0 to 5.6 without corrupting encoding

Question

I have a old mysql database that I would like to move to a recent version of mysql and optimize for SELECT ... WHERE ... statements. It is going to be a SELECT only database.

Original database uses latin 1 encoding but the actually data uses Turkish chracters. It is used by a desktop application. Probably application interprets data as latin 5 to display correctly.

I may ditch the original program and implement my own UI for queries.

My problem is that when I mysqldump -> mysql import, some chracters get corrupted. For example, YAŞAR becomes YAÞAR. I didnt add any encoding information to mysql dump and import. When I use phpmyadmin, it seems like my rows use latin1_swedish_ci collation with latin1 encoding.

How can I move my data and don't mess up with encoding.

Answer 1

Here is how I did it. First, I launched old mysqld and issued this command:

mysqldump -u root -p --tab=tmp --default-character-set=latin1 database tablename

Then, I started new mysqld and created table structure using phpmyadmin. I selected latin5_turkish_ci as collation for database and table rows.

I also copied resulting tablename.txt to new mysql data directory. And I imported the file like this:

mysqlimport -u root -p --default-chracter-set=latin5 database tablename.txt

This fixed issues I was having.