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 to calculate the MySQL database size.

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

$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, ',', '.' );
?>

For MySQL database size, 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 get the MySQL database size. It queries the information_scheme database:

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

Thank you very much! <3 ❤️

Jan Reilink

Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace – The Human Cloud. In the past I worked at CLDIN (CLouDINfra) and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at https://www.saotn.org.