It is often required to get the size of a database in MySQL to understand how much disk space does it need.
It is especially useful to check the size of a database if you prepare to migrate it somewhere or going to setup database backup and it needs to choose the storage capacity.
In this short note i will show the SQL queries that get the information about the required databases from the information_schema
and print their sizes in megabytes.
Cool Tip: Check the size of a table in MySQL with a single query! Read more →
Check The Size Of A Database In MySQL
Check the size of the all MySQL databases:
mysql> SELECT table_schema `Database`, Round(Sum(data_length + index_length) / 1024 / 1024, 1) `Size in MB` FROM information_schema.TABLES GROUP BY table_schema;
Sample output:
+--------------------+------------+ | Database | Size in MB | +--------------------+------------+ | information_schema | 0.0 | | db_name | 61.5 | +--------------------+------------+ 2 rows in set (0.66 sec)
Cool Tip: Do you have a backup? You MUST have it! Backup MySQL databases from the command-line! This is really easy! Read more →
Get the size of an explicitly specified MySQL database:
mysql> SELECT table_schema `Database`, Round(Sum(data_length + index_length) / 1024 / 1024, 1) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "db_name";
Sample output:
+----------+---------------+ | Database | Size in MB | +----------+---------------+ | db_name | 61.5 | +----------+---------------+ 1 row in set (0.02 sec)