Take the 2-minute tour ×
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. It's 100% free, no registration required.

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: dba.stackexchange.com/questions/5920/… –  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
add comment

2 Answers 2

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 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.

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

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
add comment

Your Answer

 
discard

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.