MySQL - Changing DB CHARACTER SET

Question

I have a 30GB MySql DB, made of innoDB tables. currently the tables character set is: "utf8_unicode_ci" and I want to change it to "utf8_general_ci", what the best way to do it? currently i'm going through each table and running ALTER TABLE some_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; and it takes for ever... is there a better way?

Answer 1

I know what you mean. Alter table sometimes has to rebuild the whole table when you use it.

Assuming that the Alter table statement is annoying you because it takes too long AND it locks your table, then you can use 2 tools to make it an "online" alter table.

One if from facebook https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

And one is from openark http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html (MySQL 5.1+ only)

Both these tools basically create a new table, copy the data over and keep it updated by updating both the old and the new table with new queries until the new table is completed. This means that you need to have the space available to do this operation.

Hope it helps.

Answer 2

You might try:

• Take a mysqldump w/ -e --no-create-info > source.sql
• Truncate all tables
• Alter all tables
• source source.sql

As with all risky operations, perform in a test environment first to verify the outcome. When you "source source.sql", if literally running from the mysql prompt don't forget to set names utf8. If catting into mysql, run mysql w/ --default-character-set=utf8 to avoid charset weirdness from your terminal settings.