The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to check MySQL db sizes

Discussion in 'General Discussion' started by sirswatch, Aug 5, 2004.

  1. sirswatch

    sirswatch Member

    Joined:
    Nov 5, 2003
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    I have written I little script in php for checking size of db tables of each user. This because is not a good idea limit size of db: you can incurr into a data loss.....
    This script check any db and calculate the tables and indexes sizes and then create a table with results.
    For using simply pur into $DbPassword variable the root password for mysql.
    That's all !

    <?
    $DbHost = "localhost";
    $DbDatabase = "test";
    $DbUser = "root";
    $DbPassword = "insert_your_root_password_here";
    mysql_pconnect($DbHost,$DbUser,$DbPassword) or die("Database access error. Please contact the site administrator.");
    mysql_select_db($DbDatabase);
    $dbs = mysql_list_dbs();
    while ($a_db = mysql_fetch_object($dbs)) {
    if (!$num_dbs) {
    $dblist[]= $a_db->Database;
    }
    }
    $num_dbs = count($dblist);
    if ($num_dbs < '1') {
    print "No databases found !";
    exit;
    }
    print "<table border=1 width=50% align=center><tr><th>DB NAME</th><th>Size in Mb</th></tr>";
    for ($i = 0; $i < $num_dbs; $i++) {
    $db = $dblist[$i];
    mysql_connect($DbHost,$DbUser,$DbPassword);
    mysql_select_db($db);
    $result = mysql_query("show table status");
    $size = 0;
    $out = "";
    while($row = mysql_fetch_array($result)) {
    $size += $row["Data_length"];
    $size += $row["Index_length"];
    }
    $size=number_format(($size/1024/1024),2,",",".");
    print "<tr><td>$db</td><td align=right>$size&nbsp;</td></tr>";
    }
    print "</table>";
    ?>
     
Loading...

Share This Page