MySQL - Get the size used by each database

When you are running out of disk space on your database server. It is good to know which database took the most space. The SQL query below will show you the size of each database of your MySQL.

SELECT table_schema "Database Name"
     , SUM( data_length + index_length ) / 1048576  AS "Database Size in MB"
     , SUM( data_free )/ 1048576  AS "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Output

+--------------------+---------------------+------------------+
| Database Name      | Database Size in MB | Free Space in MB |
+--------------------+---------------------+------------------+
| bt_96580           |              1.1242 |           0.0014 |
| drupal             |              2.4711 |           0.0006 |
| information_schema |              0.0078 |           0.0000 |
| mysql              |              0.5875 |           0.0000 |
| rth                |              0.1172 |           0.0000 |
| test               |              0.0011 |           0.0000 |
| tp                 |              4.3792 |           0.0000 |
+--------------------+---------------------+------------------+

MySQL

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.