A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database

MySQL MyISAM database engine has been depreciated in the latest version since 5.5.1. The MyISAM is table locking while InnoDB (now the default database engine for MySQL since 5.5.1) employs the row-level rocking. InnoDB should be the preferred and recommended database engine if you want to create new applications/tables. One deficiency of MyISAM tables is they don’t support transactions. Both InnoDB and MyISAM support the Full Text Search Index.

You can use ALTER TABLE table ENGINE=INNODB to change a table to InnoDB but if you have many tables in a database, e.g. WordPress, this will be tedious to look for MyISAM tables and convert one by one.

Luckily, the following PHP can be run at command line, and it takes parameters of database names – which you can pass many databases, and it will look for those tables in these databases that are currently MyISAM engine and issue a command of ALTER TABLE for each of them.

<?php
// https://helloacm.com/a-simple-php-command-line-tool-to-convert-mysql-tables-from-myisam-to-innodb-in-specified-database/
function ConvertAllTablesToInnoDB($database) {
  define("DB_HOST", "localhost");
  define("DB_USER", "root");
  define("DB_PASSWORD", "password");  
  $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, $database);
  if (!$conn) {
      echo "Error: Unable to connect to MySQL." . PHP_EOL;
      echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
      echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
      exit;        
  }      
  $sql = "SELECT TABLE_NAME FROM 
      INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA = '$database' 
      AND ENGINE = 'MyISAM'
  ";
  
  $rs = mysqli_query($conn, $sql);
  
  while ($row = mysqli_fetch_array($rs)) {
      $tbl = $row[0];
      $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
      echo $sql . "\n";
      mysqli_query($conn, $sql);
  }
}
    
if ($argc < 2) {
      exit( "Usage: php $argv[0] database1 database2 ...\n" );
}

for ($i = 1; $i < $argc; ++ $i) {
      echo "ConvertAllTablesToInnoDB " . $argv[$i] . "... \n";
      ConvertAllTablesToInnoDB($argv[$i]);
}

Example usage:

# php convert_to_innodb.php wordpress
ConvertAllTablesToInnoDB wordpress... 
ALTER TABLE `wp_commentmeta` ENGINE=INNODB
ALTER TABLE `wp_comments` ENGINE=INNODB
ALTER TABLE `wp_icl_string_translations` ENGINE=INNODB
ALTER TABLE `wp_imagify_files` ENGINE=INNODB
ALTER TABLE `wp_imagify_folders` ENGINE=INNODB
ALTER TABLE `wp_itsec_lockouts` ENGINE=INNODB
ALTER TABLE `wp_itsec_log` ENGINE=INNODB
ALTER TABLE `wp_itsec_temp` ENGINE=INNODB
ALTER TABLE `wp_links` ENGINE=INNODB
ALTER TABLE `wp_options` ENGINE=INNODB
ALTER TABLE `wp_postmeta` ENGINE=INNODB
ALTER TABLE `wp_posts` ENGINE=INNODB
ALTER TABLE `wp_term_relationships` ENGINE=INNODB
ALTER TABLE `wp_term_taxonomy` ENGINE=INNODB
ALTER TABLE `wp_termmeta` ENGINE=INNODB
ALTER TABLE `wp_terms` ENGINE=INNODB
ALTER TABLE `wp_usermeta` ENGINE=INNODB
ALTER TABLE `wp_users` ENGINE=INNODB
ALTER TABLE `wp_wc_download_log` ENGINE=INNODB
ALTER TABLE `wp_wc_webhooks` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_api_keys` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_attribute_taxonomies` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_downloadable_product_permissions` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_log` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_order_itemmeta` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_order_items` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_payment_tokenmeta` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_payment_tokens` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_sessions` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_shipping_zone_locations` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_shipping_zone_methods` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_shipping_zones` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_tax_rate_locations` ENGINE=INNODB
ALTER TABLE `wp_woocommerce_tax_rates` ENGINE=INNODB
ALTER TABLE `wp_wpio_images` ENGINE=INNODB
ALTER TABLE `wp_yith_ywpar_points_log` ENGINE=INNODB

There are some databases that belong to MySQL e.g. sys, mysql – these tables have engine types such as memory, csv – please don’t convert them to InnoDB.

MySQL

DevOps / Site Reliability Engineering

• How to Clean Up NVM Node Versions Except One?
• Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
• Python/Bash Script to Print the Optimized Parameters for MySQL Servers
• Learn to Manage Your MySQL Database with a Python Script
• A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
• How to Print MySQL Table Summary using PHP?
• Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
• Bash Script to Check, Repair, Optimise and Backup MySQL database
• Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
• MySQL server stopped due of out of memory exception on Ubuntu VPS
• Running Apache Server (PHP + MySQL) on Raspberry PI
• How to Optimise SQL Queries? Quick Tips
• Recovery Models in SQL Server
• Database Optimisation Script in PHP

–EOF (The Ultimate Computing & Technology Blog) —