Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 &eacute; 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

share|improve this answer
    
i tried this but the data still stored as symbols and the data in the file are symbols too, is there any other solution to this? –  M.B.Asfoor Apr 12 '12 at 12:51
    
What if for the mysqldump command you also use the same option --default-character-set=charset ? So mysqldump --default-character-set=utf8 -u root -p mydatabase –  Craig Efrein Apr 12 '12 at 15:04
    
mysqldump didn't work it didn't complete the database back file execution –  M.B.Asfoor Apr 12 '12 at 15:53
    
I ran the command like this and didn't run into any errors. Can you post the error you received? mysqldump --default-character-set=utf8 -u root -p mydatabase > /tmp/mydatabase.sql –  Craig Efrein Apr 12 '12 at 16:03
    
I used the mysql command again with an old backup and every thing went fine thank you for your help –  M.B.Asfoor Apr 12 '12 at 16:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.