Get the Size of a MySQL Database with PHP

>> Output the size of a MySQL database with this simple script

RESOURCE ADDED ON TUESDAY 27 JUNE 2006, 11:41:26

This simple tutorial will show you how to get the size of a MySQL database using PHP in just a few simple steps.

First of you'll need my formatfilesize() function; for this script to work you will obviously need to connect to your MySQL database. Then add this line of code but replace "databasename" with the name of your database:

Code: Set the database name

<?php

$dbname = "databasename";

?>

Downloads: 400

Now comes the fundamental part of the tutorial: computing the database size. It's actually very simple - all we need to do is to go through each table in the database and add up the length of the data and the index:

Code: Add up the index and data for each table

<?php

    mysql_select_db( $dbname );
    $result = mysql_query( "SHOW TABLE STATUS" );
    $dbsize = 0;

    while( $row = mysql_fetch_array( $result ) ) {

        $dbsize += $row[ "Data_length" ] + $row[ "Index_length" ];

    }

?>

Downloads: 1420

We now have the size of the databse in bytes sotred in the $dbsize variable. Now we just output it to a more user-friendly format using my formatfilesize() function:

Code: Output the database size

<?php

echo "<p>The size of the database is " . formatfilesize( $dbsize ) . "</p>";

?>

Downloads: 492

That's about the size of it!

This resource has been viewed 11763 times.

Back to Resources

Back to Tutorials

Back to Web Severside

Support Dr Quincy

These resources are provided free of charge. If, however, you would like to make a donation to help finance these resources, no matter how big or small, please use the PayPal button below.

Credits

This resource was written by Tim Bennett Bsc (Hons). Tim Bennett has a First Class Degree in Multimedia Systems Computing from Leeds Metropolitan University. He works as a freelance multimedia developer under the company name Texelate creating free and commissioned high-quality multimedia solutions. Find out more by visiting the portfolio section and viewing his blog. If you'd like to request a resource get in touch!

Rate this Resource

Click on the appropriate star below to rate this resource:

Num votes: 1474

Average: 9.8 / 10

Comments

Is the simplest and best way to get the size of a db. i try many scripts on net and thx God i found this..good job

mascka commented on 15/01/2008, 10:29:53

If you have access to shell commands, a real easy optional way to do this would be to do:

function getDbSize($dbname){
$size=shell_exec("du -b /var/lib/mysql/$dbname");
$size=explode(' ',$size);
return $size[0];
}

Steve commented on 19/09/2006, 19:38:47

Good little script - works perfectly

Steve commented on 21/08/2006, 16:35:07

Awesome

Matt commented on 11/07/2006, 09:19:23