How to correctly import no-English (UTF-8) characters from MySql4 into MySql 5.0 with mysqldump

I tried to upgrade my WordPress to 2.9.2 but I had to upgrade my database MySql first since WordPress 2.9.2 requires higher MySQL version. Just like I usually did with the database backup routine, I used “mysqldump” command to export data from MySQL 4.0 and then used “mysql” to import data into MySql 5.0. Everything seemed OK at the first, but I later on found out all my Chinese characters turned into non-readable mess characters.

The problem was my host provider, 1 and 1 located in Europe, uses “latin1-german1_ci” as default database collation. And the dumped SQL script from the MySql 4.0 does not have the database collation information even it is in UTF-8, so all new tables created in the new MySql 5.0 have the latin1_german1_ci as the default collation and latin as the default character set.

Step 1, update the default Character set and Collation in the new Database.

Log into the host provider admin panel, and launch the Phpmyadmin from there. In the phpmyadmin web interface, locate the right new database and then type the query below.

ALTER DATABASE dbxxxxxx CHARACTER SET utf8 COLLATE utf8_general_ci

* dbxxxxxx is the database name.

After the query above executes successfully, you can use the following queries to check the database Character Set and Collation.

show variables like "character_set_database";
show variables like "collation_database";

Make sure they are “utf8” and “utf-general_ci” respectively. And this is the right screenshot (Pay attention to the Collation column).

Step 2, add “–default-character-set=utf8” option in your import SQL script.

Even you set up the Character Set and Collation of your new database and tables right, you still need to instruct your import sql script to execute correctly.

The normal import script is “mysql -h -u -p dbxxxxx < wordpressdata.sql” but I need to add “–default-character-set=utf8” in it to make sure my Chinese characters imported correctly. Here is my complete script to do the importing.

mysql –default-character-set=utf8 -hdbxxxx.perfora.net -udboxxxxxxx -pxxxxxx dbxxxxxxx < /kunden/…/blogs_`date +%Y%m%d`.sql

Of course, you need to replace with your own database information. Great, all Chinese characters have been imported correctly from MySql 4.0 to MySql 5.0. The next step is to upgrade the WordPress itself.

Hope it will be helpful to you as well.