How i changed bad character set encoding in mysql database and restored it

[Fli]

Hi, i had old mysql backup of the PHPBB forum and when i look into that, it had latin 1 characters instead latin2

i mean original forum had characters like ě š č ř ž ý á í é , but in mysql dump file it was like these: Ä�ĹŻĂ*Ĺ

i had no ability to do backup again, so what i did?

i have VPS server so i have access to command line via SSH. I created list of commands to search and replace these bad characters in my sql dump file. You can find these commands there: http://pastebin.com/73qH1xsB (or as an attachment of this post ) please note that i used them one by one, not run all at once because it started somehow randomly executing it.

After done, i executed search replace command sed 's/latin1/latin2/g' mysqlfilename.sql (backup your file first) so this command replaced: latin1 to latin2 (so importing will work)

ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin2_czech_cs AUTO_INCREMENT=9 ;

we need latin2 so the import is done properly with new latin2 characters we replaced

so after sql file is changed i imported it into mysql by command:

mysql -u root -pmymysqlpassword mymysqldatabasename < mymysqldumpfileijustedited.sql --default-character-set=latin2

and it worked. After import done, i got errors like:

No Language Found! (ensure /language folder has proper language flder in it)
Unknown column 't.template_inherits_id' in 'field list' [1054] (in this case run yourforumname.com/install/database_update.php . if you dont have isntall folder, upload it from default phpbb installation)

============

here is also file convertors which can achieve similar result. You can try it: http://www.convertutf8.com/utf8_char_encoding_converter_e.htm
If you know script behind that which can achieve it on linux, please share.

[WebCare360]

+1 .. Nice