Skip to content

Instantly share code, notes, and snippets.

@dkrnl
Last active March 2, 2020 09:57
Show Gist options
  • Save dkrnl/95f9d1809368c3c3a07b to your computer and use it in GitHub Desktop.
Save dkrnl/95f9d1809368c3c3a07b to your computer and use it in GitHub Desktop.
Mysql Optimize Table InnoDB
#!/usr/bin/env php
<?php
$server = "localhost";
$username = "***";
$password = "****";
$connection = mysql_connect($server, $username, $password);
if (!$connection) {
die("Connection error: " . mysql_error());
}
$queryList = array();
$queryList[] = "PURGE BINARY LOGS BEFORE NOW();";
$queryList[] = "SET SQL_LOG_BIN=0;";
$sql = "SELECT CONCAT('OPTIMIZE NO_WRITE_TO_BINLOG TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;').
FROM `information_schema`.`tables`.
WHERE `engine`='MyISAM' AND TABLE_SCHEMA!='information_schema'";
$result = mysql_query($sql, $connection);
if ($result) {
while ($row = mysql_fetch_row($result)) {
$queryList[] = $row[0];
}
}
$sql = "SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;'),
CONCAT('ANALYZE NO_WRITE_TO_BINLOG TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
FROM `information_schema`.`tables`.
WHERE `engine`='InnoDB' AND TABLE_SCHEMA!='information_schema'";
$result = mysql_query($sql, $connection);
if ($result) {
while ($row = mysql_fetch_row($result)) {
$queryList[] = $row[0];
$queryList[] = $row[1];
}
}
$queryList[] = "SET SQL_LOG_BIN=1;";
foreach ($queryList as $sql) {
echo $sql, "\n"; flush();
mysql_query($sql, $connection);
}
$queryList[] = "FLUSH TABLES;";
$queryList[] = "FLUSH QUERY CACHE;";
mysql_close($connection);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment