How to quickly convert mysql databases from MyISAM to InnoDB

by jagbir on April 10, 2010

Recently I was in need to convert all databases of a MySQL server from MyISAM engine to InnoDB. They are facing severe issues due to table level locking in MyISAM and wanted move to InnoDB. This task can be a mammoth work but if you work little smarter, all you need to execute few statements and look at the progress while having cup of coffee. Also note that you can convert in both side, means from MyISAM to InnoDB and from InnoDB to MyISAM using these commands.

There can be many approaches to do same work, like dumping all databases and replacing myisam with innodb in all create table commands in dump file and then restoring all databases. But this can take very long time and you also need to delete data from mysql before restore etc. so I preferred following approach:

1. Extract name of all databases into a text file and remove system databases from it:

$ mysql --skip-column-names -e "show databases;" > alldb.txt
$ vi alldb.txt ## Remove system databases like 'information_schema, mysql' or any other db you want to exclude from this file.

I’ve used –skip-column-names option here to tell MySQL do not add any column/head name in output.

2. Download MaatKit mk-find tool. This is like system find command but for database:

$ cd /opt
$ wget http://www.maatkit.org/get/mk-find

3. Instead of specifying password in mysql command line, I prefer storing it in ~/.my.cnf while doing such works to avoid typing passwords every time. you can create this file or supply password as you wish.

$ cat ~/.my.cnf
[client]
user=root
password="password"

4. Before executing the command to convert storage engine make sure that ‘skip-innodb’ is not in your my.cnf file. This is because in envrionment of all MyISAM, its normally placed to disable storage engines which are not in use. You need to delete/comment this line and restart mysql service first. Execute the following command to start conversion:

$ for dbname in `cat alldb.txt`; do ./mk-find $dbname --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print; done

This will extract all database names one by one and supply that name to mk-find tool which execute alter table command on all of its tables to convert their storage engine from MyISAM to InnoDB. This can take much time depending no. of tables and their size.

Related articles on MySQL:
* Run multiple MySQL servers in a single Linux machine
* Find out clients of your MySQL Server
* Setup MySQL Cluster 7.0 in Linux
* Setup MySQL Cluster in Amazon EC2
* Optimize and fix MySQL Server running slow without any load
* Quickly repair a huge corrupted table
* Optimize large MySQL setup
* Ignore MySQL error while executing bulk statements

Previous post:

Next post: