Skip to content

Instantly share code, notes, and snippets.

@mbirth
Created July 12, 2013 09:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mbirth/5983246 to your computer and use it in GitHub Desktop.
Save mbirth/5983246 to your computer and use it in GitHub Desktop.
This script will fetch a list of all InnoDB tables from MySQL and check if they are in separate files (as per innodb_file_per_table setting) or still in the ibdata1 file (which always grows, but never shrinks). So you can decide whether to do a cleanup (http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine) or not.
<?php
if (posix_getuid() != 0) {
echo 'Run as root!' . PHP_EOL;
exit(1);
}
$mysql_host = 'localhost';
$mysql_port = '3309';
$mysql_user = 'username';
$mysql_password = 'password';
$mysql_dir = '/var/lib/mysql';
echo 'Querying MySQL.';
$pdo = new PDO('mysql:host=' . $mysql_host . ';port=' . $mysql_port . ';dbname=information_schema', $mysql_user, $mysql_password);
echo '.';
$sql = 'SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine="InnoDB"';
$stmt = $pdo->prepare($sql);
echo '.';
$stmt->execute();
echo '.';
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo ' OK' . PHP_EOL;
$tbl_file = 0;
$tbl_ibdt = 0;
foreach ($data as $table) {
$filename = $mysql_dir . DIRECTORY_SEPARATOR . encode_filename($table['table_schema']) . DIRECTORY_SEPARATOR . encode_filename($table['table_name']) . '.ibd';
if (!file_exists($filename)) {
$tbl_ibdt++;
echo 'ibdata: ' . $table['table_schema'] . '.' . $table['table_name'] . PHP_EOL;
} else {
$tbl_file++;
}
}
echo 'innodb_file_per_table : ' . $tbl_file . PHP_EOL;
echo 'Tables in ibdata1 file: ' . $tbl_ibdt . PHP_EOL;
function encode_filename($mysql_name)
{
$result = str_replace('.', '@002e', $mysql_name);
return $result;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment