Mysql database migration and character set conversion

How often have you stumbled on a mysql dump file with strange hieroglyphs. You have an old Mysql 4 database in latin1 character set and you need to migrate it on a new server with utf8 support.

The procedure is simple and easy as long as you know the steps.

The first thing you need to do is make a mysql dump with the flag --default-characer-set set as the charset of the source db:

mysqldump --default-character-set=latin1 -u user -p old_db > dump.file

Now you need to replace all inctances of the old charset (latin1) in the dumpfile with the newcharset (utf8). You can do this with sed from the command line

sed "s/latin1/utf8/" dump.file > new_dump.file

Create the new database with the desired charset on the new server with the SQL command:

CREATE DATABASE new_db CHARSET utf8 COLLATE utf8_general_ci

And finaly restore the database with the command

cat new_dump.file | mysql --default-character-set=utf8 -u user -p newdb

Afterwards be sure too make all sql connections with utf8 encoding. You can execute this sql command imminently after you make a the connection to the db from the application:

SET NAMES utf8

And that is all there is to it.