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:
- 0: size, in bytes
- 1: number of tables in the database
- 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 ❤️
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.
Slight Correction
$megabytes = number_format( round( $megabytes, 3 ), 2, ‘.’, ‘.’ );