Which is the best way to change the character set for huge data tables?

In my production database, the alert related tables are created using the default "latin" CharSet, which is why we get an error when we try to insert Japanese characters into the table. We need to change the default character and column table to UTF8. Since these tables have huge data, the Alter command can take so long (took 5 hours in my local DB with the same amount of data) and lock the table which will result in data loss. Can we plan a mechanism for changing Charset to UTF8 without data loss.

which is the best way to change encoding for huge data tables?

+3


source to share


3 answers


I found this in the 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 modify the table, then continues. It includes the change in the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table can be read by other sessions. Updates and writes to the table that starts after the ALTER TABLE operation starts until the new table is ready, then automatically redirects to the new table without any failed updates

So yes, it is tricky to minimize downtime while doing this. It depends on the usage profile of your table, is there more read / write?



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 the original table to the new one without affecting availability / bandwidth. When replication is complete (or close enough), switch the table by renaming it?

Of course, this is easier said than done - more learning is needed if possible.

+4


source


You can have a look at the Percona Toolkit :: tool for online czech: pt-online-schema-change
It does exactly that - "changes the structure of tables without blocking read or write" - with some limitations (only InnoDB tables, etc.) and the associated risks.



+4


source


Create a replicated copy of your database on a different computer or instance when you configure the stop command to stop replication and modify it. If you have more than one table, between each conversation, you can start releasing the slave again to keep the two databases in sync. (If you don't, it may take longer to sync.) When you are done converting, the replicated copy can replace your old production database and you will delete the old one. This is how I learned to minimize downtime.

+1


source







All Articles