Convert MyISAM to InnoDB

Submitted by: 
Visitors have accessed this post 906 times.

MyISAM and InnoDB is a storage engine of MySQL. If you have large databases and have lots of traffic on your website, you will probably encounter a CPU load problem due to table locking features of MyISAM.
I created this article to share my problem on several website that I owned including Almost every day I am worried about the load that MySQL is generating on my server’s CPU. In fact, there are times that this website are down and cannot be access for several minutes.
After a thorough research, I decided to convert all of the tables in every database on my server from MyISAM to InnoDB. This is backed up by an article from that explains the pros and cons of the two most common storage engine of MySQL.
Changing the storage engine one by one using phpMyAdmin is a bet of a hassle. So here’s a code to execute the conversion for every database that you have.

  1. <?php
  2. $DB_HOST = "localhost";
  3. $DB_NAME = "database";
  4. $DB_USER = "root";
  5. $DB_PASSWORD = "";
  7. // do not edit the code after this line
  8. $con = mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD);
  10. if (!$con)
  11. {
  12. die('Could not connect: ' . mysql_error());
  13. }
  15. mysql_select_db($DB_NAME , $con);
  16. $sql = "SHOW tables";
  17. $rs = mysql_query($sql);
  19. while($row = mysql_fetch_array($rs))
  20. {
  21. $tbl = $row[0];
  22. $sql = "ALTER TABLE $tbl ENGINE=INNODB";
  23. mysql_query($sql);
  24. }
  25. ?>

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.