mysqldump: special chars are not correctly saved

ddaas · 02-18-2011, 04:02 AM

Hello,
I have some tables that contain special characters from different languages like German, Italian, Russian, Spanish etc. They are stored and displayed correctly.

When want to backup my DB like "mysqldump -h localhost-u root -p dbname > dbname.sql"
the special characters are lost, they are not correctly stored nor displayed in the sql file. This means there is no possible restoration.

How can dump the tables in the right way? I think this is a very common problem.

Thank you

trickykid · 02-18-2011, 10:57 AM

No, it's not common, most people learn or read what options to use during the mysqldump to preserve special characters.

It would probably look something more like this during the mysqldump:

Code:

mysqldump -u root -p --default-character-set=latin1 dbname > dbname.sql

Then when you import the database, you need to possibly specify utf8 as the default character or change it (latin1) after it's imported in the dump file, would look something like this:

Code:

/*!40101 SET NAMES latin1 */;

Change that latin1 to utf8, then import the dump file.

ddaas · 02-18-2011, 03:14 PM

Thank you.
I'll do the tests and let everybody know how it works.

ddaas · 02-23-2011, 09:07 AM

The database had both utf8 and latin1 columns for some tables.
I can backup and restore successfully using: mysqldump -u root -p --default-character-set=utf8 dbname > dbname.sql

Then I can replace everywhere in the dump file latin1 with utf8 and the restoration is correct. Also all the columns are now utf8.