Correcting the imported latin1 data to be utf8

Question

I restored my drupal site database from a backup but after i finished i discovered that the Imported data was symbols like this قوانين كرة because the backup was taken using latin1 charset but my tables and database are utf8

I want a way to convert this data to utf8 so the are stored as letters and words in my database not symbols

How can i do this ?

Answer 1

You can restore your database using this command

mysql -u someuser -p --default-character-set=utf8 somedatabase < backup.sql

This overrides the default client character set.

I would also make sure that my tables are also in utf8.

So when you open up your backup file with a text editor, you should see utf8 as the DEFAULT CHARSET

CREATE TABLE `sometable` (
  `c1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If you are still having problems, check your backup and make sure that the actual data being restored is correctly formatted. If you had a character like é for example, it should show up as é when you view it in a text editor and not as é or some unreadable symbol.

One additional note. utf8_general_ci and latin1_swedish_ci are collations and not character sets. Character sets like UTF8 can have multiple collations, some are case sensitive _cs and others are case insensitive _ci. Collations set rules for character comparison and sorting.

http://en.wikipedia.org/wiki/Collation