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?


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.

| improve this answer | |

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.

| improve this answer | |
  • what is source source.sql? – Ran Sep 25 '11 at 3:06
  • It's a filename. Nothing special about the name, it could very well be called asdf.txt or RandomFileName. The point being you redirect the stdout to that file and run the source command or cat it back into mysql – atxdba Sep 25 '11 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.