find_innodb_tables.php

find_innodb_tables.php

<?php

// Set our global variables
$user = '';
$pass = '';
$databases = array();

// Default MySQL credential files
$try_files = array(
  '/root/.my.cnf',
  '/etc/mysql/debian.cnf',
  '/etc/my.cnf.d/client.cnf'
);

// Is our MySQL configuration loaded?
$mycnf_loaded = FALSE;

// Try each config
foreach($try_files as $try)
{

  // File does not exist
  if (!file_exists($try) || !is_readable($try)) next;

  // Grab info from file
  $parse = @parse_ini_file($try, true);

  // If the file was properly read..
  if (is_array($parse))
  {
    // Fill our global variables with the data from the config
    @list($user, $pass) = array($parse['client']['user'], $parse['client']['password']);
    // We require a username and password to continue

    if (!empty($user) && !empty($pass))
    {
      $mycnf_loaded = TRUE;
    }

  }
}

// Bail if we can't find a way to get a list of databases
if (!$mycnf_loaded)
{
  die("No working MySQL configuration file was found\n");
}

// Connect to the MySQL server using our config file variables
$conn = mysql_connect('localhost', $user, $pass) or die(mysql_error());

$sql = 'show databases';
$db_iter = mysql_query($sql) or die(mysql_error());

// Find all databases that match:
// *_* (matches cPanel database naming formats)
// NOT _schema (special tables)
while($db = mysql_fetch_array($db_iter))
{
  $db_name = $db[0];
  if (preg_match('/[\w\d]+_[\w\d]+/', $db_name) && !preg_match('/_schema/', $db_name))
  {
    $databases[] = $db_name;
  }
}

// For each of the databases we found..
foreach($databases as $active_db)
{
  $db   = mysql_select_db($active_db, $conn) or die(mysql_error());

  // Get a list of all tables in the current database that are using the MyISAM engine
  $sql = sprintf("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND ENGINE='InnoDB'", $active_db);
  $rs = mysql_query($sql) or die(mysql_error() . PHP_EOL);

  // Loop through each of the tables that matched
  while($row = mysql_fetch_array($rs))
  {
    $table = $row[0];
    echo sprintf("%s.%s\n", $active_db, $table);
    // Optionally Convert the table
    /* Be careful. */
    // mysql_query($sql) or die(mysql_error() . PHP_EOL);
  }

  
}
// Clean up our mess
mysql_close($conn) or die(mysql_error());