Get the size of DB and Tables

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Get the size of DB and Tables

Post by JayBird »

Code: Select all

 
<html> 
<head> 
<title>mysql database size</title> 
</head> 
<body> 
<h1>mysql database size</h1> 
<?php 
 
function file_size_info($filesize) { 
 
    $bytes = array('kb', 'kb', 'MB', 'GB', 'TB'); # values are always displayed 
     
    if ($filesize < 1024) $filesize = 1; # in at least kilobytes. 
 
        for ($i = 0; $filesize > 1024; $i++) 
            $filesize /= 1024; 
            $file_size_info['size'] = ceil($filesize); 
            $file_size_info['type'] = $bytes[$i]; 
            return $file_size_info; 
 
} 
 
$db_server = 'localhost'; 
$db_user = 'user'; 
$db_pwd = 'password'; 
$db_name = 'databasename'; 
$db_link = @mysql_connect($db_server, $db_user, $db_pwd) or exit('Could not connect: ' . mysql_error()); 
$db = @mysql_select_db($db_name, $db_link) or exit('Could not select database: ' . mysql_error()); 
 
// Calculate DB size by adding table size + index size: 
$rows = mysql_query("SHOW TABLE STATUS"); 
$dbsize = 0; 
while ($row = mysql_fetch_array($rows)) { 
    $dbsize += $row['Data_length'] + $row['Index_length']; 
    print '<pre>Table: <strong>' . $row['Name'] . '</strong><br />'; 
    print 'database size. . .: ' . $row['Data_length'] . '<br />'; 
    print 'Index size . . . .: ' . $row['Index_length'] . '<br />'; 
    print 'Total size . . . .: ' . ($row['Data_length'] + $row['Index_length']) . '<br /></pre>'; 
} 
 
print "database size is: $dbsize bytes<br />"; 
print 'or<br />'; 
$dbsize = file_size_info($dbsize); 
print "database size is: {$dbsize['size']} {$dbsize['type']}"; 
 
?> 
</body> 
</html>
 
Post Reply