which is the better way to change the character set for huge data tables?

Question

In my production database Alerts related tables are created with default CharSet of "latin", due to this we are getting error when we try to insert Japanese characters in the table. We need to change the table and columns default charset to UTF8. As these tables are having huge data, Alter command might take so much time (it took 5hrs in my local DB with same amount of data) and lock the table which will cause data loss. Can we plan a mechanism to change the Charset to UTF8, without data loss.

which is the better way to change the charset for huge data tables?

Answer 1

I found this on mysql manual http://dev.mysql.com/doc/refman/5.1/en/alter-table.html:

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates

So yes -- it's tricky to minimize downtime while doing this. It depends on the usage profile of your table, are there more reads/writes?

One approach I can think of is to use some sort of replication. So create a new Alert table that uses UTF-8, and find a way to replicate original table to the new one without affecting availability / throughput. When the replication is complete (or close enough), switch the table by renaming it ?

Ofcourse this is easier said than done -- need more learning if it's even possible.

Answer 2

You may take a look into Percona Toolkit::online-chema-change tool: pt-online-schema-change
It does exactly this - "alters a table’s structure without blocking reads or writes" - with some limitations(only InnoDB tables etc) and risks involved.

Answer 3

Create a replicated copy of your database on an other machine or instance, when you setup the replication issue stop slave command and alter the table. If you have more than one table, between each conversation you may consider issuing again start slave to synchronise two databases. (If you do not this it may take longer to synchronise) When you complete the conversion the replicated copy can replace your old production database and you remove the old one. This is the way i found out to minimize downtime.