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.

To resolve this problem, I had to do the following two steps.

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).

image

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.

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

  1. Pingback: 怎样正确导入中文(UTF-8)到 MySql 5.0 数据库 | 【超凡博俗 之 数码人生】

  2. Pingback: How to correctly import no-English (UTF-8) characters from MySql4 … | Source code bank

  3. David

    “–default-character-set=utf8”

    This did the trick. You totally saved my bacon. Thanks for saving me hours of banging my head against the keyboard.

    [Reply]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Answer: * Time limit is exhausted. Please reload the CAPTCHA.