How to get MySQL database size for your database?


While working with MySQL, you may need to get MySQL database size for all or a specific database. There are no direct variables that store this information. You can use the following SQL query to get it. A MySQL database size consists of 2 parts:

1. data_length – size of the table records

2. index_length – size of the table index

These are system variables which stores the information as number of bytes. The query below adds the 2 variables for the database. It converts the resultant number of bytes into mega bytes (MB) for easier understanding. Just replace $DB_NAME below to use it.

Get MySQL database size for a single database

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Database Size in MB" FROM information_schema.TABLES where table_schema="$DB_NAME" ;

$DB_NAME – Name of your MySQL database

Get MySQL database size for multiple databases

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Database Size in MB" FROM information_schema.TABLES where table_schema in ("$DB_NAME1","$DB_NAME2","$DB_NAME3") ;

$DB_NAME1, $DB_NAME2, $DB_NAME3 – Names of your MySQL databases

Get MySQL database size for each database in your system

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Get total MySQL database size due to all databases

SELECT SUM( data_length + index_length) / 1024 / 1024 "Database Size in MB" FROM information_schema.TABLES;
mm

About Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq, a business dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!