How to convert the database encoding

There are ONLY TWO methods to convert the database charset encoding and collation:


2) In-place conversion

The first method is safer, and is the only one I will cover.


After you obtain your proper mysqldump file you must check that non-Latin characters were exported properly. If your database contained non-Latin characters (Cyrillic, etc), then search for a string of text in those characters in a UTF-8 capable editor set in UTF-8 mode. If those characters appear as gibberish, then you have not specified your mysql dump parameters properly. This also likely means that your database contains UTF-8 encoded non-Latin characters in a table which claims to be Latin1 collation.

Read this article for more information: Non-latin1 characters in a MySQL version 4 database.

You have two options of proceeding:

1) At this time, skip converting your database to UTF-8 encoding, and stick with Latin1. To do this, follow my instructions of creating a readable dump file, but do not search & replace latin1 with utf8. Choose this option if you are on a shared hosting environment, and running of mysql command

show variables like 'char%';

Results in "latin1" specification.

Shared hosting does not allow editing of my.cnf. You will have to wait until your hosting company changes this setting (likely for MySQL v5.5+). Or, talk to your hosting provider tech support.

2) Go thru the additional work of converting the double-encoding present in your tables. You must follow directions of someone else's article here: Getting out of MySQL Character Set Hell.



Then, examine the table creation statements in that file.

For a version 4 MySQL source, you might only see:

CREATE TABLE 'tablename' (

because MySQL version 4 defaulted to Latin1 without explicitly saying so.


For a version 5 MySQL source, you might see:

CREATE TABLE 'tablename' (
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

You might now see the COLLATE statement specified, and it may also be other latin1 variants (swedish, etc).


Now, in a UTF-8 capable editor (NOT Notepad, Microsoft Word, or Wordpad!), Search & Replace so that the CREATE TABLE is specified thus:

CREATE TABLE 'tablename' (
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COLLATE=utf8_general_ci;


When you save this modified file, do not overwrite the original mysqldump; create another file.


For further reading:


Procedures to follow if your database has a mix of encodings, or is double-encoded:



I have prepared a Website Management Commands Master Reference which can aid you in this process.

˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅


Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 31-Dec-12 22:00:37 EST
Comments on this page: