Sysadmins of the North

Technical blog, where topics include: computer, server, web, sysadmin, MySQL, database, virtualization, optimization and security

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

In this piece of code, 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.

This may interest you:   Remove Jetpack email sharing service

Calculate the MySQL database size by querying MySQL’s 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]';

1 Comment

  1. Slight Correction
    $megabytes = number_format( round( $megabytes, 3 ), 2, ‘.’, ‘.’ );

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

o g Smo QeNRrBkXRES U