Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
duplicate:… – Derek Downey Sep 25 '11 at 1:04
I don't view this as a duplicate. OP is asking for alternatives to the straight alter table command. – atxdba Sep 25 '11 at 1:39
up vote 3 down vote accepted

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

And one is from openark (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.

share|improve this answer does this too. – user4877 Dec 3 '11 at 20:22

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.

share|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 posting your answer, you agree to the privacy policy and terms of service.

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