December 5, 2013

Convert MyISAM Tables to InnoDB

The command to convert a single table from MyISAM to InnoDB looks like:

ALTER TABLE tablename ENGINE = INNODB;

1	ALTER TABLE tablename ENGINE = INNODB;

This is described suscintly by Major.IO:

http://major.io/2007/10/03/convert-myisam-tables-to-innodb/

Kevin van Zonneveld goes further in his blog:

http://kvz.io/blog/2010/04/27/convert-all-tables-to-innodb-in-one-go/

and provides some scripts to convert all tables instead of doing one at a time.

I like the following:

echo "SELECT CONCAT('SELECT \"',table_schema,'.',table_name,'\";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');" | \
mysql | grep -v CONCAT | mysql

echo "SELECT CONCAT('SELECT \"',table_schema,'.',table_name,'\";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');" | \

mysql | grep -v CONCAT | mysql

This variation shows the table name before starting the conversion so I can see progress. The query output is pipped right back in to mysql to execute.