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:
- 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.
- Download MaatKit mk-find tool. This is like system find command but for database:
$ cd /opt
$ wget http://www.maatkit.org/get/mk-find
- 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”
- 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.
