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.

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]';

Previous

Convert .htaccess to web.config

Next

Cache MySQL query results with PHP WinCache

1 Comment

  1. Anonymous

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

Leave a Reply

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

Powered by WordPress & Theme by Anders Norén

16 queries, 0.447 seconds running PHP version 7.3.0 K yN jMVG I lX bfSomIrG