Calculate MySQL database size with PHP (off the old shelf)

Calculate the MySQL database size in PHP. Sometimes you’d be amazed what you find when cleaning out your old script archives. I found an old PHP script to calculate the size of a MySQL database. I post it here just for the fun of it.

All this piece of PHP code needs is your MySQL database credentials.

<?php
/**
 * Function to calculate the size, in bytes, of a MySQL database
 * Needs $dbhostname, $db, $dbusername, $dbpassword
 * - Jan Reilink <jan@saotn.nl>, Twitter: @HertogJanR
 */

$dbhostname = "hostname";
$db = "database name";
$dbusername = "user name";
$dbpassword = "password";

$link = mysql_connect( $dbhostname,$dbusername,$dbpassword );
if( !$link ) { die( mysql_error() ); }
$db_selected = mysql_select_db( $db, $link );
if ( !$db_selected ) { die( mysql_error() ); }

function get_dbsize( $db ) {
  $query = "SHOW TABLE STATUS FROM `".$db."`";
  if ( $result = mysql_query( $query ) ) {
    $tables = 0;
    $rows = 0;
    $size = 0;
    while ( $row = mysql_fetch_array( $result,MYSQL_ASSOC ) ) {
      $rows += $row["Rows"];
      $size += $row["Data_length"];
      $size += $row["Index_length"];
      $tables++;
    }
  }

  $data[0] = $size;
  $data[1] = $tables;
  $data[2] = $rows;
  return $data;
}

$result = get_dbsize( $db );
$megabytes = $result[0] / 1024 / 1024;
/* http://www.php.net/manual/en/function.number-format.php */
$megabytes = number_format( round( $megabytes, 3 ), 2, ',', '.' );
?>

The array $data[] holds all the information, in its keys:

  1. 0: size, in bytes
  2. 1: number of tables in the database
  3. 2: number of rows

Use with caution! And it shouldn’t be too difficult to rewrite this to MySQLi.

Calculate the MySQL database size by querying MySQL information_scheme database

You can use the following MySQL query, as a pure MySQL solution, to calculate the MySQL database. It queries the information_scheme database:

SELECT SUM( data_length + index_length ) FROM information_schema.tables
WHERE table_schema = '[db-name]';

Did this post helped you solve a problem? Or dit you find it interesting? Support Sysadmins of the North with a direct donation via Paypal of by bank wire transfer IBAN: NL31 ABNA 0432217258 (Jan Reilink). Just $ 5,- or € 5,- is more than enough, thanks!

Read this too:   How to: Test MySQL database connectivity in ASP.NET, PHP, ASP

Support SAOTN.ORG


564 views

1 comment

Leave a Reply

Your email address will not be published. Required fields are marked *