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, ‘.’, ‘.’ );