MySQL UTF8 Database migration

Question

I'm having problems to migrate an utf8 database to another server... Each source and destination table has a "DEFAULT CHARSET=utf8".

I use mysqldump to dump data and mysql < file.sql to import but when in the source table i have "España", in the destination i get "EspaÃ±a".

I read some guides, i used --default-character-set=latin1 to export and import, but the problem remains. I also tried a --default-character-set=utf8 to import, but the result is: "Espa", data is truncated to the first occurrence of a multibyte char.

I need help!

Thank you in advance

thomas-peter · Accepted Answer · 2011-04-21 10:38:45Z

3

It's very important to make sure the client is set to UTF8. Confusingly, it's not the same as setting your database to UTF8. Open /etc/my.cnf and make sure you have default-character-set = utf8 under [mysql] not just under [mysqld]

Now you should be able to pipe UTF8 dumps directly into the mysql client. I also recommend using the option --hex-blob on the mysqldump command as mysqldump is not perfect.

answered Apr 21, 2011 at 10:38

thomas-peter

7,9646 gold badges48 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darkyo · Accepted Answer · 2010-03-07 13:29:53Z

1

try

iconv --from-code=ISO-8859-1 --to-code=UTF-8 ./file >file.utf8
mysql --default-charset=utf8 < file.utf8

If it does not work i advise you to import data then convert it

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

hope it'll help

answered Mar 7, 2010 at 13:29

community wiki

Darkyo

Comments

Pekka · Accepted Answer · 2009-12-18 12:04:43Z

0

What is the collation of each table? Sounds to me like the 2nd table is still encoded ISO-8859-1 or similar.

Plus, what tool are you using to look at the destination data? Are you sure the connection that tool uses is UTF-8 as well?

answered Dec 18, 2009 at 12:04

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

4 Comments

Dario Over a year ago

Here is the SHOW CREATE TABLE code: CREATE TABLE Country ( id int(10) unsigned NOT NULL auto_increment, country varchar(255) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 how do i get the collation ?

Dario Over a year ago

i use --default-character-set=utf8 to import data using mysql < file

Dario Over a year ago

Pekka Over a year ago

Character sets and table/column collations are not the same. What collation are your tables/columns? What does DESCRIBE say? Check dev.mysql.com/doc/refman/5.0/en/charset.html

Collectives™ on Stack Overflow

MySQL UTF8 Database migration

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related