Convert All Tables to InnoDB

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 full-text 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

Please 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 cool 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 1

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

Alternative 2

This one is just in from Bob Sikkema, he mentions

For MySQL 5.5:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
    AND engine = 'MyISAM'
    AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Version for MySQL prior to MySQL 5.5

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
    AND engine = 'MyISAM'
    AND table_schema NOT IN ('information_schema', 'mysql');

Using the output from the query, you have a conversion script for the slave. Tables that have FULLTEXT indexes cannot be converted to MyISAM. To locate, run this query:

SELECT
    tbl.table_schema,
    tbl.table_name
FROM (
    SELECT
        table_schema,
        table_name
    FROM information_schema.tables
    WHERE 1=1
        AND engine = 'MyISAM'
        AND table_schema NOT IN ('information_schema', 'mysql')
) tbl
INNER JOIN
(
    SELECT
        table_schema,
        table_name
    FROM information_schema.statistics
    WHERE index_type = 'FULLTEXT'
) ndx
USING (table_schema, table_name);

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 & alternatives!