visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Knowledge Base : How to check all tables from all databases on a MySql server instance ?
By: VGR France  Date: 19/04/2005 11:03:03  English French  Points: 0 Status: Answered
Quality : Excellent
CHECK TABLE xxx and REPAIR TABLE xxx are tedious given there is no CHECK ALL TABLES command ;-)

Thus I wrote this :

<?php // // checkalltables.php : explicite // //VGR08082002 : création //VGR19042005 MOD slight revamping and cleansing for offering the script on EEE : unnecessary double quotes changed, missing TBODYs added, tags lowercased // header("Refresh: 9999"); /* set browser refresh period à 9999s (jamais) */ set_time_limit(0); // allowed infinite exec. time //begin //VGR REM yes the HTML 4.01 DOCTYPE is still missing ;-) and those tags are still uppercase echo "<HTML><BODY BACKGROUND=\"\" BGCOLOR=\"000000\" VLINK=\"FF0000\" TEXT=\"FFFFFF\" LINK=\"8080FF\">"; // // ----------------------------- DEFS ----------------------------------- // GetMyStuff($dbHost,$dbLogin,$dbPassword); //VGR19042005 REM you've to arrange to get your settings ;-) $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error()); //VGR REM this is a bad idea on Windows : use connect() $query='show databases;'; $result=mysql_query($query,$linkID) or die ("bad query show DBs : ".mysql_error()); echo "Check tables des DBs de $dbHost :<bR>"; while ($res=mysql_fetch_array($result)) { $dbName=$res[0]; mysql_select_db($dbName,$linkID) or die ("bad select DB $dbName. ".mysql_error()); $query='show tables;'; echo '<hr>'; // séparateur entre DBs $result2=mysql_query($query,$linkID) or die ("bad query show tables of $dbName. ".mysql_error()); while ($res2=mysql_fetch_array($result2)) { $nomtable=$res2[0]; $query="check table $nomtable;"; $result3=mysql_query($query,$linkID) or die ("bad query check table $nomtable of $dbName. ".mysql_error()); echo "<font color='red'>CHECK TABLE $nomtable :</font> "; echo '<table><tbody>'; while ($res3=mysql_fetch_array($result3)) echo "<tr><td>{$res3[0]}<td>{$res3[1]}</td><td>{$res3[2]}</td><td>{$res3[3]}</td></tr>\r\n"; //VGR REM ADDed eoln for nicer HTML code, but it's not necessary and spoils some bandwidth echo '</tbody></table>'; } // boucle tables } // boucle bases de données echo 'Terminé.'; ?>



Amusez-vous bien avec cet outil très utile à mon humble avis.

Il serait facile à modifier pour exécuter automatiquement le REPAIR TABLE sur les tables concernées...
By: VGR Date: 03/10/2010 10:31:37 English  Type : Comment
doing a bit of follow-up on this... I added the repair facility four years ago ;-)

Produces :

CHECK TABLE activite : databasename.activite check warning 2 clients are using or haven't closed the table properly databasename.activite check error Record-count is not ok; is 133321 Should be: 133322 databasename.activite check warning Found 331 deleted space. Should be 0 databasename.activite check warning Found 1 deleted blocks Should be: 0 databasename.activite check error Corrupt REPAIR TABLE activite : databasename.activite repair warning Number of rows changed from 133322 to 133321 databasename.activite repair status OK OK, no warnings



You'll find the PHP code doing this here.

Once I get a system crash or savage reboot, I simply run http://localhost/somepath/checkalltables.php and all possible problems are fixed in all the server's databases (ie, all web sites ;-)

HTH

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 17055.941820 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page