Converting MySQL Tables From MyISAM to InnoDB

Before InnoDB, indexes would get corrupted, updates meant full table locks—not just row locks, and there was no support for transactions. Since the advent of InnoDB we’ve come a long way.

Sites that don’t use InnoDB are missing out on performance and stability gains. As part of check for new sites, we check the engine type on every table. If we find a table using the MyISAM engine, we notify the user so they can fix it. To make it easy, there is a PHP script you can use to help convert your MyISAM tables to InnoDB.

Also please check InnoDB is configured correctly. I prefer that ‘innodb_file_per_table =1’ setting turned on. I will take the performance hit, it will make database crash easier to recover.

Please read:

Recover InnoDB database from .frm and .ibd files.
Why using innodb_file_per_table?

BACKUP YOUR DATABASE BEFORE DOING THIS! YOU HAVE BEEN WARNED!

First login to the website.
Go to the administration side of the site, and find the current theme.
Next FTP into the site.
Open the functions.php file for the current theme and paste the code below and save the file.
Uncomment the function call at the end this function.
Refresh the admin page. The conversion has begun, wait a page appears sayin what got converted.
Next remove the code you pasted into the functions.php file.
That’s about it.

/**
 * Convert  MyISAM to InnoDB tables...
 *  https://pantheon.io/docs/myisam-to-innodb/ ** see the second code window.
 */

function mysql_myisam_comverter() {

	$db = array();
	/*
	 * Change these to match your database connection information
	 */
	$db['host']     = DB_HOST;
	$db['port']     = "3306";
	$db['user']     = DB_USER;
	$db['password'] = DB_PASSWORD;
	$db['database'] = DB_NAME;
	/*
	 * DO NOT CHANGE ANYTHING BELOW THIS LINE
	 * Unless you know what you are doing. :)
	 */
	$db['connectString'] = $db['host'];
	if ( isset( $db['port'] ) && (int) $db['port'] == $db['port'] ) {
		$db['connectString'] .= ":" . $db['port'];
	}
	$mysqli = @new mysqli( $db['connectString'], $db['user'], $db['password'], $db['database'] );
	if ( $mysqli->connect_errno ) {
		echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "<br>";
		die( 1 );
	}
	$results = $mysqli->query( "show tables;" );
	if ( $results === false or $mysqli->connect_errno ) {
		echo "MySQL error: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "<br>";
		die( 2 );
	}
	while ( $row = $results->fetch_assoc() ) {
		$sql       = "SHOW TABLE STATUS WHERE Name = '{$row['Tables_in_' . $db['database']]}'";
		$thisTable = $mysqli->query( $sql )->fetch_assoc();
		if ( $thisTable['Engine'] === 'MyISAM' ) {
			echo "Changing {$row['Tables_in_' . $db['database']]} from {$thisTable['Engine']} to InnoDB.<br>";
			$sql = "alter table " . $row[ 'Tables_in_' . $db['database'] ] . " ENGINE = InnoDB;";
			$mysqli->query( $sql );
		} else {
			echo $row[ 'Tables_in_' . $db['database'] ] . ' is of the Engine Type ' . $thisTable['Engine'] . "<br>";
			echo "Not changing to InnoDB.<br>";
		}
	}
	die( 0 );

}
//mysql_myisam_comverter();