CODEWITHSUNDEEP
mysqlencodingutf-8migration
Dario
Dario

Reputation: 153

MySQL UTF8 Database migration

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

Upvotes: 2

Views: 2826

Answers (3)

thomas-peter
thomas-peter

Reputation: 7944

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.

Upvotes: 3

Darkyo
Darkyo

Reputation: 83

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

Upvotes: 1

Pekka
Pekka

Reputation: 449525

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?

Upvotes: 0

Related Questions