Importing a mysqldump file when there are many character set conversion problems

Posted on 2008-11-04
Last Modified: 2012-05-05
I'm trying to create a database using an export SQL file that I created with mysqldump. The source database is using character set latin1 and collation latin1_swedish_ci. In fact both database are created equally with regards to character  and collation variables are just similar. See code snippet.

Initially I thought it would be something simple like:
mysqldump --skip-opt -u**** -p**** -h myhost database 1> dump.sql
And then import:
mysql -u*** -p**** -v database < dump.sql

Of course I was wrong?

I noticed after some experimenting that character conversions where going on e.g. glossário_de_tradução was converted into glossÒ¡rio_de_traduÒ§Ò£o in the dump.sql file. Fixing that was of course first order of business. By changing the mysqldump command line I was able to produce a correct dump.sql:
mysqldump --default-character-set=latin1 --allow-keywords --quote-names --skip-opt -u**** -p**** -h myhost database 1> dump.sql

Now I have been working on importing that file for hours and hours with very little results to show for it. No matter what character sets and collation I use, character set conversions are going on which results in stuff in my database that looks like: glossário_de_tradução.

So I tried to create the database with statements like

create database database character set utf8 collate utf8_general_ci;
create database database character set latin1 collate latin1_swedish_ci;

With similar bad results. Changing the dump file didn't work as well.

CREATE TABLE `pages` (
ENGINE=innoDB CHARSET=utf8 COLLATE=utf8_general_ci


CREATE TABLE `pages` (
ENGINE=innoDB CHARSET=latin1 COLLATE=latin1_swedish_ci

I expected a database migration to be hard work but I never expected anything like this. Maybe my own fault for being such a cheap skate and wanting to use a free database instead of a commercial product. But anyway, no use in crying of the sea of spilled MySQL milk, migrating to another database is not an option anymore, my question now is how do you actually export and import a MySQL database?

Thanks and Regards,
mysql> show variables like 'collation%'

    -> ;


| Variable_name        | Value             |


| collation_connection | latin1_swedish_ci | 

| collation_database   | latin1_swedish_ci | 

| collation_server     | latin1_swedish_ci | 


3 rows in set (0.00 sec)    

mysql> show variables like 'character%'

    -> ;


| Variable_name            | Value                            |


| character_set_client     | latin1                           | 

| character_set_connection | latin1                           | 

| character_set_database   | latin1                           | 

| character_set_filesystem | binary                           | 

| character_set_results    | latin1                           | 

| character_set_server     | latin1                           | 

| character_set_system     | utf8                             | 

| character_sets_dir       | /opt/lampp/share/mysql/charsets/ | 


8 rows in set (0.00 sec)

Open in new window

Question by:ostraaten
    1 Comment

    Accepted Solution

    I solved the damn problem myself with the following statements:
    mysqldump --default-character-set=latin1 --allow-keywords --quote-names --skip-opt -u*** -p*** -h*** db 1> dump.sql

    Create dabase:
    create database db character set utf8 collate utf8_general_ci;
    mysql --default-character-set=utf8  -u#{user} -p#{pass} -v #{db} < dump.sql

    I don't understand why this works but I don't mind.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Little introduction about CP: CP is a command on linux that use to copy files and folder from one location to another location. Example usage of CP as follow: cp /myfoder /pathto/destination/folder/ cp abc.tar.gz /pathto/destination/folder/ab…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now