Converting a MySQL database from latin1 to utf8

Introduction
When you create a new database on MySQL, the default behaviour is to create a database supporting the latin1 character set. This is fine for most use cases, however if your application needs to support natural languages that do not use the Latin alphabet (Greek, Japanese, Arabic etc.), then you will need to convert your database to use UTF-81 instead.

In this tutorial, I will show you have to convert an existing database and tables from latin1 to the utf8 character set. Note that I am using MySQL 5.5.34.

Creating a test database
Firstly we are going to create a test database for testing the migration process. Note that if you are going to migrate a real database, you should run this procedure against an offline backup, not against a live production system.

Lets begin by creating a new database with a simple table:


mysql> create database testmigration;
mysql> use testmigration;
mysql> create table test (sometext varchar(50));

The new database and table will have the latin1 character set by default on a stock MySQL installation, lets confirm this:

 mysql> show full columns from test;
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| sometext | varchar(50) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> select default_character_set_name from information_schema.schemata where schema_name = "testmigration";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1                     |
+----------------------------+
1 row in set (0.00 sec)

If I try to store some non-Latin characters in the test.sometext field, it will become garbled.

Doing the migration to UTF-8
To begin with, we will alter the default character set on the new database to be utf8, which will ensure that any new tables in this database will use this by default:

mysql> alter database testmigration character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

select default_character_set_name from information_schema.schemata where schema_name = "testmigration";

+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8                       |
+----------------------------+
1 row in set (0.00 sec)

That takes care of new tables, but for existing tables we have to do something a little more complex. Drop back to your bash terminal, and run the following command:

 1
$ mysqldump --add-drop-table -u root --password='password' testmigration | sed -e 's/CHARSET\=latin1/CHARSET\=utf8\ COLLATE\=utf8_general_ci/g' | iconv -f latin1 -t utf8 | mysql -u root --password='password' testmigration

The command uses the mysqldump command to dump the database to standard out, then sed is used to replace latin1 with utf8 in the dump, iconv is used to convert the dump from latin1 character encoding to utf8, and finally the mysql command is used to restore the resulting backup to the database server.

Testing
To confirm that this worked, log back in again and inspect the table as before:


mysql> use testmigration;
mysql> show full columns from test;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| sometext | varchar(50) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                     |
+-------+--------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `sometext` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)</code></pre>

You should now be able to safely insert in non-Latin characters from your clients.