Find MySQL database size of all your databases

Sometimes it’s useful to find the size of all your MySQL databases. You can do this on the operating system, but there is an easier way. Connect to your MySQL database and use this handy SQL query.

Finding the MySQL database size

mysql> SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 
    -> "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+---------------------+
| Database Name      | Database Size in MB |
+--------------------+---------------------+
| database1          |        473.71875000 |
| database2          |          1.17160511 |
| database3          |          4.56250000 |
| database4          |         16.26483250 |
| database5          |          4.49138069 |
| information_schema |          0.00781250 |
| mysql              |          0.29175186 |
+--------------------+---------------------+
7 rows in set (2.05 sec)

To also include free space on each database, we can change the query a bit and we get this result:

Find the size, including free space

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024
    -> "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB"
    -> FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| database1          |         473.71875000 |   53676.00000000 |
| database2          |           1.17160511 |       0.17476654 |
| database3          |           4.56250000 |   12993.00000000 |
| database4          |          16.26483250 |    6390.00000000 |
| database5          |           4.52287960 |    3408.00000000 |
| information_schema |           0.00781250 |       0.00000000 |
| mysql              |           0.29175186 |       0.01016235 |
+--------------------+----------------------+------------------+
7 rows in set (2.83 sec)

Updated: In a comment below there was confusion about what the “Free Space in MB” field actually measured. This field measures the number of allocated but unused bytes. In other words, it is the size of the database files compared to the data stored in the database files. Not necessarily the total size your database can hold. The total size of the database is really more dependent on the size of your harddisk.

Of course, these commands also work on MariaDB.

NOTE: For very large databases, these queries for database size might take some time and put extra load on the database. Choose your timing carefully to prevent impacting the performance of your MySQL server.