Today again I had to convert a MySQL database from Latin1 to UTF8. As I don’t want to search for tutorials every time I have to do this, I decided to write my way of doing that down here:

I exported the database using the following command:

mysqldump -u user -p --default-character-set=latin1 --skip-set-charset\
--single-transaction database > database.sql

The “–default-character-set=latin1″ is mandatory, when the default encoding in MySQL already is UTF8.

Then I opened the file in Notepad++ and replaced all “DEFAULT CHARSET=latin1″ with “DEFAULT CHARSET=utf8″. I did not have to change the encoding of the document, as it already was UTF8. If collation is defined, replace it as well (in my databases it generally isn’t).

I created a new database. Just to be sure that the database character set is correct, you can run:

ALTER DATABASE database DEFAULT CHARACTER SET utf8\
DEFAULT COLLATE utf8_general_ci;

After that I imported the sql file:

mysql -u user -p --default-character-set=utf8 database < database.sql