altre destinazioni

vedi anche

ultimi post

ultimi commenti

tag principali

categorie

archivi

powered by

  • WPFrontman + WP

friends

copyright

  • © 2004-2011
    Ludovico Magnocavallo
    tutti i diritti riservati

Convert a bunch of tables to InnoDB

27 aprile 2005

3 commenti

tag

categorie

A friend just asked me how to convert a bunch of MySQL tables from InnoDB to InnoDB, and since it seems that Google yields no practical answers to this question, I am posting the following few lines as a reference for other people. Nothing new, but useful if you are not familiar with either MySQL or bash.

The SQL statement to convert a single table to a different table type in the MySQL client is:

alter table mytable type=InnoDB;

More often than not you will need to convert all the tables in a single DB, so if you are using bash you can save a few boring keystrokes and the inevitable typing errors using something like this:

for t in $(mysql --batch --column-names=false -e "show tables" mydbname);
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

If you need to omit a few tables, or convert only tables starting with a common prefix, you can use grep in the for statement:

for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep -v "exclude_this");
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep "include_this");
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

Or directly type the names of the tables you need:

for t in table_1 table_2 table_n;
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

3 commenti

  • Kumar Chetan
    13 maggio 2005 #

    Inspiring.
    I am going to write a PHP script that will do this for me and may be post the same to my blog.
    thanx

  • Leonard
    23 maggio 2005 #

    Great bit of info, especially for guys like me who don’t really want to learn Bash just to change my MySQL table types.

    One note, on my mysql binary (v. 3.23.58) there doesn’t appear to be a “–column-names” switch, and the script above will not work. Running without that switch will get the job done, but the script will give a single error when it tries to convert a “Tables_in_dbname” table to innodb.

  • ludo
    23 maggio 2005 #

    Leonard, thanks for pointing out the missing switch in MySQL 3.x.

    As you have noticed the warning message does no harm, but if you want to exclude it just use “| grep -v Tables_in” after the mysql invocation, as shown in one of the examples above.