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

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. All this piece of PHP code needs to calculate the MySQL database size are your database credentials (username, password).

Calculate the MySQL database size with PHP

<?php
/**
 * Function to calculate the size, in bytes, of a MySQL database
 * Needs $dbhostname, $db, $dbusername, $dbpassword
 * - Jan Reilink
 */
$dbhostname = "hostname";
$db = "database name";
$dbusername = "user name";
$dbpassword = "password";
$link = mysqli_connect( $dbhostname, $dbusername, $dbpassword, $db );
if( !$link ) {
  die( mysqli_connect_error() );
}

function get_dbsize( $link, $db ) {
  $query = "SHOW TABLE STATUS FROM `".$db."`";
  $tables = 0;
  $rows = 0;
  $size = 0;
  if ( $result = mysqli_query( $link, $query ) ) {
    while ( $row = mysqli_fetch_assoc( $result ) ) {
      $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( $link, $db );
$megabytes = $result[0] / 1024 / 1024;

/* https://www.php.net/manual/en/function.number-format.php */
$megabytes = number_format( round( $megabytes, 3 ), 2, ',', '.' );
?>

For the 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! Note that the number of rows is an estimate for InnoDB tables.

Still have old PHP code using mysql_* functions? Learn how to convert PHP ext/mysql to MySQLi.

Calculate the MySQL database size by querying the information_schema database

You can use the following MySQL query, as a pure MySQL solution, to get the MySQL database size in bytes. It queries the information_schema database:

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

Divide the result by 1024 twice to get the size in megabytes, just like the PHP script does.

Is your database bigger than expected? Check, repair and optimize MySQL tables with mysqlcheck to reclaim unused space.