Convert your MySQL databases from InnoDB to MyISAM or vice versa

I`m supporting several WordPress Multisite installations and on one of them i noticed that half of the tables were InnoDB and the other half MyISAM…this happened because the installation was migrated from server with default engine MyISAM to a server with InnoDB. So basically this wordpress tutorial will be about converting your databases from InnoDB engine to MyISAM engine and vice versa.

The installation is running on VPS with Ubuntu 12.04 in OpenVZ container(not so clever I know), and the mysql memory consumption and performance were very poor but I didn`t know which of the two engines had a problem. Mysqltuner didn`t help me much, so I backed up the database and converted it entirely in InnoDB. It was lightning fast in the beginning, but after a while it started eating a lot of memory and the performance went down again…Turns out that InnoDB`s performance on OpenVZ isn`t so great…it takes a lot more memory then on a dedicated server and the performance is very poor for some reason. When i converted the tables back to MyISAM everything went back to normal. So for any of you having the same problem I`ll post the code i`m using for the database conversion between engines.(You`ll need terminal access)

Convert to InnoDB


#Navigate to your temp folder

cd /tmp

#Generate the queries

#Change 'username' and 'db_name' to yours

mysql -u username -p -e "SHOW TABLES IN db_name;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

#Run the queries

mysql -u username -p db_name < alter_table.sql

Convert to MyISAM


#Navigate to your temp folder

cd /tmp

#Generate the queries

#Change 'username' and 'db_name' to yours

mysql -u username -p -e "SHOW TABLES IN db_name;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=MYISAM;" > alter_table.sql

#Run the queries

mysql -u username -p db_name < alter_table.sql

  • Hey there! This post could not be written any better!
    Reading through this post reminds me of my old room mate!
    He always kept chatting about this. I will forward this article to him.
    Pretty sure he will have a good read. Thanks for sharing!

  • I’ve been battling the innodb/OpenVZ problem for weeks now and I’m so glad I finally found this simple solution. Thank you so much!

    • awordpress

      Glad it helped 🙂