Where to get total disk space used for MySQL?

You can obtain the size of your MySQL database through SQL commands. Though it is not as nice and straight forward as we would all like it to be. Here is how it works, and some example code to try out. I currently use this methodology for managing the mysql quota sizes.

How to obtain the size of your database's tables. MySQL will not allow you to see the total size of the "database" but rather returns the size of the data and indexes for each table within your database. You can use the command, "SHOW TABLE STATUS", this returns a row for each table in your database with information for each table, this command is documented at http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html.

The information that is relevant to the actual size of your tables are the columns, Data_length, and Index_length. The size is reported in "bytes". Add the values Data_Length with Index_length to obtain the total size of the table. Then add each total for each table together to obtain the total size of the database.

Below is some VERY rough PHP code that will print the size of each table.
<snip>
define(DBHOST,'localhost:/storage/mysql/tmp/mysql.sock');
define(DBUSER,'root');
define(DBPASS,'iwtmlom');
define(DBNAME,'MYSQLDB_48281');

mysql_connect(DBHOST, DBUSER, DBPASS) or
die("Could not connect: " . mysql_error());

mysql_select_db(DBNAME) or
die("Could not select " . DBNAME . mysql_error());

$result = mysql_query("SHOW TABLE STATUS");
while($foo = mysql_fetch_object($result))
{
echo "Table Name: $foo->Name \n";
echo "$foo->Data_length \n";
echo "$foo->Index_length \n";
$tblSize = $foo->Data_length + $foo->Index_length;
echo "Table Size in Bytes: $tblSize\n";
echo "Table Size MegaBytes: " . (int)($tblSize/1024)/1024 . "\n";
echo "-----------------------\n";
echo "\n";
}
</snip>
quote:Originally posted by dbassett74

I couldn't find this in the control panel. Any help on how to retreive this info would be appreciated. Dan.
</blockquote id="quote"></font id="quote">