How to convert your MySQL databases from MyISAM to InnoDB

Valic —  December 4, 2012 — Leave a comment

This job can be done in many ways like dumping all databases and replacing myisam with innodb in all created tables. However this solution can take very long time.

The simplest way is to use maatkit package. I will show you how to convert your MySQL databases from MyISAM to InnoDB using maatkit:

Step1. Install maatkit package:

apt-get install maatkit

Step2. Now we need to find a list with all your databases. You can do that using the following command:

mysql -u root --password=your_password --skip-column-names -e "show databases;" > databases.list

Step3. Now convert all your MySQL databases from MyISAM to InnoDB

for databases in `cat databases.list`; do /usr/bin/mk-find $databases --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print; done

This command will extract all database names from databases.list file and send that name to mk-find  which is executing ”ALTER TABLE” command on all of its tables to convert their storage engine from MyISAM to InnoDB.

Enjoy!

Valic

Posts Twitter Facebook

Editor in Chief at Debian-Tutorials, Linux enthusiast.

No Comments

Be the first to start the conversation.

Leave a Reply