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, ',', '.' ); ?>
Code language: PHP (php)

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]';
Code language: SQL (Structured Query Language) (sql)
Sharing is caring

1 thought on “Calculate MySQL database size with PHP (off the old shelf)”

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

Comments are closed.

Show Buttons
Hide Buttons