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;
