The following is only for my future reference and should not realy interest anyone else unless that person is facing a similar problem as I did.

Goal:

To import multilingual data from remote hosting to production server on PC, ensuring that nothing is corrupted in the process.

Problem definition:

MySQL database located on webhost (Server 1) did not import correctly multilingual text correctly. Webhost’s character set was set to latin1 whereas production server on PC (Server 2) was set to UTF-8. One is able to check the settings of the servers by typing show variables in mysql shell.

Steps taken to achieve goal:

1. To ensure that export and import are carried out correctly, ensure that Server 1’s character-set settings are exactly the same as Server 2’s character set settings.

------------------------------------------
| SETTINGS for SERVER 1 and SERVER 2
| character_set_client  | latin1
| character_set_connection  | latin1
| character_set_database  | latin1
| character_set_results  | latin1
| character_set_server  | latin1
| character_set_system  | utf8
| collation_connection  | latin1_swedish_ci
| collation_database  | latin1_swedish_ci
| collation_server  | latin1_swedish_ci
------------------------------------------

2. Export the information from Server 1:
a) MySQLDump:
mysqldump -p -u<user_name> —default-character-set=latin1 <database_name> > mt_latin1.sql;

b) Compress:
gzip -9 mt_latin1.sql;

c) Download the compressed file mt_latin1.sql.gz into Server 2

3. Create and configure database in Server 2:
Create table set to collation latin1_swedish_ci

4. Import mt_latin1.sql.gz into newly created table

Subjects: Technology 技術

Tags: database, MySQL, unicode