Some time ago I was in the situation where I was looking at 200 MyISAM tables screaming to get converted to InnoDB for performance reasons. You probably know that MyISAM is better at fulltext searches and such, but what I needed was this database stop locking entire tables when I was just doing row-level interactions. Here's how I did in one go.

I'm not the kind of guy who's going to spend 3 hours & 600 mouseclicks in phpmyadmin. So this needed to be automated.

Check your engines

To find out what tables currently use for their storage engine, execute:

DATABASENAME="kvz"
 
echo "SELECT TABLE_NAME, ENGINE
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = '${DATABASENAME}';" | mysql --defaults-file=/etc/mysql/debian.cnf

Dryrun

To see what MySQL commands are going to be executed, you can safely type this:

DATABASENAME="kvz"
 
echo 'SHOW TABLES;' \
 | mysql --defaults-file=/etc/mysql/debian.cnf ${DATABASENAME} \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t

Change the DATABASENAME

As you can see Ubuntu has - thanks to Debian - the /etc/mysq/debian.cnf file so you don't even need a password (that's only after you are root of course).

How sick is that.

Ok, on to the fun part.

Warning

To any inexperienced sysadmin reading this, I would have to make it clear to:

• Investigate if you need this & if your DB is compatible
• First test on a replica on another machine
• Make backups
• Plan for downtime
• Use at own risk
• And most important:
• Not come crying to me that I wrecked your DB : )

Execute

Now that you have taken all the necessary precautions, here's how to feed the commands from Dryrun back to MySQL again:

DATABASENAME="kvz"
 
echo 'SHOW TABLES;' \
 | mysql --defaults-file=/etc/mysql/debian.cnf ${DATABASENAME} \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
 | mysql --defaults-file=/etc/mysql/debian.cnf ${DATABASENAME}

Depending on the size of your tables this may take a while. But by the end of it, you'll have an InnoDB-only database. Nice.

Alternative

Here's another way as suggested by Tim in the comments:

DATABASENAME="kvz"
 
for t in `echo "show tables" | mysql --batch --skip-column-names $DATABASENAME`; do mysql $DATABASENAME -e "ALTER TABLE `$t` ENGINE = InnoDB;"; done

His use of --skip-column-names --batch is especially noteworthy, this way we could lose the awk matching for 'Tables_in_', which makes it more robust (what if that string changes in a future version).

Other purposes

Obviously these same methods could be used to convert all MySQL tables to MyISAM, change the encoding of all MySQL tables to UTF8, etc.

Share your thoughts & mutations!

You probably shouldn't follow me